Optimizing ODBC Performance

The fastest way to access ODBC data is first to use Microsoft Access to create linked tables in an .mdb file and then to use those linked tables in your DAO code. Although older versions of DAO permitted you to do it, one of the slowest and most inefficient ways to process server data is to open a dynaset on an attached ODBC table directly and then proceed to move through the dynaset.

Many of the same techniques used to optimize DAO code — excluding the ones previously noted — can be used to optimize ODBC performance as well. In addition to the techniques suggested in "Optimizing DAO Code" earlier in this chapter, there are several techniques that apply specifically to improving ODBC data performance.

If you don't have Microsoft Access, you can create your own linked tables in an .mdb file by using code such as that shown in the following example. You only have to do this once, after which you can reuse the .mdb file in multiple applications.

Dim db As Database
Dim td as Tabledef
Set db = CreateDatabase("d:\attached\CorpSQL.mdb", dbLangGeneral)
Set td = db.CreateTableDef("OrderDetail")
td.Connect = "ODBC;DSN=SSRVR1;UID=Fred;PWD=RHS;DATABASE=SQLDB;"
td.SourceTableName = "OrderDetails"
db.Tabledefs.Append td

For more information, see "Accessing External Databases with DAO" in Help.

Using Remote Data Caching

You can improve your application's performance by caching remote data. A cache is a space in local memory that holds the data most recently retrieved from the server. When data is requested from an ODBC source, Jet first checks the cache for the requested data, which takes less time than retrieving it from the server. If you anticipate working extensively in a small range of records (fewer than 200), you should use remote data caching. Use the CacheStart and CacheSize properties to specify the range and size you want within the result set. Use the FillCache method to quickly fill all or part of this range with data from the server.

Dim rs As Recordset
Dim db As Database

Set db = OpenDatabase("e:\attached\CorpSQL.mdb")
Set rs = db.OpenRecordset("OrderDetail", dbOpenDynaset)
rs.FindFirst "CustID = 1001"
rs.CacheStart = rs.Bookmark
rs.CacheSize = 50
rs.FillCache

Note

This code assumes that you have an .mdb file with an OrderDetail table attached to an ODBC data source.

Using SQL Pass-Through Queries

A SQL pass-through query is a specialized type of SQL query that's designed to bypass the Jet engine and communicate directly with a SQL server. In a regular query, Jet resolves references to fields, tables, and functions when the SQL statement is compiled. In a pass-through query, Jet hands the entire query over to the server to compile and resolve references. There are several advantages to running a SQL pass-through query:

You can take advantage of server-specific functionality that doesn't exist at the local level (such as SQL stored procedures) when you write your statement. For more information about stored procedures, see your server software manual.
SQL pass-through queries are faster than local queries because they communicate directly with the SQL server, which can process data, compile SQL code, and return a result set faster that the Jet engine can.
Because the entire query is processed on the server and only the Recordset object is returned, you have less network traffic while data is being requested and returned.

The following example creates a temporary pass-through query.

Dim db as Database
Dim tempqry as QueryDef

Set db = OpenDatabase("Mydb.mdb")
Set tempqry = db.CreateQueryDef("")
db.Connect = "ODBC; DSN=MySQLDBD; UIS=Guest;PWD=''
PassThrough.ReturnsRecords = True
tempqry.SQL = "UPDATE Orders Set ShipCity = 'LondonTown' WHERE _
    ShipCity = 'London'"
tmpqry.Execute
tmpqry.SQL = "UPDATE Orders Set ShipCity = 'New York City' WHERE _
    ShipCity = 'New York'"
tempqry.Execute