Stacked vs Multiple Inner Joins

[Jack Leach]

Hi all, being a little slow when it comes to queries, I'm wondering if
someone has any advice on which is the more efficient method to use.

Consider three tables, tblOrders, tblOrderDetails, and tblOrderReleases,
each with a one to many with the table listed before it.

tblOrders has a fldStatus, integer, to tell whether the order is open or
closed. tblOrderDetails has item numbers, ect for the order, and
tblOrderReleases has release-specific information for each record in
tblOrderDetails.

So lets say that I want to do some analyzing of the Release records, but
only for orders that are currently open (tblOrders.fldStatus = 1).

Am I better off to set up a single query that references all three tables
with a few inner joins, and directly check the value of the status field
(WHERE tblOrders.fldStatus = 1), or is it more efficient/better practice to
use stacked queries for this? Base the query for my actual release
analyzation off a seperate query for only open orders (SELECT * FROM
tblOrders WHERE fldStatus = 1)?


I might also mention that the Status field of tblOrders is, in essence,
storage of a calculated value. The open/closed status of an order can be
checked through analyzation of a ShippedComplete and BilledComplete fields
that are held on a Release basis. So, I can also construct a query of
tblOrderReleases WHERE (fldShipComp = -1) AND (fldBilledComp = -1).

Hopefully this makes some sort of sense. Basically I'm just trying to be
aware of any performance issues and other pitfalls between one method and the
other.

Thanks for any insight!
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[KARL DEWEY]

Am I better off to set up a single query that references all three tables
with a few inner joins,
I would use left joins instead.
FROM (tblOrders LEFT JOIN tblOrderDetails ON tblOrders.PrimaryKey =
tblOrderDetails.ForeignKey) LEFT JOIN tblOrderReleases ON
tblOrderDetails.PrimaryKey = tblOrderReleases.ForeignKey

Use whatever criteria gives you the level of information needed.

 

[John Spencer]

In most cases you won't see much difference in performance.

I usually use joins as they should be optimized by the query engine to give
you the best performance. If the query is slow I might look at using stacked
queries and see if the performance is better. Sometimes one method works
better than the other, sometimes I see no discernible difference.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Jack Leach]

Thanks guys

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Jack Leach]

One more quick one if I may...

If I have a where clause as follows:

WHERE (tblOrders.fldStatus = 0) AND (tblOrderReleases.fldBEdComp = 0)

then JET will not bother checking tblOrderReleases.fldBEdComp if
tblOrders.fldStatus is anything but 0, correct?

For efficiency, I'm looking to completely skip any evaluation of the
Releases table unless tblOrders.fldStatus = 0... i.e - I want the order
status checked first, and if it's in criteria continue to check the releases
records

I believe I have this correct, just looking for a confirmation. Sorry for
my ignorance when it comes to SQL, I've never been strong in it and am making
an attempt to confirm all those "I thinks" that I have laying around.

Thanks again!
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[John Spencer]

I don't know about that. I have observed queries that seem to check every
criteria no matter what, but no one outside of Microsoft knows how the query
engine actually works (and I'm not sure there is anyone in Microsoft that
knows all the details).

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Jack Leach]

your theory is blown

.....yup


tblTest1 contains:

fldID fldDesc
1 SomeVal
2 OtherVal
3 DifferentVal
4 SomeOtherVal


tested with the following query:

SELECT tblTest1.*
FROM tblTest1
WHERE (((TestIt([tblTest1].[fldID]))=False)
AND ((TestIt([tblTest1].[fldDesc]))=False));

with the following function:

Public Function TestIt(varVal As Variant) As Boolean
MsgBox "Function Ran " & CStr(varVal)
TestIt = False
End Function


returns messageboxes of values in the following order...

SomeVal
1
OtherVal
2
DifferentVal
3
SomeOtherVal
4


Furthermore, if we change the TestIt function to return True and leave the
criteria the same, the message sequence contains only text and not numbers...
so, the "right side" column (and criteria) in this case is being tested first.

Moving on to a one-to-many design, when the same concept is applied, the
criteria works the same... the "many" side of the two tables (and the right
side of the AND operator) is evaluated first. Completely the opposite of
what I would have expected.

Oh well, thanks for the idea for the test anyway.

cheers,
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Jack Leach]

your theory is blown

.....yup


tblTest1 contains:

fldID fldDesc
1 SomeVal
2 OtherVal
3 DifferentVal
4 SomeOtherVal


tested with the following query:

SELECT tblTest1.*
FROM tblTest1
WHERE (((TestIt([tblTest1].[fldID]))=False)
AND ((TestIt([tblTest1].[fldDesc]))=False));

with the following function:

Public Function TestIt(varVal As Variant) As Boolean
MsgBox "Function Ran " & CStr(varVal)
TestIt = False
End Function


returns messageboxes of values in the following order...

SomeVal
1
OtherVal
2
DifferentVal
3
SomeOtherVal
4


Furthermore, if we change the TestIt function to return True and leave the
criteria the same, the message sequence contains only text and not numbers...
so, the "right side" column (and criteria) in this case is being tested first.

Moving on to a one-to-many design, when the same concept is applied, the
criteria works the same... the "many" side of the two tables (and the right
side of the AND operator) is evaluated first. Completely the opposite of
what I would have expected.

Oh well, thanks for the idea for the test anyway.

cheers,
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[david]

The query optimiser optimises stacked queries

as a single query, so it makes no difference to

the final result.



However, re-ordering any query, stacked,

sub-query or joined, means that the query enters

the optimiser slightly differently, so the optimiser

may not find the same result. There is no way

to predict if one way of writing it will be better

than another way.



Also, a very complex stacked query is still just a

very complex query. There are bugs in the query

optimiser which sometimes surface when dealing

with very complex queries.



FWIW, given that these rare bugs have never been

fixed, and looking at the litany of failure over the last

10 years of changes to JET, I am 100% sure that

no-one at MS understands the JET query optimiser



(david)