Too often, an application that utilizes ActiveX Data Objects (ADO) suffers from poor performance. The problem can usually be blamed on about a half dozen incorrect choices made during coding. You can adopt some simple techniques to optimize ADO performance.
The connection object
The often ignored connection object represents a unique physical connection to a data source. The recordset and command objects that connect to a physical data source use a connection object. While it seems simple enough, there is a potential problem with this arrangement. Connection objects can be created either implicitly or explicitly. Performance issues can arise when a connection object is implicitly created. Each time a recordset object or command object is opened, a new connection is also opened. This implicit creation of connection objects increases the run time of programs and scripts by about three times. If there are multiple recordsets or command objects, even the simplest applications may seem like they’re running on an old Z80 machine.
One way to correct this problem is to use a technique known as connection pooling. While it may sound like a hassle, connection pooling is no big deal to implement. Connection pooling basically involves explicitly defining an ADO connection, opening it, and coding the recordset and connection objects to use the existing connection as the active connection. When you’re finished with an object, just close it and set the active connection to null. Setting the active connection to null prevents errors when the connection is closed while it is still being used as an active connection.
It is important to note that there is a difference between a connection string and a connection object. A connection string identifies the data source’s provider, driver, server, user ID, and password, while a connection object connects to a physical data source.
Data providers are another possible drag on performance. A data provider is used to connect to a database, execute commands, and retrieve results. Microsoft supplies several OLE DB providers with ADO for databases ranging from Access to SQL Server and Oracle. If a data provider is not specified, the default is the ODBC OLE DB provider. The problem with the default is that it's not optimized for a particular database, so it won’t be as fast as the specific data provider for your database.
Another often overlooked way to improve performance is to use the connection object’s execute method. The execute method provides a way to execute stored procedures, and, if necessary, return a recordset. Stored procedures are precompiled SQL that can contain logic and multiple database queries. They can accept parameters and return single or multiple result sets. Because stored procedures are precompiled, they offer a significant advantage over in-line SQL, which must be compiled at execution time.
The recordset object
The ADO recordset object is used in applications to retrieve and manipulate data on the record level. After a successful query has been executed, the recordset object contains the columns and rows that match the selection criteria that was provided in a SQL command or a stored procedure. While this sounds simple enough, remember what the old maps say: “Here be dragons!” Even something as basic as cursor type can have a big impact on performance.
The recordset object’s cursor type property establishes how a recordset is going to be navigated and determines whether changes by other users should be visible. There are for cursor types:
- Dynamic cursor
- Keyset cursor
- Static cursor
- Forward-only cursor
Dynamic cursors are the most flexible but also the slowest of the four types. Using dynamic cursors allows navigation of recordsets both forward and backward. In addition, dynamic cursors let you see additions, deletions, and updates made by other users and enable you to add, update, and delete records. Keyset cursors work the same way as dynamic cursors, with the exception of seeing additions made by other users.
Static cursors provide a static snapshot of the records in a recordset when the query was run, which is useful in generating reports. Unlike dynamic cursors and keyset cursors, static cursors do not allow updates.
As the name implies, forward-only cursors allow only forward movement in a recordset. Like static cursors, updates are not permitted. While these limitations may seem somewhat restrictive, forward-only cursors are the fastest cursor type.
As strange as it sounds, laziness can also create a drag on performance. Allow me to elaborate before you say, “Huh?” Coding a SQL statement that contains “SELECT *” is a waste of resources unless every column in a table is needed. While it takes more development time to list the columns individually, it pays off in performance.
In addition to speeding things up a little, listing the columns individually allows you to control the order of the columns in the recordset object. Knowing the ordinal number of the each column in a recordset provides a way to access the individual columns with less overhead than the traditional recordset (“column name”). Coding recordset.collection (ordinal number) consumes fewer resources, since the overhead of associating a column name with column isn’t necessary.
The techniques mentioned here can be used with both new and existing applications. With some careful thought and planning, ActiveX Data Objects don’t have to be the cause of poor application performance.
Do you have some ADO optimization tricks to share? Post a comment below or send us an e-mail.