ADO is by far the most commonly used data-access middleware for retrieving data and updating SQL Server databases. However, ADO's default settings aren't always the best options for optimal performance. Here are seven ways you can boost ADO performance with SQL Server.
7. Use the Native SQL Server OLE DB Provider
By default, an ADO Recordset uses the OLE DB provider for ODBC (MSDASQL) to connect to SQL Server. However, the native OLE DB provider for SQL Server (SQLOLEDB) offers a shorter code path, which results in better performance.
6. Reuse an Existing Connection Object
If you're developing a stateful application, reusing an existing ADO Connection object in a Command or Recordset object's ActiveConnection property lets you avoid the overhead incurred when ADO implicitly creates and opens a Connection object for you.
5. Explicitly Define Parameters
ADO's ability to dynamically determine the properties of the parameters that a Command object uses can be a time-saver during development, but this feature typically adds unnecessary round-trips to the server in a production application. Explicitly defining a parameter's Type, Direction, and Size reduces the number of round-trips your application makes to the server.
4. Tune the CacheSize
The ADO CacheSize affects server-side Keyset, Static, and Dynamic Recordsets. The default CacheSize of 1 works well for updates and combinations of operations, but if your application needs to retrieve large resultsets, try increasing this value. Reducing the number of round-trips to the server is one of the most important keys to ADO and SQL Server application performance.
3. Use Command Objects Instead of Cursors
Sometimes using a cursor for updates is almost too easy to avoid. However, updateable cursors carry overhead, and you get better performance if you use Command objects that contain T-SQL INSERT, UPDATE, and DELETE statements to send updates to SQL Server.
2. Use Fast Forward Only Recordsets
The best ADO data-retrieval performance comes from using Fast Forward Only Recordsets. ADO creates a Fast Forward Only Recordset, sometimes called a fire hose cursor, when you specify a Recordset as Forward Only, Read Only, with a CacheSize of 1. The Fast Forward Recordset lets SQL Server quickly stream large amounts of data to the client with low overhead.
1. Use Smart SQL
Using good SQL is the best way to get better performance from ADO and all SQL-based data-access technologies. When you build SQL statements, include only the rows and columns that you really need. Take advantage of SQL's set-based processing to let the server efficiently handle all data-retrieval requirements.
