Performance Tuning SQL Server OLE DB
The SQL Server OLE DB drivers are automatically installed when SQL Server is installed. These drivers are periodically updated, often with little fanfare. Like SQL Server service pack updates, you will generally want to update to the latest drivers as they often include performance improvements.
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