Configure TUniConnection to use Native Client in SQL Server 2014

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Ivan_Carpio89
Posts: 29
Joined: Mon 10 Dec 2018 23:49

Configure TUniConnection to use Native Client in SQL Server 2014

Post by Ivan_Carpio89 » Tue 11 Dec 2018 18:32

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.

Stellar
Devart Team
Posts: 215
Joined: Tue 03 Oct 2017 11:00

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

Post by Stellar » Wed 12 Dec 2018 11:33

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.

Ivan_Carpio89
Posts: 29
Joined: Mon 10 Dec 2018 23:49

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

Post by Ivan_Carpio89 » Wed 12 Dec 2018 15:59

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;

Ivan_Carpio89
Posts: 29
Joined: Mon 10 Dec 2018 23:49

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

Post by Ivan_Carpio89 » Wed 12 Dec 2018 17:14

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.

ertank
Posts: 135
Joined: Wed 13 Jan 2016 16:00

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

Post by ertank » 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.

Ivan_Carpio89
Posts: 29
Joined: Mon 10 Dec 2018 23:49

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

Post by Ivan_Carpio89 » Wed 12 Dec 2018 17:47

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!

Ivan_Carpio89
Posts: 29
Joined: Mon 10 Dec 2018 23:49

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

Post by Ivan_Carpio89 » Wed 12 Dec 2018 17:55

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.

Stellar
Devart Team
Posts: 215
Joined: Tue 03 Oct 2017 11:00

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

Post by Stellar » Thu 13 Dec 2018 15:19

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

Ivan_Carpio89
Posts: 29
Joined: Mon 10 Dec 2018 23:49

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

Post by Ivan_Carpio89 » Thu 13 Dec 2018 19:19

I already sent the demo, and a small database.

Stellar
Devart Team
Posts: 215
Joined: Tue 03 Oct 2017 11:00

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

Post by Stellar » Fri 14 Dec 2018 15:05

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.

Ivan_Carpio89
Posts: 29
Joined: Mon 10 Dec 2018 23:49

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

Post by Ivan_Carpio89 » Fri 14 Dec 2018 18:39

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.

Post Reply