Delphi with SQL Server: OLEDB vs. Native Client drivers

Question

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.

Solution 1

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.

Solution 2

I think you should concentrate on optimizing the:

• sql server engine and database settings
• your queries
• your data schema

Difference in speed between connection libraries is so small, even negligible, that it may cause a very tiny slowdown of systems and in very specific scenarios

Solution 3

1. In your question you are mxing OLE and SQL Native Client. Probably you are mean few things in the same time:

   • OLE -> OLEDB, which is obsolescent generic data access technology;
   • OLE -> "SQL Server OLEDB Provider", which is SQL Server 2000 OLEDB provider;
   • SQL Server Native Client, which is SQL Server 2005 and higher client software. And it includes as OLEDB provider, as ODBC driver.

2. If to talk about OLEDB providers and supported SQL Server versions, then:

   • "SQL Server OLEDB Provider" (SQLOLEDB) supports SQL Server 2000 protocol;
   • "SQL Server Native Client 9" (SQLNCLI) supports SQL Server 2000 and 2005 protocols;
   • "SQL Server Native Client 10" supports SQL Server 2000, 2005 and 2008 protocols.

   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.

3. Abstractly comparing, I can only speculate about differences between SQLNCLI and SQLOLEDB:

   • One is more correctly uses server protocol;
   • One is using more advanced protocol features;
   • One performs more processing, what heps to handle more situations;
   • One uses more generic / optimized data represenation.

   Without correct benchmark application and environment it is hard to accept your comparision results, because they may depend on multiple factors.

Solution 4

Short answer:
It doesn't matter.

Long answer:
The difference in performance between the 2 client libs is relatively negligible compared to the Server execution + Network data transfer, which is what you are mostly measuring, hence the inconclusive test data. There is a good chance that you use the same low level layer in both cases anyway with only a minor difference in indirection on top of it.

As a matter of fact, if your tests show no visible difference, it just proves that the slowness is not related with the choice of the client lib and optimization should be searched elsewhere.

For your present test, you should use the SQL Profiler to measure the queries execution time on the Server at the same time you run your test, you would see that they vary also quite a bit. Subtracting those numbers from the test end results would give you the timing for the bundle Client time + Network transfer.

Network performance is quite variable and has more impact on your test than you would think. Try having someone streaming video at the same time you run your test and you will see... (Have had that with my former company; tuning the SQL was not the answer in this case )

Solution 5

While it certainly could be at the database end, I think there is a lot to look at in the overall system - at least your test system. In general, it is hard to do timing if the work you are asking the database to do is very small compared to the overall work. So in general, is the database task a big job or simply the retrieval of one data item? Are you using stored procedures or simple queries? Is your test preparing any stored procedures before running the test? Do you get consistent times each time you run any test in sucession?

View more solutions

Comments

[kobik]

The main thing is to properly handle PKs, Indexed, Clustered Indexes, Locking correctly, working efficiently with Cursors, optimizing your queries, testing solution plans etc... The client driver is the least significant in terms of performance.

[kobik]

You might see different results in load tests when running the client with the same driver. SQL might cache the query / view / indexes, differences in current connection speed etc... my point was that changing Client driver to increase speed wont help much. It's just a side comment.

[mjn]

According to this article - Microsoft is Aligning with ODBC for Native Relational Data Access, OLE DB will disappear for SQL server in favor of ODBC (please correct me if I am wrong)

[Arnaud Bouchez]

I've measured on real data direct connection to Oracle OCI to be 3 to 5 times faster than using the official OleDB provider from Oracle, or 10 times faster than good old BDE TQuery. This is not negligible. I'm speaking of real data. Perhaps less visible with SQL Server, but I've read on some benchmark that ODBC is faster than OleDB in some cases (because it's lighter). With today's modern DBs, client side can be the real bottleneck, when retrieving a huge amount of rows.