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.
我被告知SQL本机客户端应该比OLEDB驱动程序要快。因此，我将一个实用程序放在一起，在两者之间进行负载测试，并且得到了混合的结果。有时一个更快，有时另一个更快，不管查询是什么(简单的select、where子句、join、order by等)。当然，服务器负责大部分工作负载，但我对从数据进入PC到应用中数据可访问的时间感兴趣。
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.
负载测试由非常小的查询组成，查询返回非常大的数据集。例如，我从SysTables中选择*，这个表有50,000+记录。在接收到数据之后，我将在结果中执行另一个循环(使用而不是Q)。eof……Q。下一个……等等)。我还尝试向查询添加一些东西——比如Val的order，其中Val是varchar(100)字段。
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?
那么，这两者之间有什么区别呢?我知道OLE非常灵活，支持许多不同的数据库引擎，而原生客户端仅针对SQL Server。但幕后还发生了什么?这对Delphi如何使用这些驱动程序有什么影响?
This is specifically using ADO via the TADOConnection component and TADOQuery as well.
这是通过TADOConnection组件和TADOQuery特别使用ADO。
I'm not necessarily looking or asking for ways to improve performance - I just need to know what are the differences between the drivers.
我并不需要寻找或寻求提高性能的方法——我只需要知道驱动之间的区别。
8 个解决方案
#1
7
微软是这样陈述的:
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).
SQL Server Native Client是一个独立的数据访问应用程序编程接口(API)，用于OLE DB和ODBC，它是SQL Server 2005中引入的。SQL Server本地客户端将SQL OLE DB提供程序和SQL ODBC驱动程序组合到一个本地动态链接库(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.
在我看来，ADO只是OleDB上面向对象的应用程序层DB。它将在所有情况下使用OleDB。所使用的是提供程序的更改。如果指定SQLNCLI10提供程序，您将使用协议的最新版本。如果您指定了SQLOLEDB提供者，那么您将使用通用的SQL Server 2000 +协议。
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.
但是IMHO建议为您的数据库使用最合适的提供者。某些类型的数据(如var(maxchar)或Int64)被告知最好处理。而且SQLNCLI10提供程序已经被更新，所以我想它应该更加优化了。
#2
5
-
In your question you are mxing OLE and SQL Native Client. Probably you are mean few things in the same time:
在您的问题中，您是mxing OLE和SQL原生客户端。也许你在同一时间是没有什么意义的:
- OLE -> OLEDB, which is obsolescent generic data access technology;
- OLE -> OLEDB是一种过时的通用数据访问技术;
- OLE -> "SQL Server OLEDB Provider", which is SQL Server 2000 OLEDB provider;
- OLE ->“SQL Server OLEDB Provider”，即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.
- SQL Server本机客户端，它是SQL Server 2005和更高的客户端软件。它包括作为OLEDB提供程序，作为ODBC驱动程序。
-
If to talk about OLEDB providers and supported SQL Server versions, then:
如果要讨论OLEDB提供程序并支持SQL Server版本，那么:
- "SQL Server OLEDB Provider" (SQLOLEDB) supports SQL Server 2000 protocol;
- “SQL Server OLEDB Provider”(SQLOLEDB)支持SQL Server 2000协议;
- "SQL Server Native Client 9" (SQLNCLI) supports SQL Server 2000 and 2005 protocols;
- “SQL Server Native Client 9”(SQLNCLI)支持SQL Server 2000和2005协议;
- "SQL Server Native Client 10" supports SQL Server 2000, 2005 and 2008 protocols.
- “SQL Server Native Client 10”支持SQL Server 2000、2005和2008协议。
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.
您没有说您使用的是什么SQL Server版本。通常，最好使用与SQL服务器版本相对应的SQL Server OLEDB提供程序。否则，您可能会遇到服务器和客户端版本之间的不兼容性。
-
Abstractly comparing, I can only speculate about differences between SQLNCLI and SQLOLEDB:
抽象地比较，我只能推测SQLNCLI和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.
如果没有正确的基准应用程序和环境，就很难接受比较结果，因为它们可能取决于多个因素。
#3
5
I think you should concentrate on optimizing the:
我认为你应该集中精力优化:
- sql server engine and database settings
- sql服务器引擎和数据库设置
- 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
连接库之间的速度差异非常小，甚至可以忽略不计，因此可能会导致系统和特定场景的非常小的放缓
#4
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.
Long answer:与服务器执行+网络数据传输相比，2个客户端libs的性能差异相对来说可以忽略不计，这是您主要测量的，因此没有决定性的测试数据。很有可能你在这两种情况下都使用了相同的低电平层，在它上面只有很小的间接差异。
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.
对于当前的测试，您应该使用SQL Profiler来度量服务器上运行测试时的查询执行时间，您会发现它们也有很大的不同。从测试端结果中减去这些数字将为绑定客户端时间+网络传输提供时间。
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 )
网络性能是相当多变的，它对你的测试的影响比你想象的要大。试着让某人在你运行测试的同时播放视频，你会看到……(我和以前的公司有过这样的经历;在这种情况下，调优SQL不是答案)
#5
3
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?
虽然它肯定是在数据库端，但我认为在整个系统中有很多东西要看——至少是您的测试系统。一般来说，如果您要求数据库做的工作与总体工作相比非常小，那么就很难进行计时。因此，一般来说，数据库任务是一项大任务，还是仅仅是检索一个数据项?您使用的是存储过程还是简单的查询?在运行测试之前，您的测试是否准备了任何存储过程?每次你在sucession上运行测试时，你得到的是一致的时间吗?
#6
1
- The query execution time tells you how well the database engine (and any schema/query optimization) work well. Here what you use doesn't matter. ODBC/OLEDB/Native whatever just passes the query along to the database and it is executed there
- 查询执行时间告诉您数据库引擎(以及任何模式/查询优化)的工作情况。这里你用什么并不重要。ODBC/OLEDB/Native都会将查询传递到数据库，并在那里执行
- The time it takes to read from the first record to the last tells you how well the data access layer and your network perfom. Here you time how well data are returned and "cached" on your client. Depending on the data, the network settings may be important. For example if your tables use "large" records, a larger MTU may requires less packets (and less roundtrips) to send them to the client.
- 从第一个记录读取到最后一个记录所需的时间告诉您数据访问层和您的网络性能有多好。在这里，您将了解如何将数据返回和“缓存”到客户机上。根据数据，网络设置可能很重要。例如，如果您的表使用“大”记录，较大的MTU可能需要更少的包(和更少的往返)将它们发送到客户端。
Anyway, before looking for a solution, you have to identify the problem. Profile your application, both client side and server side (SQL Server has good tools for that), and find what exactly makes it slower. Then and only then you can look for the correct solution. Maybe the data access layer is not the problem. 20,000 records is a small dataset today, not a large one.
无论如何，在寻找解决方案之前，您必须确定问题所在。概要说明您的应用程序，包括客户端和服务器端(SQL server有很好的工具)，并找出究竟是什么使其速度变慢。只有这样，你才能找到正确的解决方案。也许数据访问层不是问题所在。现在20000条记录是一个小数据集，而不是一个大数据集。
#7
1
You cannot use the native clients with ADO, as is.
您不能像往常一样使用ADO的本机客户端。
ADO does not understand the XML SQL Server data type. The field type:
ADO不理解XML SQL Server数据类型。字段类型:
field: ADOField;
field := recordset.Fields.Items["SomeXmlColumn"];
Attempting to access field.Value throws an EOleException:
试图访问字段。抛出一个EOleException价值:
- Source: Microsoft Cursor Engine
- 来源:微软光标引擎
- ErrorCode: 0x80040E21 (E_ITF_0E21)
- 错误代码:0 x80040e21(E_ITF_0E21)
- Message: Multiple-step operation generated errors. Check each status value
- 消息:多步操作产生错误。检查每一个状态值
The native client drivers (e.g. SQLNCLI, SQLNCLI10, SQLNCLI11) present an Xml data type to ADO as
本机客户端驱动程序(例如SQLNCLI、SQLNCLI10、SQLNCLI11)将Xml数据类型呈现给ADO as
field.Type_ = 141
While the legacy SQLOLEDB driver presents an Xml data type to ADO as adLongVarWChar, a unicode string:
当遗留的SQLOLEDB驱动程序向ADO提供Xml数据类型adLongVarWChar时，unicode字符串:
field.Type_ = 203 //adLongVarWChar
And the VARIANT contained in field.Value is a WideString (technically known as a BSTR):
以及场中包含的变量。值是一个宽字符串(技术上称为BSTR):
TVarData(field.Value).vtype = 8 //VT_BSTR
The solution, as noted by Microsoft:
解决方案，如微软所言:
Using ADO with SQL Server Native Client
Existing ADO applications can access and update XML, UDT, and large value text and binary field values using the SQLOLEDB provider. The new larger varchar(max), nvarchar(max), and varbinary(max) data types are returned as the ADO types adLongVarChar, adLongVarWChar and adLongVarBinary respectively. XML columns are returned as adLongVarChar, and UDT columns are returned as adVarBinary. However, if you use the SQL Server Native Client OLE DB provider (SQLNCLI11) instead of SQLOLEDB, you need to make sure to set the DataTypeCompatibility keyword to "80" so that the new data types will map correctly to the ADO data types.
现有的ADO应用程序可以使用SQLOLEDB提供程序访问和更新XML、UDT、大值文本和二进制字段值。新的更大的varchar(max)、nvarchar(max)和varbinary(max)数据类型分别作为ADO类型adLongVarChar、adLongVarWChar和adLongVarBinary返回。XML列作为adLongVarChar返回，UDT列作为adVarBinary返回。但是，如果您使用的是SQL Server本地客户端OLEDB提供者(SQLNCLI11)而不是SQLOLEDB，您需要确保将datatypecompatiable关键字设置为“80”，以便新的数据类型能够正确地映射到ADO数据类型。
They also note:
他们还指出:
If you do not need to use any of the new features introduced in SQL Server 2005, there is no need to use the SQL Server Native Client OLE DB provider; you can continue using your current data access provider, which is typically SQLOLEDB.
如果您不需要使用SQL Server 2005中引入的任何新特性，那么就不需要使用SQL Server本地客户机OLE DB提供程序;您可以继续使用当前的数据访问提供程序(通常是SQLOLEDB)。
#8
0
Also, besides the lack of support for the XML data type, Delphi ADO does not recognize columns defined in SQL Server as TIME (DBTYPE_DBTIME2=145) or DATETIMEOFFSET (DBTYPE_DBTIMESTAMPOFFSET=146); trying to use those fields in your application will cause multiple errors like 'Invalid Variant Value' or some controls (like TDBGrid) will simply drop the field entirely.
此外，除了缺乏对XML数据类型的支持之外，Delphi ADO不承认SQL Server中定义的列是TIME (DBTYPE_DBTIME2=145)或DATETIMEOFFSET (DBTYPE_DBTIMESTAMPOFFSET=146);尝试在应用程序中使用这些字段会导致多个错误，比如“无效的变量值”或一些控件(如TDBGrid)将完全删除该字段。
Seems like the lack of support for DBTYPE_DBTIME2=145 is a bug/QC-issue since there is already ftTime support (it's also not clear to me why SQL Server doesn't return DBTYPE_DBTIME which Delphi does support), the XML and Offset types have no clear TFieldType mapping.
似乎缺少对DBTYPE_DBTIME2=145的支持是一个bug/ qc问题，因为已经有ftTime支持(我也不清楚为什么SQL Server不返回DBTYPE_DBTIME，而Delphi支持DBTYPE_DBTIME)， XML和偏移类型没有清晰的TFieldType映射。
Data Type Support for OLE DB Date/Time Improvements
数据类型支持OLE DB日期/时间的改进
#1
7
微软是这样陈述的:
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).
SQL Server Native Client是一个独立的数据访问应用程序编程接口(API)，用于OLE DB和ODBC，它是SQL Server 2005中引入的。SQL Server本地客户端将SQL OLE DB提供程序和SQL ODBC驱动程序组合到一个本地动态链接库(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.
在我看来，ADO只是OleDB上面向对象的应用程序层DB。它将在所有情况下使用OleDB。所使用的是提供程序的更改。如果指定SQLNCLI10提供程序，您将使用协议的最新版本。如果您指定了SQLOLEDB提供者，那么您将使用通用的SQL Server 2000 +协议。
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.
但是IMHO建议为您的数据库使用最合适的提供者。某些类型的数据(如var(maxchar)或Int64)被告知最好处理。而且SQLNCLI10提供程序已经被更新，所以我想它应该更加优化了。
#2
5
-
In your question you are mxing OLE and SQL Native Client. Probably you are mean few things in the same time:
在您的问题中，您是mxing OLE和SQL原生客户端。也许你在同一时间是没有什么意义的:
- OLE -> OLEDB, which is obsolescent generic data access technology;
- OLE -> OLEDB是一种过时的通用数据访问技术;
- OLE -> "SQL Server OLEDB Provider", which is SQL Server 2000 OLEDB provider;
- OLE ->“SQL Server OLEDB Provider”，即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.
- SQL Server本机客户端，它是SQL Server 2005和更高的客户端软件。它包括作为OLEDB提供程序，作为ODBC驱动程序。
-
If to talk about OLEDB providers and supported SQL Server versions, then:
如果要讨论OLEDB提供程序并支持SQL Server版本，那么:
- "SQL Server OLEDB Provider" (SQLOLEDB) supports SQL Server 2000 protocol;
- “SQL Server OLEDB Provider”(SQLOLEDB)支持SQL Server 2000协议;
- "SQL Server Native Client 9" (SQLNCLI) supports SQL Server 2000 and 2005 protocols;
- “SQL Server Native Client 9”(SQLNCLI)支持SQL Server 2000和2005协议;
- "SQL Server Native Client 10" supports SQL Server 2000, 2005 and 2008 protocols.
- “SQL Server Native Client 10”支持SQL Server 2000、2005和2008协议。
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.
您没有说您使用的是什么SQL Server版本。通常，最好使用与SQL服务器版本相对应的SQL Server OLEDB提供程序。否则，您可能会遇到服务器和客户端版本之间的不兼容性。
-
Abstractly comparing, I can only speculate about differences between SQLNCLI and SQLOLEDB:
抽象地比较，我只能推测SQLNCLI和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.
如果没有正确的基准应用程序和环境，就很难接受比较结果，因为它们可能取决于多个因素。
#3
5
I think you should concentrate on optimizing the:
我认为你应该集中精力优化:
- sql server engine and database settings
- sql服务器引擎和数据库设置
- 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
连接库之间的速度差异非常小，甚至可以忽略不计，因此可能会导致系统和特定场景的非常小的放缓
#4
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.
Long answer:与服务器执行+网络数据传输相比，2个客户端libs的性能差异相对来说可以忽略不计，这是您主要测量的，因此没有决定性的测试数据。很有可能你在这两种情况下都使用了相同的低电平层，在它上面只有很小的间接差异。
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.
对于当前的测试，您应该使用SQL Profiler来度量服务器上运行测试时的查询执行时间，您会发现它们也有很大的不同。从测试端结果中减去这些数字将为绑定客户端时间+网络传输提供时间。
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 )
网络性能是相当多变的，它对你的测试的影响比你想象的要大。试着让某人在你运行测试的同时播放视频，你会看到……(我和以前的公司有过这样的经历;在这种情况下，调优SQL不是答案)
#5
3
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?
虽然它肯定是在数据库端，但我认为在整个系统中有很多东西要看——至少是您的测试系统。一般来说，如果您要求数据库做的工作与总体工作相比非常小，那么就很难进行计时。因此，一般来说，数据库任务是一项大任务，还是仅仅是检索一个数据项?您使用的是存储过程还是简单的查询?在运行测试之前，您的测试是否准备了任何存储过程?每次你在sucession上运行测试时，你得到的是一致的时间吗?
#6
1
- The query execution time tells you how well the database engine (and any schema/query optimization) work well. Here what you use doesn't matter. ODBC/OLEDB/Native whatever just passes the query along to the database and it is executed there
- 查询执行时间告诉您数据库引擎(以及任何模式/查询优化)的工作情况。这里你用什么并不重要。ODBC/OLEDB/Native都会将查询传递到数据库，并在那里执行
- The time it takes to read from the first record to the last tells you how well the data access layer and your network perfom. Here you time how well data are returned and "cached" on your client. Depending on the data, the network settings may be important. For example if your tables use "large" records, a larger MTU may requires less packets (and less roundtrips) to send them to the client.
- 从第一个记录读取到最后一个记录所需的时间告诉您数据访问层和您的网络性能有多好。在这里，您将了解如何将数据返回和“缓存”到客户机上。根据数据，网络设置可能很重要。例如，如果您的表使用“大”记录，较大的MTU可能需要更少的包(和更少的往返)将它们发送到客户端。
Anyway, before looking for a solution, you have to identify the problem. Profile your application, both client side and server side (SQL Server has good tools for that), and find what exactly makes it slower. Then and only then you can look for the correct solution. Maybe the data access layer is not the problem. 20,000 records is a small dataset today, not a large one.
无论如何，在寻找解决方案之前，您必须确定问题所在。概要说明您的应用程序，包括客户端和服务器端(SQL server有很好的工具)，并找出究竟是什么使其速度变慢。只有这样，你才能找到正确的解决方案。也许数据访问层不是问题所在。现在20000条记录是一个小数据集，而不是一个大数据集。
#7
1
You cannot use the native clients with ADO, as is.
您不能像往常一样使用ADO的本机客户端。
ADO does not understand the XML SQL Server data type. The field type:
ADO不理解XML SQL Server数据类型。字段类型:
field: ADOField;
field := recordset.Fields.Items["SomeXmlColumn"];
Attempting to access field.Value throws an EOleException:
试图访问字段。抛出一个EOleException价值:
- Source: Microsoft Cursor Engine
- 来源:微软光标引擎
- ErrorCode: 0x80040E21 (E_ITF_0E21)
- 错误代码:0 x80040e21(E_ITF_0E21)
- Message: Multiple-step operation generated errors. Check each status value
- 消息:多步操作产生错误。检查每一个状态值
The native client drivers (e.g. SQLNCLI, SQLNCLI10, SQLNCLI11) present an Xml data type to ADO as
本机客户端驱动程序(例如SQLNCLI、SQLNCLI10、SQLNCLI11)将Xml数据类型呈现给ADO as
field.Type_ = 141
While the legacy SQLOLEDB driver presents an Xml data type to ADO as adLongVarWChar, a unicode string:
当遗留的SQLOLEDB驱动程序向ADO提供Xml数据类型adLongVarWChar时，unicode字符串:
field.Type_ = 203 //adLongVarWChar
And the VARIANT contained in field.Value is a WideString (technically known as a BSTR):
以及场中包含的变量。值是一个宽字符串(技术上称为BSTR):
TVarData(field.Value).vtype = 8 //VT_BSTR
The solution, as noted by Microsoft:
解决方案，如微软所言:
Using ADO with SQL Server Native Client
Existing ADO applications can access and update XML, UDT, and large value text and binary field values using the SQLOLEDB provider. The new larger varchar(max), nvarchar(max), and varbinary(max) data types are returned as the ADO types adLongVarChar, adLongVarWChar and adLongVarBinary respectively. XML columns are returned as adLongVarChar, and UDT columns are returned as adVarBinary. However, if you use the SQL Server Native Client OLE DB provider (SQLNCLI11) instead of SQLOLEDB, you need to make sure to set the DataTypeCompatibility keyword to "80" so that the new data types will map correctly to the ADO data types.
现有的ADO应用程序可以使用SQLOLEDB提供程序访问和更新XML、UDT、大值文本和二进制字段值。新的更大的varchar(max)、nvarchar(max)和varbinary(max)数据类型分别作为ADO类型adLongVarChar、adLongVarWChar和adLongVarBinary返回。XML列作为adLongVarChar返回，UDT列作为adVarBinary返回。但是，如果您使用的是SQL Server本地客户端OLEDB提供者(SQLNCLI11)而不是SQLOLEDB，您需要确保将datatypecompatiable关键字设置为“80”，以便新的数据类型能够正确地映射到ADO数据类型。
They also note:
他们还指出:
If you do not need to use any of the new features introduced in SQL Server 2005, there is no need to use the SQL Server Native Client OLE DB provider; you can continue using your current data access provider, which is typically SQLOLEDB.
如果您不需要使用SQL Server 2005中引入的任何新特性，那么就不需要使用SQL Server本地客户机OLE DB提供程序;您可以继续使用当前的数据访问提供程序(通常是SQLOLEDB)。
#8
0
Also, besides the lack of support for the XML data type, Delphi ADO does not recognize columns defined in SQL Server as TIME (DBTYPE_DBTIME2=145) or DATETIMEOFFSET (DBTYPE_DBTIMESTAMPOFFSET=146); trying to use those fields in your application will cause multiple errors like 'Invalid Variant Value' or some controls (like TDBGrid) will simply drop the field entirely.
此外，除了缺乏对XML数据类型的支持之外，Delphi ADO不承认SQL Server中定义的列是TIME (DBTYPE_DBTIME2=145)或DATETIMEOFFSET (DBTYPE_DBTIMESTAMPOFFSET=146);尝试在应用程序中使用这些字段会导致多个错误，比如“无效的变量值”或一些控件(如TDBGrid)将完全删除该字段。
Seems like the lack of support for DBTYPE_DBTIME2=145 is a bug/QC-issue since there is already ftTime support (it's also not clear to me why SQL Server doesn't return DBTYPE_DBTIME which Delphi does support), the XML and Offset types have no clear TFieldType mapping.
似乎缺少对DBTYPE_DBTIME2=145的支持是一个bug/ qc问题，因为已经有ftTime支持(我也不清楚为什么SQL Server不返回DBTYPE_DBTIME，而Delphi支持DBTYPE_DBTIME)， XML和偏移类型没有清晰的TFieldType映射。
Data Type Support for OLE DB Date/Time Improvements
数据类型支持OLE DB日期/时间的改进