Understanding Linked Tables

M

Maury Markowitz

I am trying to speed up a number of long-running queries in our application.
The "bad ones" are invariably those that join tables from our two data
sources. I have a feeling that Jet is basically doing "select *" on both and
then doing the join locally.

Is there some sort of description I can read on what Jet does with these
sorts of queries? If the problem is as I suspect, I'll need to fix it
somehow. A passthrough doesn't seem to solve the problem, because it's two
different servers -- there's nowhere to "pass through" to.

Maury
 
J

John Vinson

I am trying to speed up a number of long-running queries in our application.
The "bad ones" are invariably those that join tables from our two data
sources. I have a feeling that Jet is basically doing "select *" on both and
then doing the join locally.
Click to expand...

Yep. What else CAN it do?
Is there some sort of description I can read on what Jet does with these
sorts of queries? If the problem is as I suspect, I'll need to fix it
somehow. A passthrough doesn't seem to solve the problem, because it's two
different servers -- there's nowhere to "pass through" to.
Click to expand...

Well, you have a problem then!

Is there any way to separately filter the two tables prior to joining
them? You could create two temporary local tables containing fewer
records (using whatever criteria are available on each table
separately) and join the two resulting tables.


John W. Vinson[MVP]
 
M

Maury Markowitz

John Vinson said:
Yep. What else CAN it do?
Click to expand...

Use the WHERE clauses and send subqueries to each server returning only
the rows it needs?
Is there any way to separately filter the two tables prior to joining
them? You could create two temporary local tables containing fewer
records (using whatever criteria are available on each table
separately) and join the two resulting tables.
Click to expand...

I think so, but it seems this would really complicate the queries. I think
what I'll try first is using a Linked Server in SQL Server. Apparently it
_will_ devolve the queries into subqueries for each server.

It will be interesting to experiment anyway, something I should learn one
way or the other.

Maury
 
Top