Devart Forums

Discussion forums for open issues and questions concerning database tools, data access components and developer tools from Devart
https://forums.devart.com/

Configure TUniConnection to use Native Client in SQL Server 2014

https://forums.devart.com/viewtopic.php?f=28&t=38161

Page 1 of 1

Configure TUniConnection to use Native Client in SQL Server 2014

Posted: Tue 11 Dec 2018 18:32
by Ivan_Carpio89
Hello,

What configuration should I make to the TUniConnection to connect to the Native Client library?

I tried to do it by configuring the following:

UniConnection -> Options:
Provider = prNativeClient
NativeClientVersion = nc2012

However, when executing the following query:

Code: Select all

SELECT client_interface_name
FROM sys.dm_exec_connections
WHERE session_id = 56 --My session id
Indicates that it is OLEDB, and ODBC should be displayed.

Any recommendation?

Thanks in advance.

Re: Configure TUniConnection to use Native Client in SQL Server 2014

Posted: Wed 12 Dec 2018 11:33
by Stellar
SNAC, or SQL Server Native Client, is a term that has been used interchangeably to refer to ODBC and OLE DB drivers for SQL Server. We get access to Native Client library using OLE DB interface.
To connect to the server using Microsoft SQL Server Native Client 11.0, you should specify the following options:

Code: Select all

UniConnection1.SpecificOptions.Values['SQL Server.Provider'] := 'prNativeClient';
UniConnection1.SpecificOptions.Values['SQL Server.NativeClientVersion'] := 'nc2012';
UniConnection1.Connect;
If the specified version of the Native Client is not installed on the computer, UniDAC will generate a corresponding exception when attempting to connect.

Re: Configure TUniConnection to use Native Client in SQL Server 2014

Posted: Wed 12 Dec 2018 15:59
by Ivan_Carpio89
OK thanks.
My question arose based on a test that I am doing, which consists of the following:

Code: Select all

procedure TFTest.Test1;
var i: Integer; dTime, dtStart: TDateTime;
begin
   dtStart:= Now();
   for i:= 0 to 1000 do begin
      UniQuery1.Close;
      UniQuery1.SQL.Clear;
      UniQuery1.Params.Clear;
      UniQuery1.SQL.Add('SELECT * FROM TEST_TABLE');  
      UniQuery1.SQL.Add('WHERE COL1 >= ''2018-08-20 00:00:00.000'' AND COL2 = :pCOL2');
      UniQuery1.SQL.Add('ORDER BY COL3 DESC');
      UniQuery1.Params.CreateParam(ftString, 'pCOL2', ptInput).AsString:= 'F';
      UniQuery1.Open;
      While not UniQuery1.Eof do begin
         dTime:= UniQuery1.FieldByName('COL1').AsDateTime;
	 UniQuery1.Next;
      end;//While
   end;//for
   ShowMessage(FormatDateTime('hh:mm:ss.zzz', Start-Now));// 10.5 seconds
end;//Test1
The duration of the previous test is 10.5 seconds. Exactly that same test I did it with a TFDQuery,
and his duarion was 2.5 seconds.

The first difference I found is that the connection of the TFDConnection
it appears to me as ODBC and that of TUniConnection as OLEDB.

I'm interested in the UniDAC components. Any recommendations to configure the TUniQuery?

The TFDQuery configuration is as follows:

TFDQuery:
FDQuery1.FetchOptions.Mode:= fmAll;
FDQuery1.CachedUpdates:= True;
FDQuery1.UpdateOptions.LockMode:= lmNone;
FDQuery1.FetchOptions.AutoClose:= False;
FDQuery1.ResourceOptions.ParamCreate:= False;

Re: Configure TUniConnection to use Native Client in SQL Server 2014

Posted: Wed 12 Dec 2018 17:14
by Ivan_Carpio89
Hello, I already found the reason for my performance problem in UniDAC, and it is the following property:

Code: Select all

UniQuery1.FetchRows:= 65535;//By testing I set this property
With the previous configuration it takes 10.5 seconds, but in the following way:

Code: Select all

UniQuery1.FetchRows:= 10;//with this config. it takes 2.5 seconds
It already takes 2.5 seconds. Just one question, is there the possibility that I also use the SNAC ODBC interface with UniDAC?

Thanks for your support.

Re: Configure TUniConnection to use Native Client in SQL Server 2014

Posted: Wed 12 Dec 2018 17:34
by ertank
Ivan_Carpio89 wrote: ↑Wed 12 Dec 2018 17:14 Hello, I already found the reason for my performance problem in UniDAC, and it is the following property:
Out of my curiosity, how many records do you have in your result set?

As to my knowledge, You should also set Options of your TUniQuery and set FetchAll to False for your parameter to actually be used.

Again, as to my knowledge:
- UniDAC automatically creates parameters after you set SQL property.
- UniDAC understands it is input because you are assigning a value.
- UniDAC understands it's datatype is string because you are using "AsString" in your assignment.
So, you can do your parameter assignment just as below:

Code: Select all

UniQuery.ParamByName('pCOL1').AsString := 'F';
You should always refer to documentation. However, above code should be just fine.

Re: Configure TUniConnection to use Native Client in SQL Server 2014

Posted: Wed 12 Dec 2018 17:47
by Ivan_Carpio89
ertank wrote: ↑Wed 12 Dec 2018 17:34
Ivan_Carpio89 wrote: ↑Wed 12 Dec 2018 17:14 Hello, I already found the reason for my performance problem in UniDAC, and it is the following property:
Out of my curiosity, how many records do you have in your result set?

As to my knowledge, You should also set Options of your TUniQuery and set FetchAll to False for your parameter to actually be used.

Again, as to my knowledge:
- UniDAC automatically creates parameters after you set SQL property.
- UniDAC understands it is input because you are assigning a value.
- UniDAC understands it's datatype is string because you are using "AsString" in your assignment.
So, you can do your parameter assignment just as below:

Code: Select all

UniQuery.ParamByName('pCOL1').AsString := 'F';
You should always refer to documentation. However, above code should be just fine.
Sorry, in my example the query has TOP 100, I missed adding the TOP.

Regarding the parameters, he created it manually because the TUniQuery configured it like this:

UniQuery1.ParamsCheck: = False;

But regardless if the parameter is created manually or is created automatically, the performance problem falls on the FetcRows property.

Thank you!

Re: Configure TUniConnection to use Native Client in SQL Server 2014

Posted: Wed 12 Dec 2018 17:55
by Ivan_Carpio89
Just one question, is there the possibility that I also use the SNAC ODBC interface with UniDAC?

My question is important for me, because the total duration of the 1000 queries, that is, it adds the duration of each of the queries, the result is as follows:

1.- FireDAC (Native Client ODBC): 1.7 seconds
2.- UniDAC (Native Client OLEDB): 1.9 seconds

NOTE 1: To obtain the total duration, export the result of the SQL Server Profiler to a table, and with a query I obtained the total duration.

NOTE 2: It seems a very small difference, however, in an ERP with a large number of queries, it is important.

Thanks in advance.

Re: Configure TUniConnection to use Native Client in SQL Server 2014

Posted: Thu 13 Dec 2018 15:19
by Stellar
We compared the performance of UniDAC and FireDAC using your sample code, but the results testing are not in favor of FireDAC.
To investigate the speed of UniDAC, provide us with a small sample and send it to us, including scripts for creating database objects.
You can send the sample using the contact form at our site: devart.com/company/contactform.html

We also do not recommend using parameters in simple queries. We have not once investigated the speed of execution of parameterized queries, and always came to the same conclusion that the execution of simple queries without parameters is faster than the performance of the same parameterized queries.

More information about improving UniDAC performance:
https://www.devart.com/unidac/docs/incr ... rmance.htm

Re: Configure TUniConnection to use Native Client in SQL Server 2014

Posted: Thu 13 Dec 2018 19:19
by Ivan_Carpio89
I already sent the demo, and a small database.

Re: Configure TUniConnection to use Native Client in SQL Server 2014

Posted: Fri 14 Dec 2018 15:05
by Stellar
Disable the DisconnectedMode option for TUniConnection. In your case, it is not advisable to use DisconnectedMode, since a large number of queries are executed in a relatively short time.
Learn more about DisconnectedMode: devart.com/unidac/docs/devart.dac.tdaconnectionoptions.disconnectedmode.htm

When testing the performance, you use a remote or local server. If you are using a local server, then the reason for the improved performance of FireDAC may be the use of the Shared Memory protocol.

You can try adding an index for the TIEMPO_TOTAL field, this will be a significant increase of performance for this SQL expression.

Re: Configure TUniConnection to use Native Client in SQL Server 2014

Posted: Fri 14 Dec 2018 18:39
by Ivan_Carpio89
My connection is local, and by instance name:
When testing the performance, you use a remote or local server. If you are using a local server, then the reason for the improved performance of FireDAC may be the use of the Shared Memory protocol.
I did my tests again:

1) I disabled the "DisconnectedMode" option
Disable the DisconnectedMode option for TUniConnection
2) I disabled the "Shared Memory" protocol for the instance of SQL Server and restarted the service so that it would take effect.

The result of my test was exactly the same, having a difference of 1 second in favor of FireDAC. Any suggestions?

And thanks for the suggestion to add an index, but for both tests the SQL's have the same environment and its execution is on the same scenario, that is, the only difference is UniDAC (Native Client with OLEDB) and FireDAC (Native Client with ODBC) ):
You can try adding an index for the TIME_TOTAL field, this will be a significant increase of performance for this SQL expression.
I thank you for your support.
All times are UTC
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/