Basic Principles: Getting the Most from the Data Access Object Layer

Basic Principles: Getting the Most from the Data Access Object Layer

Robert Eineigl

May 9, 1995

In the last Basic Principles column ("Tips and Tradeoffs in Data Access Performance," March 10, 1995), I discussed three of the four major design choices a data-access client application programmer must make when using Visual Basic:

• Which back-end relational database to use

• How to choose the data-access method

• Design of the back-end database system

Now I'll cover a top-ten list of performance enhancements when using the Visual Basic and Microsoft Access data-access object (DAO) layer.

Before considering the list, it's important to note that the latest version of the Microsoft Access engine (2.0) is mandatory for best performance, because the Jet 2.0 engine (hereafter referred to as Jet) provides the Visual Basic DAO.

This article focuses on maximizing DAO performance when accessing remote databases, such as SQL Server, as opposed to ISAM (indexed sequential access method) databases, such as Microsoft Access, dBASE, Paradox, FoxPro, and Btrieve.

Because Jet relies on the open database connectivity (ODBC) protocol to access remote databases, using the DAO inefficiently can limit its ODBC performance capability and cause dramatic performance hits. As with any data access method, there is no substitute for efficient use of the DAO.

#1. To attach or not to attach

The greatest single performance enhancement you can make to a Visual Basic ODBC application is to attach the tables in an ODBC database to a local Microsoft Access database so that Jet stores field, index, and connection information for the remote tables. Now when you access the tables, the engine doesn't submit repeated redundant queries to the system catalog procedures on the server to verify this information.

If the structure of the attached table changes, you will need to reattach the table. This can be done on the fly, in code, in response to a trappable error. If you use attached tables, the Jet engine automatically connects to the server when you open the local Microsoft Access database. You can then create snapshots or dynasets on the attached tables.

#2. Which DAO object?

The DAO layer in Visual Basic provides great coding leverage, if used efficiently. To access remote databases through ODBC, two kinds of recordsets are provided: dynasets, which can be updated, and snapshots, which cannot.

If you don't need to update data and the recordset contains fewer than 500 records, a snapshot can reduce the time to open the recordset.

Because the data contained in each row is fetched for a snapshot recordset, you can further minimize the performance hit by ensuring that you return only the columns you need.

If, however, the recordset is large or contains fields of the DB_LONGBINARY or DB_MEMO type, using a dynaset is more efficient, because fetching keys (and data only as needed) requires fewer local resources than actually fetching all the data. When you open a dynaset, Jet retrieves only the primary key of each record, whereas a snapshot retrieves the entire record.

Although the CreateSnapshot statement by itself returns faster than the CreateDynaset statement, fully establishing set membership with a large result set, by executing a MoveLast on both objects, will take longer on the snapshot object than on the dynaset object. Instead of transferring the data in each column of a row during the fetch, Jet builds a dynaset by fetching just the key values that uniquely identify each row. This is why an ODBC table must have a unique index to be updatable. Using key values speeds the chunking operation and minimizes network traffic, especially when browsing large tables.

If you need only to append data to an ODBC table, open the dynaset with the DB_APPENDONLY flag. This will avoid populating the dynaset with the keys/bookmarks of all the records in the table. If you're trying to append a lot of data to an ODBC table already stored in an .MDB table, wrap the inserts with transactions.

#3. Use transactions

A transaction is a set of operations considered as a single unit. A transaction is saved (committed) only if all the operations succeed. If any operation fails, all successful operations are rolled back (canceled), and the data is returned to its previous state.

Transactions are a powerful way to improve performance as well as multiuser concurrency. Using transactions allows Jet to buffer operations and then commit the changes in a single batch. This saves disk I/O and/or network packets.

On most servers, transactions generate locks that prevent other users from updating or even reading data affected by the transaction until it's committed or rolled back, so keep transactions as short as possible. Avoid beginning a transaction and then waiting for the user's response before completing it.

When using "bulk operation" SQL statements, such as UPDATE, INSERT, and APPEND, Jet places a transaction around the operation. This is necessary because one Jet SQL statement may actually correspond to many server statements.

If the server supports transactions at all, Jet assumes only single-level support, that is, no nesting of transactions. Therefore, if your Basic code nests transactions, only the outermost BeginTrans, Commit, and Rollback are actually sent to the server; nested transaction methods are ignored.

Jet often conserves connections by sharing them between queries. If you use server-specific transaction commands in passthrough queries, they can confuse the internal tracking of server transactions performed by Jet, as well as give you unexpected results. Instead, use the native BeginTrans method. Jet translates these transaction methods into the equivalent server commands.

#4. To passthrough or not passthrough

The DB_SQLPASSTHROUGH flag is an option when connecting directly to ODBC databases. Using this flag with the CreateSnapshot method tells Jet to ignore parsing the SQL statements and pass them through to the ODBC driver and, ultimately, to the database for parsing and execution. Because the result set from a query passed through to the back end is not updatable, the set is fetched into a general buffer and accessed with local pointers.

Use the ExecuteSQL method when executing bulk operations for a greater performance gain for action queries such as updates. This passthrough method allows the use of the thinnest possible DAO layer and, if used for operations that the Jet optimizer may handle inefficiently, offers an alternative that depends strictly on the server's optimizer. To create a passthrough query, you must know the SQL syntax supported by your server. Reusing a single snapshot object, with the passthrough flag set, allows a continuous session of native back-end syntax, including native transaction syntax, to be sent to the back end, bypassing the DAO while retaining its ease of fetching result sets.

#5. Set versus row processing logic

Processing rows in a While or For loop, even if wrapped in a transaction, is usually slower than letting Jet or the ODBC server use the set processor in its SQL engine. For example, updating one row at a time within a code loop is a common practice in programming to ISAM databases. With the appropriate conditions specified in the WHERE clause, a single SQL update statement could replace the loop and enhance performance.

#6. Use the Refresh method sparingly

The Refresh method is generally an expensive operation relative to most DAO operations. Basically, it involves recreating all the collections in the DAO object. DAO has many collections on its various objects, and a DAO collection can get out of date if someone else makes schema changes to the database. The Refresh method of each collection forces DAO to fill the entire collection again with the latest information in the database, and is generally only useful for rarely run utility programs that modify or examine database structures.

#7. Use parameterized queries

You can use QueryDef objects stored in the local Microsoft Access .MDB file to perform SQL queries on attached ODBC tables, or you can create QueryDefs that accept parameters analogous to stored procedures on SQL Server. A common requirement of applications is to build an SQL string based on values that the user provides. While this can be done dynamically, by building on the fly an SQL statement combined with input variables, it isn't efficient.

The alternative is to create parameterized queries that have variables in place of the actual values in the SQL statement. A saved and compiled parameterized query can reduce the steps Jet needs to perform queries to four:

1. The parameterized query is sent to the ODBC server.

2. The server parses and compiles the SQL string.

3. Jet passes the values for the parameters.

4. The ODBC server retrieves the results and returns them to Jet. Successive queries with different parameter values further leverage the server's compilation.

#8. Use the SQL WHERE clause instead of Find methods or the Filter property

Using the SQL WHERE clause instead of the native Find method can be much faster, especially against ODBC databases. The SQL query can then be optimized by the ODBC server and return just the row(s) requested, rather than having Jet search through its entire membership of keys looking for rows that match the criteria.

Similarly, the appropriate SQL WHERE clause can replace the Filter property. Also, replacing a Sort property with an SQL ORDER BY clause in a query allows the server to do the work and return the data in the order requested. Running this new query will be faster than recreating a new recordset. As a general rule, don't use the Sort property on any set of rows that has more than 100 records.

#9. Use Bookmarks versus the Find method

Bookmarks correspond directly to the unique keys for a table. The fastest way to return to a record is to store its bookmark. The memory overhead in storing the string for the bookmark is far less than the execution time involved in using the Find method.

#10. Use SQL joins versus multiple DAO objects

Rather than concatenate values from multiple dynasets created from individually attached tables in code, it is far more efficient to use Jet's capability as an SQL engine to join the attached tables in a single additional dynaset. This may seem obvious, but the temptation to use multiple data controls for simplicity of data entry sometimes leads programmers to use code to do all the work to browse the results, instead of simply employing an SQL join.

Robert Eineigl works in Microsoft's Information Technology Group. His nocturnal interests include bicycle design, keyboard improvisation, and wildlife serenades with his custom-made trumpet.

More Reading

The Developer Network Development Library has numerous articles on database programming, including:

• "Jet Database Engine ODBC Connectivity" by Neil Black and Stephen Hecht. This describes in great detail how Jet uses ODBC to retrieve server data. This is required reading for anyone using DAO to write significant server applications.

• "ODBC: Architecture, Performance, and Tuning" by Colleen Lambert. This paper provides a good overview of how ODBC works, and addresses performance issues in a realistic and useful fashion.

Some of these are also available directly from Microsoft, usually from one of its download services, such as the Internet server at ftp.microsoft.com.