Looping through a whole table

[uffek]

I am looping through the same table (7 million records), stored in 3 different databases, SQL server 2008 R2 Express, PostgreSQL and Oracle 11g Express. All databases are on the same server and are accessed from the same client (6.4.14 & Delphi 10.0 64-bit based). But I get very different results:

MSSQL: 100 sec
PostgreSQL: 648 sec
Oracle: 1636 sec (direct mode)

The code is basically like this:

Code: Select all

  tbl.UniDirectional:= true;
  tbl.Open;
  while not tbl.eof do tbl.Next;

Any ideas to speed up the two slow ones?

[MaximG]

Your DB servers have completely different architecture. Therefore, even when they are installed on the same hardware platform, you can get different performance values, even when processing the same data. The reason for this may be different settings for each database, different data storage methods, etc. So performance tuning is necessary to execute in each of applied databases separately. When using UniDAC main ways of increasing performance are available at the link: https://www.devart.com/odac/docs/?incre ... rmance.htm

[uffek]

Setting table.fetchrows = 500 for postgresql and oracle helped a lot.
mssql seem to not care much about this setting.

[MaximG]

Each of the used DB has specific paticularities of performance setting. Microsoft SQL server is no exception. In addition to the general recommendations that are available in our documentation to SDAC : https://www.devart.com/sdac/docs/?performance_sdac.htm You can also learn the following: https://www.devart.com/sdac/docs/?performance_sdac.htm. Besides, using the Direct mode can affect the performance effectively : https://www.devart.com/sdac/docs/?direct_mode.htm . Please note that the properties described in the documentation to SDAC are available in Unidac with using SQLServerUniProvider through the UniConnection.SpecificOptions property: https://www.devart.com/unidac/docs/?sqlprov_article.htm To work with the Direct mode using Microsoft SQL server, set the prDirect value to the TUniConnection.SpecificOptions.Values['SQL server.Provider'] property".

[uffek]

Sample code shown here do not compile:
https://www.devart.com/sdac/docs/?direct_mode.htm

errors in:
MSConnection.Options.Provider := prDirect;
MSConnection.Authentication := auServer;

Similar errors in the ODAC sample code.

This kind of error is repeated throughout the documentation.

[ViktorV]

Please specify what particular errors you get in SDAC and ODAC.

[uffek]

COMPILATION errors.

For instance TUniconnection does not have a property called authentication.
Or rather it can (no longer?) be accessed that way in code.

[ViktorV]

Each DAC product reference contains samples of working with components of specific DAC.
The properties described in SDAC (ODAC) documentation are available in Unidac with SQLServerUniProvider (OracleUniProvider) through the UniConnection.SpecificOptions property : https://www.devart.com/unidac/docs/?sqlprov_article.htm (https://www.devart.com/unidac/docs/?oraprov_article.htm)
For example :

Code: Select all

UniConnection.SpecificOptions.Values['SQL server.Provider'] := 'prDirect';
UniConnection.SpecificOptions.Values['SQL server.Authentication'] := 'auServer';