Page 1 of 1
Looping through a whole table
Posted: Sat 17 Sep 2016 16:04
by 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?
Re: Looping through a whole table
Posted: Mon 14 Nov 2016 13:25
by 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
Re: Looping through a whole table
Posted: Tue 22 Nov 2016 13:59
by uffek
Setting table.fetchrows = 500 for postgresql and oracle helped a lot.
mssql seem to not care much about this setting.
Re: Looping through a whole table
Posted: Thu 24 Nov 2016 15:44
by 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".
Re: Looping through a whole table
Posted: Fri 25 Nov 2016 08:45
by 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.
Re: Looping through a whole table
Posted: Fri 25 Nov 2016 09:33
by ViktorV
Please specify what particular errors you get in SDAC and ODAC.
Re: Looping through a whole table
Posted: Fri 25 Nov 2016 09:43
by 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.
Re: Looping through a whole table
Posted: Fri 25 Nov 2016 11:15
by 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';