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:
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.
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.
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 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 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.
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.)
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.