DAO External: Improving Performance with External Data Sources
| Overview | How Do I | FAQ | Sample | | ODBC Driver List
This article explains some things you can do to improve performance when you connect to external data sources, such as ODBC data sources. For general information about external data sources, see the article DAO External: Working with External Data Sources.
Improving Performance with ODBC Data Sources
If you're connecting to an ODBC data source, the following guidelines apply:
Use attached tables instead of directly opened tables whenever possible.
See the article DAO External: Working with External Data Sources and the topic "Accessing External Databases with DAO" in DAO Help.
Important This recommendation has the most significant impact on performance of all the recommendations in this list.
Retrieve and view only the data you need.
Use restricted queries to limit the number of records you retrieve, and select only the columns you need. This requires transferring less data across the network.
Don't use dynaset-type recordsets if you're not updating the data.
Use forward-scrolling snapshot-type recordsets if you're only scrolling forward. Don't scroll through records unnecessarily, and avoid jumping to the last record of a large table.
Use caching.
In class , MFC supports caching a specified number of records. Doing so takes longer initially, when the data is retrieved into the cache, but moving through the records in the cache is faster than retrieving each record as it is scrolled to.
Turn off the double-buffering option in MFC objects.
This is a general way to improve performance that applies as well to working with external data sources.
For bulk operations, such as adding records in bulk, use an SQL pass-through query. Call for the representing your .MDB database. Then call the database object’s member function, or create a recordset, with the dbSQLPassThrough option set. For more information about pass-through queries, see the article DAO Querydef: Action Queries and SQL Pass-Through Queries. (You only need to set the connection information once, if you are always performing your SQL pass-through queries through the same connection.)
Avoid using queries that cause processing to be done locally.
Don't use user-defined functions with remote column arguments. Use heterogeneous joins (joins on tables in two databases) only on indexed columns, and realize if you do this that some processing is done locally. When accessing external data, the Microsoft Jet database engine processes data locally only when the operation can't be performed by the external database. Query operations performed locally include:
WHERE clause restrictions on top of a query with a DISTINCT predicate.
WHERE clauses containing operations that can't be processed remotely, such as user-defined functions that involve remote columns. (Note that in this case only the parts of the WHERE clause that can't be processed remotely will be processed locally.)
Joins between tables from different data sources.
Note Simply having joins between tables from different data sources doesn't mean that all of the processing occurs locally. If restrictions are sent to the server, only relevant rows are processed locally.
Joins over aggregation or the DISTINCT predicate.
Outer joins containing syntax not supported by the ODBC driver.
DISTINCT predicates containing operations that can't be processed remotely.
ORDER BY arguments (if the remote data source doesn't support them).
ORDER BY or GROUP BY arguments containing operations that can't be processed remotely.
Multiple-level GROUP BY arguments, such as those used in reports with multiple grouping levels.
GROUP BY arguments on top of a query with a DISTINCT option.
Cross-tab queries that have more than one aggregate or that have an ORDER BY clause that matches the GROUP BY clause.
TOP or TOP PERCENT predicate.
See Also DAO: Where Is..., DAO External: Working with External Data Sources, DAO External: Attaching External Tables