.net OleDB 与 SQLClient 的优缺点是什么？

What are the pros and cons of OleDB versus SQLClient?

提问by Electrons_Ahoy

Some context: one of the systems I'm working on is a .net 2.0 web application. VB.net for the front end, and SQL Server 2005 for the backend. For a variety of reasons that have been lost to time, the original designer decided to use the .Net OleDB connection rather than the SQLClient connection.

一些上下文：我正在开发的系统之一是 .net 2.0 Web 应用程序。前端使用VB.net，后端使用SQL Server 2005。由于各种已被时间遗忘的原因，最初的设计者决定使用 .Net OleDB 连接而不是 SQLClient 连接。

After a few years of development, this particular system is on the cusp of crossing the line from "beta" into "1.0" status. One of the things we've been talking about at this point is moving to the SQLClient connection. While I'm aware that it's best practice to be using it, and that it's the only way to get at the fancier features in SQL Server 2005 (which we aren't using, obviously) what are the advantages of using the one over the other? Any hidden gotchas I should know about? And can anyone point me at some benchmarks showing relative speeds? (I hear that the SQLClient is supposed to be faster, but I've never seen any numbers to back that up.)

经过几年的发展，这个特殊的系统正处于从“beta”进入“1.0”状态的边缘。在这一点上我们一直在谈论的事情之一是转移到 SQLClient 连接。虽然我知道使用它是最佳实践，并且它是获得 SQL Server 2005 中更高级功能的唯一方法（显然我们没有使用），使用它的优势是什么？其他？我应该知道的任何隐藏问题？谁能指出一些显示相对速度的基准？（我听说 SQLClient 应该更快，但我从未见过任何数字来支持这一点。）

Thanks, all.

谢谢，所有。

回答by Joel Coehoorn

OleDb is more generic. If you ever move to a different database type in the future there's a good chance it'll have an Ole driver and you won't have to change as much code.

OleDb 更通用。如果您将来迁移到不同的数据库类型，那么它很有可能拥有一个 Ole 驱动程序，并且您不必更改那么多代码。

On the other hand, the Sql Server native driver is supposed to be faster as you said, and it has nicer parameter support (parameters can use names and don't haveto be in order).

在另一方面，SQL Server本机驱动程序应该是如你所说更快，具有更好的参数支持（参数可以使用名称，不具有符合规定）。

In my personal experience, I've never noticed the speed difference; I also couldn't find anything to back up the claim. I suspect the performance advantage is real, but that you'd have to process millions of records before you could start to measure it.

根据我的个人经验，我从未注意到速度差异；我也找不到任何东西来支持索赔。我怀疑性能优势是真实存在的，但您必须先处理数百万条记录，然后才能开始衡量它。

What I did notice made a meaningful difference were the error messages. I was having trouble with an old OleDb app, and I switched it to SqlClient out of desperation. Of course, it still didn't work, but the better error messages provided enough new information I was able to fix the problem.

我注意到的一个有意义的区别是错误消息。我在使用旧的 OleDb 应用程序时遇到问题，出于绝望，我将其切换到 SqlClient。当然，它仍然不起作用，但更好的错误消息提供了足够的新信息，我能够解决问题。

回答by Richard

OLEDB is much faster than the SQLClient, EXCEPT when it is access through ADO.NET. The drivers for OLEDB are written in native unmanaged code however, when you access these drivers through ADO.NET, you have to go through several layers (including an abstraction layer and a COM interop layer). The abstraction layer takes care of resource management such as managing memory handles to ensuring that garbage collection occurs correctly, changing data types and parameters to .NET types and converting the oledb buffer to row and column bindings. The COM interop layer takes care of marshalling passing messages from .NET to COM and vice versa including locking/unlocking/converting pointers.

OLEDB 比 SQLClient 快得多，但通过 ADO.NET 访问时除外。OLEDB 的驱动程序是用原生非托管代码编写的，但是，当您通过 ADO.NET 访问这些驱动程序时，您必须经过多个层（包括抽象层和 COM 互操作层）。抽象层负责资源管理，例如管理内存句柄以确保垃圾回收正确发生、将数据类型和参数更改为 .NET 类型以及将 oledb 缓冲区转换为行和列绑定。COM 互操作层负责编组从 .NET 到 COM 的传递消息，反之亦然，包括锁定/解锁/转换指针。

Don't listen to anyone that makes false accusations about OleDB's performance without understanding how they tested it and what environment they used (managed code vs managed code). The only thing that slows OleDB down is the amount of plumbing that is required to get the native code to play nice with the managed code. Also keep in mind that the SqlClient .NET library has its own plumbing and IS NOT A NATIVE .NET library like most people think it is. The SqlClient libraries in .NET use the the SNINativeMethodWrapper and SNIPacket classes which are wrappers that marshal data between unmanaged code (sqlncli.dll) and managed .NET code. This is the undocumented truth and the reason why the .NET SqlClient will never be able to out perform the OleDB when you use OleDB in native unmanaged code.

不要听任何人在不了解他们如何测试它以及他们使用什么环境（托管代码与托管代码）的情况下对 OleDB 的性能做出错误指责。唯一会减慢 OleDB 速度的是使本机代码与托管代码完美配合所需的管道数量。还要记住，SqlClient .NET 库有自己的管道，不像大多数人认为的那样是一个 NATIVE .NET 库。.NET 中的 SqlClient 库使用 SNINativeMethodWrapper 和 SNIPacket 类，它们是在非托管代码 (sqlncli.dll) 和托管 .NET 代码之间封送数据的包装器。这是未公开的事实，也是当您在本机非托管代码中使用 OleDB 时 .NET SqlClient 永远无法执行 OleDB 的原因。

In summary, if you are using 100% managed code, you will get better performance from the System.data.SqlClient. If you have a mixed environment, you will get far better performance talking to OleDB directly or to either sqlncli.dll (SQL2005) or sqlncli10.dll (SQL 2008). Keep in mind that both OleDB and ODBC are being updated by Microsoft and the latest OleDB drivers DO talk to the latest unmanaged native SQL client libraries. Microsoft recommend using OleDB in unmanaged applications when high performance is required.

总之，如果您使用 100% 托管代码，您将从 System.data.SqlClient 获得更好的性能。如果您有一个混合环境，则直接与 OleDB 对话或与 sqlncli.dll (SQL2005) 或 sqlncli10.dll (SQL 2008) 对话将获得更好的性能。请记住，Microsoft 正在更新 OleDB 和 ODBC，并且最新的 OleDB 驱动程序确实会与最新的非托管本机 SQL 客户端库通信。当需要高性能时，Microsoft 建议在非托管应用程序中使用 OleDB。

See "SQL Server 2008 Books Online\Database Engine\Development\Developer's Guide\SQL Server 2008 Native Client Programming\SQL Server 2008 Native Client (OLE DB)" for more information.

有关详细信息，请参阅“SQL Server 2008 联机丛书\数据库引擎\开发\开发人员指南\SQL Server 2008 Native Client Programming\SQL Server 2008 Native Client (OLE DB)”。

回答by bernd_k

Here is some PowerShellCode to do a direct compare:

这是一些用于直接比较的PowerShell代码：

Ole-DB:

Ole-DB：

$ConnectionString      = "server=localhost;database=MyDatabase;trusted_connection=yes;Provider=SQLNCLI10;"
$sql = "SELECT * FROM BigTable"

$conn = New-Object System.Data.OleDb.OleDbConnection($ConnectionString)
$conn.open()
$cmd = New-Object system.Data.OleDb.OleDbCommand($sql,$conn)
#$cmd.CommandTimeout = $timeout
$da = New-Object system.Data.OleDb.OleDbDataAdapter($cmd)
$dt = New-Object system.Data.datatable
[GC]::Collect()
$start = get-date
[void]$da.fill($dt)
$now = get-date
[int]($now - $start).Milliseconds
$conn.close()
#$dt

SQLClient:

SQL客户端：

$ConnectionString      = "Data Source=localhost;Initial Catalog=MyDatabase;Integrated Security=True"
$sql = "SELECT  * FROM BigTable"


$conn=new-object System.Data.SQLClient.SQLConnection($ConnectionString) 
$conn.Open() 
$cmd=new-object System.Data.SQLClient.SQLCommand($sql,$conn)
#   $cmd.CommandTimeout=$timeout
$dt = New-Object system.Data.datatable
$da=New-Object System.Data.SQLClient.SQLDataAdapter($cmd)
[GC]::Collect()
$start = get-date
[void]$da.fill($dt)
$now = get-date
[int]($now - $start).Milliseconds
$conn.close()
#$dt

I got

我有

Ole-DB : SQL-Client
538 - 839
767 - 456
592 - 678

And as a result, for ad-hoc queries of this type I prefer Ole-DB, as I have only to adjust the connection string to pull data from an Oracle database.

因此，对于这种类型的即席查询，我更喜欢 Ole-DB，因为我只需调整连接字符串即可从 Oracle 数据库中提取数据。

回答by Mitchel Sellers

I am with Joel on this one, SqlClient is the best to use if you are planning on sticking with SQL Server. There are performance gains, but you have to start working with large sets, and high numbers of transactions to typically start seeing the benefits of this.

我和 Joel 一起讨论这个问题，如果您打算坚持使用 SQL Server，那么最好使用 SqlClient。有性能提升，但您必须开始使用大型集和大量事务，通常才能开始看到这样做的好处。

Overall, the errors and functionality provided is much more tailored to what SQL Server can do, thus a "better" implementation if you will. It also has support for MARS which for some makes it the "must do" switch.

总的来说，所提供的错误和功能更适合 SQL Server 可以做什么，因此如果您愿意，这是一个“更好”的实现。它还支持 MARS，这对某些人来说使其成为“必须做”的开关。

回答by Rad

You could always write a sample application with some typical operations using SqlClient and OleDB and benchmark them to compare performance. I doubt the difference would be significant but there's only one way to find out.

您始终可以使用 SqlClient 和 OleDB 编写带有一些典型操作的示例应用程序，并对它们进行基准测试以比较性能。我怀疑差异会很大，但只有一种方法可以找出答案。

I don't think you' have any problems using OleDb unless you're using exotic data types like XML in which case you might need to work a little harder.

我认为使用 OleDb 不会有任何问题，除非您使用的是 XML 之类的奇特数据类型，在这种情况下，您可能需要更加努力地工作。