Delphi与SQL Server：OLEDB与Native Client驱动程序 [英] Delphi with SQL Server: OLEDB vs. Native Client drivers
问题描述
我被告知SQL Native Client应该比OLEDB驱动程序要快。所以我把一个实用程序放在一起，在两者之间进行一个负载测试，并得到混合的结果。有时一个更快，有时候是另一个，无论查询可能是什么（简单选择，where子句，加入，排序等）。当然，服务器做大部分的工作负载，但是我对PC中的数据到应用程序内访问数据的时间感兴趣。
负载测试由返回非常大的数据集的非常小的查询组成。例如，我从SysTables 中执行 select *，此表具有50,000个以上的记录。收到数据后，我再次执行循环结果（使用而不是$ code> Q.eof ... Q.next ... 等）。我也尝试在查询中添加一些内容，例如Val 中的，其中 Val 是一个 varchar（100）字段。
以下是我的负载测试仪的示例，底部的数字是平均值...
真的，两者有什么区别？我知道OLE是非常灵活的，并且支持许多不同的数据库引擎，而Native Client则仅针对SQL Server。但幕后还有什么事情呢？而且如何影响Delphi如何使用这些驱动程序？
这是通过 TADOConnection 组件和 TADOQuery 。
我不一定在寻找或要求提高绩效的方法 - 我只是需要知道驱动程序之间有什么区别。
As 由Microsoft指定：
SQL Server Native客户端是用于SQL Server 2005中引入的OLE DB和ODBC的独立数据访问应用程序编程接口（API）。SQL Server Native Client将SQL OLE DB提供程序和SQL ODBC驱动程序组合为一个本机动态-link library（DLL）。
根据我的理解，ADO只是OleDB上面向对象的应用程序级DB层。在所有情况下都会使用OleDB。提供者使用什么变化。如果您指定 SQLNCLI10 提供程序，则将使用最新版本的协议。如果您指定 SQLOLEDB 提供程序，则将使用通用SQL Server 2000 +协议。
因此：
ADO - > OleDB - > SQLNCLI10提供者 - > MS SQL Server（MSSQL 2000,2005或2008协议）
ADO - > OleDB - > SQLOLEDB提供者 - > MS SQL Server（MSSQL 2000协议）
关于性能，我想你不会有很大的不同。像往常一样，它将取决于处理的数据。
但是，IMHO建议为数据库使用最适合的提供者。被告知某些数据（如 var（maxchar）或 Int64 ）被最好地处理。而$ code> SQLNCLI10 提供者已经更新，所以我想这是更多的调整。
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.
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.
这篇关于Delphi与SQL Server：OLEDB与Native Client驱动程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
