In Focus: Guidelines for Working with ADO

ActiveX Data Object (ADO) is Microsoft's latest data access object model. ADO 2.0 and Microsoft Data Access Component (MDAC) 2.0 are part of Microsoft's Universal Data Access (UDA) strategy.

Before you start working with ADO, we recommend you scope out http://www.microsoft.com/data. This site contains information about ADO, Object Linking and Embedding Database (OLE DB), OLE DB for online analytical processing (OLAP) Services, Open Database Connectivity (ODBC), Remote Data Service (RDS, formerly Advanced Data Connector), and UDA. Other resources include Jim Lewallen's "ADO Performance and Architecture" presentation from Microsoft's Professional Developers Conference (PDC) in 1998, and David Sussman and Alex Homer's ADO 2.0 Programmer's Reference (WROX).

Here are a few tips for working with ADO:

• Don't be lazy. For example, avoid SELECT * FROM tablename queries, ask for only the columns you need, and use a WHERE clause to limit the number of records SQL Server returns. Describe command parameters yourself, because getting command parameter information is often as expensive as executing the command.

• Use cursor-based updating only if you need it. Although using a SQL statement to update data isn't always feasible, do so when possible. Updating data through the RECORDSET object is easier than using SQL, but this process is more expensive.

• 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.

• When you know that query results will yield only one row of data, use a stored procedure with output parameters rather than opening a RECORDSET to fetch that data. A RECORDSET's query results include data and metadata, and the metadata is bigger and takes longer to retrieve than the data. Using a stored procedure is more efficient.

• If you must use a cursor, use the COLLECT methods for singleton SELECT commands. The RECORDSET::GET_COLLECT and RECORDSET::PUT_COLLECT methods are RECORDSET object shortcuts that let you quickly receive and set a field value without first obtaining a field reference. Use the COLLECT methods when you don't need to obtain or set properties other than FIELD::VALUE.

• If you know a query won't return any rows, use adExecuteNoRecords, ADO's new ExecuteOptionEnum option. This option causes ADO to avoid the overhead of both creating a RECORDSET and setting cursor properties.

• Use CONNECTION::EXECUTE instead of RECORSDET::OPEN or COMMAND::EXECUTE for single execution of one-time commands, which is a fairly common scenario in Internet Information Server (IIS), Active Server Pages (ASP), and Microsoft Transaction Server (MTS) environments. In these environments, the code typically opens a connection, executes a row or non-row returning command, processes results, and closes the connection.

• Use native OLE DB providers (drivers) whenever possible, but be selective about which drivers you use. SQL Server 7.0 ships with native providers for SQL Server and Oracle8. However, as Intersolv (vendors of DataDirect OLE DB drivers) explains, "The DataDirect Oracle8 OLE DB provider supports all the new Oracle8 data types and features rather than just access to Oracle8 servers. The Microsoft provider can connect to Oracle8 servers but doesn't support any of the new data types or features. For example, DataDirect allows access to binary large objects (BLOBs) and character large objects (CLOBs), but the Microsoft provider doesn't offer these features. Also, DataDirect supports other import features such as scrollable cursors and updatable rowsets, which the Microsoft provider doesn't support."