Home | Overview | How Do I | FAQ | Sample | Tutorial | 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.
If you're connecting to an ODBC data source, the following guidelines apply:
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.
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.
In class CDaoRecordset, 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.
This is a general way to improve performance that applies as well to working with external data sources.
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:
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.
See Also DAO: Where Is..., DAO External: Working with External Data Sources, DAO External: Attaching External Tables