Performance of MS Access when JOINing from linked tables in different servers?

Question

If I have an MS Access database with linked tables from two different database servers (say one table from an SQL Server db and one from an Oracle db) and I write a query which JOINs those two tables, how will Access (or the Jet engine, I guess?) handle this query? Will it issue some SELECTs on each table first to get the fields I'm JOINing on, figurre out which rows match, then issue more SELECTs for those rows?

Answer 1

That would be my guess. It helps if there are indexes on both sides of the join but, as neither server has full control over the query, further query optimization is not possible.

Answer 2

I have no practical experience joining tables from two different data systems. However, depending on the requirements, etc, etc, you may find it faster to run SELECT queries with only the records and fields required into Access tables and do the final join and query in Access.

Answer 3

Um.... Ew?

Answer 4

Yep, you can have some serious performance issues. I have done this type of thing for years. Oracle, Sql, and DB2 - ugh. Sometimes I have had to set it up on a timer at 5:00am so when I get in at 7:00 it's done.

If your dataset is significant enough, it is often faster to build a table locally and then link the data. For remote datasets, also look into passthroughs.

For example, lets say you are pulling all of yesterday's customers from the oracle db and all of the customer purchases from the sql db. Let's say you have an average of 100 customers daily but a list of 30,000 and lets say your products have a list of 500,000. You could query the oracle db for your list of 100 customers, then write it as in IN statement in a passthrough query to the sql db. You'll get your data almost instantly.

Or if your recordsets are huge, build local tables of the two IDs, compare them locally and then just pull the necessary matches.

It's ugly but you can save yourself hours literally.

Answer 5

The key thing to understand is this:

Are you asking a question that Access/Jet can optimize before it sends its request to the two server databases? If you're joining the entirety of both tables, Jet will have to request both tables, which would be ugly.

If, on the other hand, you can provide criteria that limit one or both sides of the join, Access/Jet can be more efficient and request the filtered resultset instead of the full table.