Page 1 of 1
V6.2.8: Error parsing TUniConnection::ConnectString
Posted: Mon 21 Sep 2015 07:44
by GNiewerth
Hello,
I´m using UniDAC to connect to a MS SQL Server from a non-visual component using this connect string:
Provider Name=SQL Server;Data Source=W7-WS-0050\\MSSQL;Initial Catalog=test;Port=0;User ID=sa;Password=xxxx;Login Prompt=False
The queries are painfully slow (~650ms using UniDAC, ~40ms using MS SQL Server Management Studio), so I tried to set the provider mode manually to
prNativeClient using this connect string:
Provider Name=SQL Server;Provider=SQLNCLI.1;Data Source=W7-WS-0050\MSSQL;Initial Catalog=test;Port=0;User ID=sa;Password=xxxx;Login Prompt=False
And now I even cannot connect to the database. The error message shown is
Connection parameter name is unknown: Initial Catalog
1) What do I have to do to improve the performance of my queries? At the moment UniDAC is 15(!) times slower than MS SQL Server Management Studio.
2) How do I set the Provider in the connect string correctly?
Re: V6.2.8: Error parsing TUniConnection::ConnectString
Posted: Mon 21 Sep 2015 10:04
by azyk
Thank you for your note.
1) Unfortunately, we can't reproduce the described problem. Could you provide test access to your SQL Server and the SQL query reproducing the behavior to andreyz*devart*com ?
2) We have reproduced and fixed the described bug. The fix will be included in the next UniDAC build.
As a workaround, use the OLEDBProvider option instead of Provider in the connection string. A connection string with this correction will be like the following:
Code: Select all
Provider Name=SQL Server;OLEDBProvider=SQLNCLI.1;Data Source=W7-WS-0050\MSSQL;Initial Catalog=test;Port=0;User ID=sa;Password=xxxx;Login Prompt=False
Re: V6.2.8: Error parsing TUniConnection::ConnectString
Posted: Mon 21 Sep 2015 14:09
by GNiewerth
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.
Re: V6.2.8: Error parsing TUniConnection::ConnectString
Posted: Wed 23 Sep 2015 10:36
by azyk
Please send to andreyz*devart*com the complete test sample you used to compare performance of ODBC and UniDAC. Include scripts for creating and filling in the test tables. In addition, specify the exact ODBC driver version you used for the test.
Re: V6.2.8: Error parsing TUniConnection::ConnectString
Posted: Fri 02 Oct 2015 08:37
by GNiewerth
Any news on this issue?
Re: V6.2.8: Error parsing TUniConnection::ConnectString
Posted: Thu 08 Oct 2015 12:37
by azyk
We have composed a test sample using your project parts. We have tested performance of UniDAC and ODBC using our test sample (data source uses the SQL Server Native Client 11.0 Provider). However, test results didn't show the difference between UniDAC and ODBC performance, you described.
Please try to create a separate test case project reproducing the described issue with performance - and send it to me.
Re: V6.2.8: Error parsing TUniConnection::ConnectString
Posted: Tue 17 Nov 2015 08:51
by GNiewerth
Hello azyk,
on 27.10 I sent you an email containing a download link to our sample database. Were you able to download, install and reproduce the behaviour I described?
Re: V6.2.8: Error parsing TUniConnection::ConnectString
Posted: Tue 17 Nov 2015 14:22
by AlexP
Please resend link application to support*devart*com.