What are the pros and cons of OleDB versus SQLClient?

Question

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.

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.)

Thanks, all.

Answer 1

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.

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 have to be in order).

In my personal experience, I've never noticed the speed difference, so like you I couldn't find anything to back that up.

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

Answer 2

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.

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.

Answer 3

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.

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.

Answer 4

well, the hell with sqlclient I've tried with one app.,

it's slower than OleDb, at least in my case!

Answer 5

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.

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.

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.

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.