DAO: General Performance Tips

Home |  Overview |  How Do I |  FAQ |  Sample |  Tutorial |  ODBC Driver List

This article offers tips for improving the performance of your MFC DAO applications. Use these tips as your starting point, and benchmark your changes. Keep in mind that these tips will often help, but there are no absolutes. Weigh everything in the context of your database and your application. Topics covered include:

• Best tip
  
  
• Recordset types
  
  
• Selecting records
  
  
• ODBC
  
  
• Caching and double buffering
  
  
• Opening databases
  
  
• Attached tables
  
  
• SQL
  
  
• Transactions
  
  
• Locating records
  
  
• Other tips

How you improve performance in a database application depends on what kind of performance improvement you need. You might need some of the following kinds of performance improvements more than others:

• Better query speed
  
  
• Faster record location
  
  
• Faster scrolling through records
  
  
• Up-to-date record content in multi-user environments
  
  
• Better performance with external databases, especially ODBC data sources

Best Tip

The design of your data is usually a bigger factor in performance than the design of your code:

• Use Microsoft Access to examine your database design, queries, and indexes. Run your queries in Access and use the results to adjust your table and index designs for better performance. Then save the queries in your database for use from your code.
  
  
• Normalize your database schema to avoid storing multiple copies of your data. Consult any standard database text, such as C.J. Date's Introduction to Database Systems, 10th edition (Addison-Wesley, 1995), or consult the Microsoft Access documentation.

Also:

• Store infrequently updated tables in your local Microsoft Jet (.MDB) database. If the data doesn’t change often, you can keep a local copy for queries and avoid having to move the data across the network.

Recordset Types

• In general, use a table-type recordset rather than either a dynaset-type recordset or a snapshot-type recordset if possible.
  
  
• For remote data, use snapshot-type recordsets rather than dynaset-type recordsets. But beware of Memo fields, especially in ODBC data sources. If the data contains Memo fields, use a dynaset-type recordset instead if you won’t be retrieving all the fields from all the rows. Dynaset-type recordsets are also better for COM objects in ODBC data sources.
  
  
• For ODBC data with COM objects or Memo fields, use dynaset-type recordsets instead of snapshot-type recordsets.

Selecting Records

• For dynaset-type recordsets and snapshot-type recordsets, select only the fields you need instead of all fields.
  
  
• For snapshot-type recordsets against ODBC data sources, use the dbForwardOnly option in your recordsets if you’ll be making a single pass through your data.
  
  
• For dynaset-type recordsets against ODBC data sources, cache multiple records. See the article DAO Recordset: Caching Multiple Records for Performance.
  
  
• If you're adding records to a dynaset-type recordset, especially against an ODBC data source, use the dbAppendOnly option.
  
  
• Requery recordsets rather than reopening them. Note that you lose this advantage if you change filters or sorts before you requery.
  
  
• Parameterize queries instead of using dynamic SQL statements, especially against ODBC data sources.
  
  
• Store queries instead of using dynamic SQL statements, especially on machines with low memory.
  
  
• Refresh current field values by calling Move with a parameter of AFX_MOVE_REFRESH instead of calling MoveNext and MovePrev. (Calling Move with a parameter of 0 is equivalent.)

ODBC

• Attach ODBC tables to a local Microsoft Jet (.MDB) database rather than opening the ODBC data source directly.
  
  
• Reduce your ODBC timeouts for faster performance in failure cases.
  
  
• For ODBC data with COM objects or Memo fields, use dynaset-type recordsets instead of snapshot-type recordsets.
  
  
• For snapshot-type recordsets against ODBC data sources, use the dbForwardOnly option in your recordsets.
  
  
• For dynaset-type recordsets against ODBC data sources, cache multiple records. See the article DAO Recordset: Caching Multiple Records for Performance.
  
  
• With ODBC SQL statements that don’t retrieve data, use pass-through queries where possible. For related information, see the article DAO Querydef: Action Queries and SQL Pass-Through Queries.
  
  
• Speed ODBC finds by downloading to a local indexed table and seeking. If you will be making numerous finds in the data, copy it to a local Microsoft Jet database table and use Seek to locate information.
  
  
• On ODBC data, use Find only on indexed fields; otherwise, open a new recordset using an SQL statement with an appropriate WHERE clause.

For more information about working with ODBC data sources, see the articles Database Topics (DAO) and DAO External: Working with External Data Sources.

Caching and Double Buffering

• For best performance, turn off MFC's double-buffering mechanism. However, the tradeoff is that you must write more code to update a field. For more information, see the article DAO Record Field Exchange: Double Buffering Records.
  
  
• Cache multiple records when you are using an ODBC data source. See the article DAO Recordset: Caching Multiple Records for Performance.
  
  
• Cache tabledef references if they will be used many times. Keep your CDaoQueryDef objects open and reuse them rather than recreating them.

Opening Databases

• Open databases for exclusive use if you are the only user. Open databases as read-only if all users will be read-only.
  
  
• Use the dbDenyWrite option if nobody else will be writing to the database.
  
  
• Retrieve data from ODBC databases by attaching to a Microsoft Jet (.MDB) database instead of opening the ODBC database directly.

Attached Tables

• Attach ODBC tables to a local Microsoft Jet (.MDB) database rather than opening the ODBC data source directly.
  
  
• Open attached Microsoft Jet tables as table-type recordsets by parsing the tabledef connect string for the database name and then opening that database directly.

SQL

• With ODBC SQL statements that don’t retrieve data, use pass-through queries where possible. For related information, see the article DAO Querydef: Action Queries and SQL Pass-Through Queries.
  
  
• Replace code loops that run a query again and again with the equivalent SQL statements to run the query once for the whole loop. For example, rather than doing 100 update calls, run one bulk query for all of the affected records.
  
  
• Replace repeated execution of the same dynamic SQL with a temporary query. (This applies only if you are using a querydef pointer in CDaoRecordset::Open to create your recordset.)

Transactions

• Always embed your MFC DAO code in transactions if you are performing multiple updates. Balance transaction sizes against the likely available memory. Don’t try to do ten thousand large updates in a single transaction. Instead, break the work into smaller lots of, say, 500 records.

Locating Records

• Use Seek rather than Find. (Seek only works with table-type recordsets.)
  
  
• Return to a location in a recordset using bookmarks rather than Find. See the article DAO Recordset: Bookmarks and Record Positions.
  
  
• Speed ODBC finds by downloading to a local indexed table and seeking. If you will be making numerous finds in the data, copy it to a local Microsoft Jet database table and use Seek to locate information.
  
  
• On ODBC data, use Find only on indexed fields; otherwise, open a new recordset.

Other Tips

• Use the power of Microsoft Jet queries to save writing and debugging code. For example, the Microsoft Jet database engine allows you to update the results of join queries and automatically distributes the changes to the underlying tables.
  
  
• Replace short Memo fields with long text fields.
  
  
• Replace floating-point numbers with integers.