Thank you azyk,
using the keyword OLEDBProvider I can connect to the database again. However, I hoped it fixed the performance issue, but it didn´t.
I´m sorry I can´t provide access to the database.
Do I have to pay attention to some TUniConnection or TCustomDADataSet settings? All I do is this:
Code: Select all
TUniConnection* Database = new TUniConnection( NULL );
Database->ConnectString = "Provider Name=SQL Server;Data Source=W7-WS-0050\\MSSQL;Initial Catalog=test;Port=0;User ID=sa;Password=xxxx;Login Prompt=False";
Database->Connect();
TCustomDADataSet* Query = Database->CreateDataSet();
Query->SQL->Add( "...." );
Query->Prepare();
Query->Params->Items[0]->AsInteger = criterion1;
Query->Params->Items[1]->AsInteger = criterion2;
Query->Params->Items[2]->AsInteger = criterion3;
StopWatch w;
w.start();
Query->Execute();
w.stop();
TRACE "Defect query (UDAC) returned ", Query->RecordCount, " records in ", w.Elapsed, " seconds.";
...
The query contains 5 tables and uses LEFT JOINS, the table the query is run against has about 2.8M records.
I implemented the same query as ODBC query and produced these results:
[21.09.2015 13:59:40.863] [ThreadID 9944] Defect query (UDAC) returned 2133 records in 0.690991 seconds.
[21.09.2015 13:59:40.921] [ThreadID 9944] Defect query (ODBC) returned 2133 records in 0.0278557 seconds.
[21.09.2015 13:59:41.601] [ThreadID 9944] Defect query (UDAC) returned 0 records in 0.677355 seconds.
[21.09.2015 13:59:41.622] [ThreadID 9944] Defect query (ODBC) returned 0 records in 0.0200665 seconds.
[21.09.2015 13:59:42.302] [ThreadID 9944] Defect query (UDAC) returned 0 records in 0.678661 seconds.
[21.09.2015 13:59:42.323] [ThreadID 9944] Defect query (ODBC) returned 0 records in 0.0200141 seconds.
[21.09.2015 13:59:42.999] [ThreadID 9944] Defect query (UDAC) returned 364 records in 0.675489 seconds.
[21.09.2015 13:59:43.026] [ThreadID 9944] Defect query (ODBC) returned 364 records in 0.0210829 seconds.
[21.09.2015 13:59:43.699] [ThreadID 9944] Defect query (UDAC) returned 0 records in 0.671977 seconds.
[21.09.2015 13:59:43.717] [ThreadID 9944] Defect query (ODBC) returned 0 records in 0.0118834 seconds.
The speed comparison shows clearly that ODBC outperforms UniDAC by the factor of 30, though it adds an additional level of indirection. Furthermore it shows that the bottleneck is not the SQL Server but some part of the UniDAC suite.
Edit:
I measured the query execution time only and ignored the time needed to iterate over the results.