Performance of a Query Based on Linked Tables Compared to a Pass-Through Query

[Steve R.]

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:

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:

Me.RecordIDnum01.RowSource = "SELECT * FROM Query1 ORDER BY RecordIDnum;"

ODBC Linked query:

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.

 

[pbaldy]

I'd say the long answer is "it depends". Access JET/ACE is pretty good at passing the SQL back to the server anyway, so your simple query is being processed by the server either way. Your query isn't really a good test, because it's returning all records, so there wouldn't really be a speed advantage. The pass through would be faster when a restricted data set was being returned, and for whatever reason the linked query can't be processed by the server.

For instance, if you use IIf() in your query, SQL Server may shrug it's shoulders and pass all 10k records to the front end to process because it doesn't understand that function. If you use a pass through with the T-SQL equivalent, SQL Server will process it and just send back the 1 desired record. In that situation, it would be much faster.

 

[Steve R.]

Your query isn't really a good test, because it's returning all records, so there wouldn't really be a speed advantage. The pass through would be faster when a restricted data set was being returned, .. .

Good point.

If you use a pass through with the T-SQL equivalent, SQL Server will process it and just send back the 1 desired record. In that situation, it would be much faster.

I've seen references to T-SQL and look forward to experimenting with that approach.

 

[pbaldy]

Your pass through query must have used T-SQL, though in many regards it's the same as JET/ACE SQL. My buddy Leigh has an excellent resource here:

http://www.utteraccess.com/forum/Beginning-Sql-Server-Deve-t1732935.html

Down near the bottom are some of the syntax differences.

 

[Steve R.]

Your pass through query must have used T-SQL, though in many regards it's the same as JET/ACE SQL.

Thanks for responding, I missed picking up on your response till today. I had purposely chosen testing criteria to avoid the T-SQL syntax issue. Which brings me now to the results of a new test.

I developed a hard coded query that would return only 5 records out of the approximate 10,000 records available. The pass-though query was imperceptibly faster than the JET/ACE SQL query. The Access query occasionally gave a result of 1 second versus 0 seconds for all the pass-through queries.

Essentially a reverse of the prior results from my first testing which was based on returning all records for one field, which you pointed-out as being an incorrect approach.

I will keep digging into this. It's an excellent learning challenge. We seem to be adding about 1,500 records per year. Though, it is a very simple database, the need for efficient code becomes ever more important.

 

[pbaldy]

Like I said, JET/ACE is pretty good about letting the server process the query anyway, so that's no surprise. Both were likely being processed by the server and only the 5 records were being sent over the network.

You won't see much difference until you have an Access query that can't be processed by the server. I work with SQL back ends most of the time, and probably 80-90% of my queries are Access queries. I don't switch to a pass through (or SP, etc) until there's a performance issue.

 

[Steve R.]

You won't see much difference until you have an Access query that can't be processed by the server. I work with SQL back ends most of the time, and probably 80-90% of my queries are Access queries. I don't switch to a pass through (or SP, etc) until there's a performance issue.

Excellent point. Thanks.

 

[syswizard]

This likely applies to SELECT queries. INSERT queries are a whole different story. Server-side queries are about 5x to 10x faster than local queries.

 

[Steve R.]

This likely applies to SELECT queries. INSERT queries are a whole different story. Server-side queries are about 5x to 10x faster than local queries.

Thanks for the added information. So far, they are all SELECT queries to return a filtered recordset back to the local PC.