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




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |