Seven Deadly Sins of ADO

One of ADO's best assets as a great database interface is its flexibility. However, ADO's flexibility gives you more ways to shoot yourself in the foot while you're writing applications. Here's a list of the seven most common errors that I see in ADO coding. (Remember, these are the things that you shouldn't do.) For more examples of using ADO, check out http://www .microsoft.com/data/techmat.htm.

Sin 7: Ignoring the native OLE DB Provider. You can use ADO with both the native OLE DB providers and with the OLE DB Provider for ODBC, but this choice makes continuing to use the old ODBC Data Source Names (DSNs) and drivers a bit too easy. The native OLE DB Provider for SQL Server is more efficient than these old options and provides access to features such as the SQL Server IRowsetFast interface and LinkedServer rowset, which will never be available from ODBC.

Sin 6: Using Recordsets instead of stored procedures. Although ADO lets you easily perform queries and updates by using Recordsets, SQL Server stored procedures provide a big performance advantage because stored procedures cache execution plans. Stored procedures also can reduce network traffic by executing a batch of SQL statements in one invocation.

Sin 5: Letting ADO determine stored procedure parameters. ADO can dynamically discover the parameters of a stored procedure by using the Refresh method of the Parameters collection. This capability can easily tempt you into not explicitly creating a Parameters object in your code. This feature is useful during development, but avoid it in your production code because it will cause unnecessary trips to the server.

Sin 4: Using Recordset objects for all updates. Although using a Recordset object to update the database is easy and convenient, this method requires an open cursor and therefore has relatively high resource requirements. Updating the database by using direct SQL statements—or, better yet, stored procedures—is more efficient, especially for multiple insert, update, and delete operations.

Sin 3: Using independent connections for the Command and Recordset objects. Another easily misused feature of ADO is the ability of the Recordset and Command objects to create their own connect objects. With typical network applications, repeatedly connecting and disconnecting is much less efficient than reusing an existing Connection object. The primary exception to this rule is during Web development, when you often want to isolate the connections for each Web page.

Sin 2: Using resource-intensive cursors when you don't need them. ADO supports four types of cursors: forward-only, static, keyset, and dynamic. Of these, the forward- only cursor (aka the firehose cursor) has the least overhead and best performance. You'll often be tempted to use more resource-intensive cursors, such as the keyset or dynamic, to get basic scrolling capability, but you don't need to.

Sin 1: Using the default ADO Recordset cache size. ADO's Recordset object Cache-Size property controls how many rows SQL Server retrieves when ADO issues an sp_cursorfetch against a server-side cursor. Using the CacheSize property default value of 1 can be inefficient and might result in many round trips to the server.