SQLSERVER 

DATABASE ACCESS BEST PRACTICES

 
Introduction ADO Programming    Design/Implementation    Database Design
www.sql-server-performance.com www.sqlmag.com      
 
ADO Programming
  • 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.
  • Avoid using Serverside cursors where possible.

    IE Avoid Cursorlocation = adUseServer

    If you want scrolling, don't default to server cursors. Microsoft set the ADO CursorLocation default to adUseServer primarily for backward compatibility. However, for most scrolling scenarios, you are better off using a client cursor.
  • If Serverside cursors are required then 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 can be grossly inefficient when retrieving multiple rows. A CacheSize of 1 causes the stored procedure sp_cursorfetch to be submitted to SQLServer for each row in the resultset. This processing significantly increases the number of roundtrips from the client to the SQLServer and adversely affects the application’s and SQLServer’s performance.

    Reducing the number of round-trips to the server is one of the most important keys to ADO and SQL Server application performance.

    A CacheSize value of 100 to 500 should be considered – the value set will depend on the total number of rows that will be returned.
  • Use Command Objects Instead of Cursors methods for Updates.

    IE. Don’t use the update or delele methods of a Recordset object.

    Note: updateable cursors carry overhead, and you get better performance if you use Command objects that contain T-SQL INSERT, UPDATE, and DELETE statements or STORED PROCEDURE calls to send updates to SQL Server.
    1. Use the adCmdStoredProc property if creating a command object to execute a stored procedure.
    2. Use the adExecuteNoRecords  property if no resultsets will be returned by the stored procedure.
       
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.

 

July 2001