Performance Tuning SQL Server OLE DB

By : Brad McGehee Jan 17, 2007

Printer friendly

To upgrade the SQL Server OLE DB drivers, you will need to install what Microsoft calls MDAC (Microsoft Data Access Components). The latest MDAC can be downloaded from Microsoft at http://msdn.microsoft.com/data. [6.5, 7.0, 2000, 2005] Updated 10-4-2005

*****

When making your connection to SQL Server, choose DSN-less connections for the fastest connection. DSN-less connections use the SQL Server OLE DB driver to make the connection. [7.0, 2000, 2005] Updated 10-4-2005

*****

If you have a choice between using ODBC or OLE DB to access your SQL Server database, choose OLE DB, as it is generally faster. [7.0, 2000, 2005] Updated 10-4-2005

*****

In both SQL Server, when you INSERT large amounts of BLOB data into a database using a stored procedure, and when using the Microsoft OLE DB provider for SQL Server, performance can be very slow. In fact, it is 10 times slower than performing the same task when using the Microsoft OLE DB provider for ODBC driver.

So, if you have a need to insert large amounts of BLOB data into a database that uses the Microsoft OLE DB provider for SQL Server, do so by not using a stored procedure, or if you want to use a stored procedure, use the Microsoft OLE DB provider for ODBC driver instead. [7.0, 2000] Updated 10-4-2005

Your Name
Email	(Emails will not be displayed on the site or used for promotional purposes)
Comment
Type characters in the image	(case sensitive)