I have been told that SQL Native Client is supposed to be faster than the OLEDB drivers. So I put together a utility to do a load-test between the two - and am getting mixed results. Sometimes one is faster, sometimes the other is, no matter what the query may be (simple select, where clause, joining, order by, etc.). Of course the server does the majority of the workload, but I'm interested in the time it takes between the data coming into the PC to the time the data is accessible within the app.
The load tests consist of very small queries which return very large datasets. For example, I do select * from SysTables
and this table has 50,000+ records. After receiving the data, I do another load of looping through the results (using while not Q.eof ... Q.next ...
etc.). I've also tried adding some things to the query - such as order by Val
where Val
is a varchar(100)
field.
Here's a sample of my load tester, numbers on very bottom are averages...
So really, what are the differences between the two? I do know that OLE is very flexible and supports many different database engines, whereas Native Client is specific to SQL Server alone. But what else is going on behind the scenes? And how does that affect how Delphi uses these drivers?
This is specifically using ADO via the TADOConnection
component and TADOQuery
as well.
I'm not necessarily looking or asking for ways to improve performance - I just need to know what are the differences between the drivers.
As stated by Microsoft:
SQL Server Native Client is a stand-alone data access application programming interface (API), used for both OLE DB and ODBC, that was introduced in SQL Server 2005. SQL Server Native Client combines the SQL OLE DB provider and the SQL ODBC driver into one native dynamic-link library (DLL).
From my understanding, ADO is just an Object Oriented application-level DB layer over OleDB. It will use OleDB in all cases. What changes is the provider used. If you specify the SQLNCLI10
provider, you'll use the latest version of the protocol. If you specify the SQLOLEDB
provider, you'll use the generic SQL Server 2000 + protocol.
As such:
ADO -> OleDB -> SQLNCLI10 provider -> MS SQL Server (MSSQL 2000, 2005 or 2008 protocol)
ADO -> OleDB -> SQLOLEDB provider -> MS SQL Server (MSSQL 2000 protocol)
About performance, I think you won't have a big difference. Like always, it will depend on the data processed.
But it is IMHO recommended to use best fitted provider for your database. Some kind of data (like var(maxchar)
or Int64
) is told to be best handled. And the SQLNCLI10
provider has been updated, so I guess it is more tuned.
In your question you are mxing OLE and SQL Native Client. Probably you are mean few things in the same time:
If to talk about OLEDB providers and supported SQL Server versions, then:
You did not sayd what SQL Server version you are using. In general, best is to use SQL Server OLEDB provider corresponding to your SQL Server version. Otherwise you can run into incompatibility between server and client versions.
Abstractly comparing, I can only speculate about differences between SQLNCLI and SQLOLEDB:
Without correct benchmark application and environment it is hard to accept your comparision results, because they may depend on multiple factors.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With