Steve R.
Retired
- Local time
- Today, 17:27
- Joined
- Jul 5, 2006
- Messages
- 5,674
The short answer appears that a query based on (ODBC) linked tables is marginally faster than a pass-through query.
In reading on how to create a pass-through query, the narrative made it seem that it would be faster. But then, the pass-though narrative mentioned duplicating the ODBC connection to your database. Given that
duplication, I was wondering if it would actually be any faster.
From MS Access Help:
I have an ODBC linked table connected to SQL Server with just over 10,000 records. As an experiment, to see if there would be any performance improvement by using a pass-through query, I ran the following code 10 time each. Both queries just return the record number to populate a combobox.
Pass-through query:
ODBC Linked query:
Both queries are bare bones, only returning one field, but I figured that if a pass-through query was faster that it would still show-up based on the fact that there were just over 10,000 records. But, my assumption could be wrong. Hence the post.
The query using the linked tables appeared marginally faster. Based on those results, I will stick with using queries based on the ODBC linked tables.
In reading on how to create a pass-through query, the narrative made it seem that it would be faster. But then, the pass-though narrative mentioned duplicating the ODBC connection to your database. Given that
duplication, I was wondering if it would actually be any faster.
From MS Access Help:
In some situations, however, you may want SQL processing to be performed by the server computer. ..... By using pass-through queries, you work directly with the tables on the server instead of the data being processed by the Access database engine.).
I have an ODBC linked table connected to SQL Server with just over 10,000 records. As an experiment, to see if there would be any performance improvement by using a pass-through query, I ran the following code 10 time each. Both queries just return the record number to populate a combobox.
Pass-through query:
Code:
Me.RecordIDnum01.RowSource = "SELECT * FROM Query1 ORDER BY RecordIDnum;"
ODBC Linked query:
Code:
Me.RecordIDnum01.RowSource = "SELECT RecordIDnum FROM " & csInspectionTable & " ORDER BY RecordIDnum;"
Both queries are bare bones, only returning one field, but I figured that if a pass-through query was faster that it would still show-up based on the fact that there were just over 10,000 records. But, my assumption could be wrong. Hence the post.
The query using the linked tables appeared marginally faster. Based on those results, I will stick with using queries based on the ODBC linked tables.