Access local table joining linked table question

Question

Access local table joining linked table question

Randy Doub

SSCertifiable

Points: 5322
More actions
November 29, 2012 at 8:05 am

#270859

In Access, if I query sql server and build a local table of something like 30,000 id numbers, then join that local table to a linked sql server table, what actually happens? Does Access send those 30,000 id numbers to sql server to perform the join and run the query?
Thanks all.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Randy Doub (11/29/2012)
In Access, if I query sql server and build a local table of something like 30,000 id numbers, then join that local table to a linked sql server table, what actually happens? Does Access send those 30,000 id numbers to sql server to perform the join and run the query?
Thanks all.

No idea what Access does, but SQL Server 2008 (this is the SQL Server 2008 forum section) permits you to choose using the REMOTE join hint.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Andrew Kernodle SSCertifiable Points: 5314 More actions · Answer 2

If I remember correctly, the calculations are run entirely on the Access end of the program; Access itself will pull the entirety of the data from the linked table, make the join, and display the results. The exception to this is with pass-through queries, which basically just use Access as the returning program for the query; you send a SQL statement through Access, the SQL database processes the query and returns results, and Access displays them.

- 😀

Banana-823045 SSCommitted Points: 1689 More actions · Answer 3

hisakatama is essentially correct - Access has to be the one to perform the heterogeneous join since it has access to data from both sides (SQL Server knows nothing about the Access table). However, the specific "How" can vary slightly:

1) If you join the Access table to a pass through query then it can execute passthrough query once and need not any further communication with SQL Server

2) If you join the Access table to a linked table, things can get complicated -- with a dynaset recordset, Access may choose to fetch all keys from the linked tables and send requests for rest of data row by row on demand. This is either good or bad.

3) Access is smart enough to push any sargable predicates back to SQL Server where it can. So if you join to linked table / passthru query that contains a filter, Access will marshal that filter back to SQL Server but it still needs to process the join itself and for non-sargable predicates, it must pull in more data and evaluate the filter locally, discarding the data. So if you can, ensure that you join only on rows you actually want.