Configure TUniConnection to use Native Client in SQL Server 2014

[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.

[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.

[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;

[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.

[ertank]

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]

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]

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]

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]

I already sent the demo, and a small database.

[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.

[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.