Order of table

[fiefie.niles]

I am using MS Access.

In my query I LEFT JOIN 1 table and another query, when I do
FROM queryA LEFT JOIN tblB ON queryA.key = tblB.Key
the query runs 3-4 seconds.

When I do
FROM queryA LEFT JOIN tblB ON tblB.key = queryA.Key
the query runs 1 second.

I can also do INNER JOIN, and the order of the table also makes a
difference.
FROM queryA INNER JOIN tblB ON queryA.key = tblB.Key runs 3-4 seconds.
FROM queryA INNER JOIN tblB ON tblB.key = queryA.Key runs 1 seconds.

What is the reason why the order of the table makes a difference in
speed ?

Thank you very much.

 

[Bob Barrows [MVP]]

I am using MS Access.

Just curious: why did you include an ADO group in this crosspost? Your
question has nothing to do with ADO, does it? Best practice is to post your
question to one group.

More below:

In my query I LEFT JOIN 1 table and another query, when I do
FROM queryA LEFT JOIN tblB ON queryA.key = tblB.Key
the query runs 3-4 seconds.

When I do
FROM queryA LEFT JOIN tblB ON tblB.key = queryA.Key
the query runs 1 second.

I can also do INNER JOIN, and the order of the table also makes a
difference.
FROM queryA INNER JOIN tblB ON queryA.key = tblB.Key runs 3-4 seconds.
FROM queryA INNER JOIN tblB ON tblB.key = queryA.Key runs 1 seconds.

What is the reason why the order of the table makes a difference in
speed ?

Thank you very much.

The order of outer joins can certainly matter, especially when one table has
many more records than the other. Think about what you are telling the query
engine to do: take all the records from one table, then find the matching
records in the other table. This will usually involve the creation of
temporary work tables, and if one table is much larger than the other, then
it certainly matters which table is the "outer" table in the query.

However, the order in which you write your operands in the ON expressions
definitely should not matter. Something else is going on. Did you run your
queries enough time when testing them to eliminate extraneous issues such
as caching, i/o, etc?

 

[fiefie.niles]

Thank you.

The order of outer joins can certainly matter

Did you mean the following will make a difference:
FROM queryA LEFT JOIN tblB ON ..
and
FROM tblB LEFT JOIN queryA ON ..

FROM queryA INNER JOIN tblB ON ..
and
FROM tblB INNER JOIN queryA ON ..

 

[Bob Barrows [MVP]]

Thank you.
Did you mean the following will make a difference:
FROM queryA LEFT JOIN tblB ON ..
and
FROM tblB LEFT JOIN queryA ON ..
Yes.


FROM queryA INNER JOIN tblB ON ..
and
FROM tblB INNER JOIN queryA ON ..

But inner joins should be the same

 

[fiefie.niles]

Thank you.
Please confirm, so if queryA has more records/rows than tblB, on INNER
JOIN it does not matter which table appear first, but on a LEFT JOIN it
will be faster to do
FROM tblB LEFT JOIN queryA ON ..
than
FROM queryA LEFT JOIN tblB ON ..

 

[Bob Barrows [MVP]]

Thank you.
Please confirm, so if queryA has more records/rows than tblB, on INNER
JOIN it does not matter which table appear first, but on a LEFT JOIN
it will be faster to do
FROM tblB LEFT JOIN queryA ON ..
than
FROM queryA LEFT JOIN tblB ON ..

I don't believe i can answer this in a vacuum, but:
Why would performance matter? An outer join (left, right or full) is done
because a particular result is needed, not because one way works faster than
another. A more pertinent question would be: which one will give me the
results i need?

 

[fiefie.niles]

Thank you.
Yes, I understand that.
So, in the query
FROM tblB LEFT JOIN queryA ON ..
if tblB has more items than queryA, the query will run slower than
the other way around, is that correct ?

 

[Bob Barrows [MVP]]

Again, I cannot answer this question in a vacuum. Theoretically, I believe
the answer is "yes", but theory is no replacement for actual testing.

 

[Stephen Howe]

Yes, I understand that.

So, in the query
FROM tblB LEFT JOIN queryA ON ..
if tblB has more items than queryA, the query will run slower than
the other way around, is that correct ?

Not necessarily. It all depends on the Query Optimiser, Indexes and the
database.

I don't believe with SQL Server 2000 and above it will make a scrap a
difference which way round it is because the optimiser is sufficiently
clever enough. The same is not true with Access, there is not the same
degree of sophistication so maybe you have to "help" the Access engine.

In any case Bob's right. This has not got anything to do with ADO and
correctness should come before anything else i.e. the right query to bring
back the right records. If the query seems slows, you can bone up on what is
necessary to get good performance out of the Access engine bearing in mind
that it may not be applicable to other databases.

Stephen Howe