Speed finds by creating a temporary indexed table and seeking

When you know that you are going to do queries that only involve a certain subset of data, you can create a temporary table containing just the rows and columns that you expect to search later. This is created by executing a three or four table join and returning only the needed columns and rows. If you add an index to the temporary table, access to this table is even faster.

Snapshots are a good way to get to this data for ODBC servers, but they cannot be searched with the Seek method because the temporary result that is returned is not indexed. The find methods are unable to use an index for the same reason, and they can be slow if this is a large dynaset. Further WHERE clause refinements to the query still take server time, and if there are a large number of such queries this can be too slow.

One possible solution in this scenario is to do a make-table query to create a "permanent" table in the local database that has its own index, that the time to seek is well worth the time it takes to build. The numbers below show an example of these different approaches with a small table:

7 seconds to create permanent table subset from an ODBC table and index and 0.7 seconds to open it, versus
0.7 seconds to open a snapshot (and movelast)
The times to find data in each of these result sets is:
2.7 seconds to locate each of n records in the table using Seek, versus
21.6 seconds to locate each of n records in the snapshot using findfirst

This does not scale very well with larger tables that are drawn from SQL Server, but you can always use passthrough to create real SQL Server temporary tables and query against those.

When tried against larger .MDB tables the results are as follows:

For a query that returns 564 records,

77 seconds to create a permanent table and index, versus
70 seconds to create a snapshot with the same data (with movelast)

Searching for 342 records:

14 seconds via Seek against the permanent table
83 seconds via FindFirst against the snapshot

There is a 10% price to pay to get a permanent table built vs. a snapshot. However, on the lookup end, the improved performance is dramatic - almost six times as fast. Obviously the number of subsequent searches you expect to be doing is a big factor here.

The downside to using permanent tables like this are:

The developer must ensure that the table is deleted when no longer needed. Jet does not provide for a way to automatically drop these tables when the database or workspace is no longer in scope.
Name collisions on multi-user systems. You must build a guaranteed unique name. (See the Access Knowledge Base article Q88929 for one way of doing this.)

Of course like all the tips given in this paper, you should carefully benchmark any solution to ensure that all the tradeoffs are taken into account.