Stacked vs Multiple Inner Joins

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)

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.

--
Build a little, test a little.


"Jack Leach" wrote:

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)

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 wrote:
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!

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" wrote:

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)

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)



"Jack Leach" wrote:

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)

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 wrote:
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 dymondjack at hot mail dot com wrote:

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.


You can test that hypothesis by creating and using a
function that generates a message and returns False. If you
get two messages per record then your theory is blown. Do
this on a very small table or you will be clicking message
boxes all day long.

Public Function Test(junk)
MsgBox Junk
Test = False
End Function

then create a query on a very small table:

SELECT table.*
FROM table
WHERE test(thisfield) AND text(thatfield)

The function must us a table fiel as an argument or the
query will be optimized to only evaluate the function one
time.

--
Marsh
MVP [MS Access]

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)



"Marshall Barton" wrote:

Jack Leach dymondjack at hot mail dot com wrote:

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.


You can test that hypothesis by creating and using a
function that generates a message and returns False. If you
get two messages per record then your theory is blown. Do
this on a very small table or you will be clicking message
boxes all day long.

Public Function Test(junk)
MsgBox Junk
Test = False
End Function

then create a query on a very small table:

SELECT table.*
FROM table
WHERE test(thisfield) AND text(thatfield)

The function must us a table fiel as an argument or the
query will be optimized to only evaluate the function one
time.

--
Marsh
MVP [MS Access]
.

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)



"Marshall Barton" wrote:

Jack Leach dymondjack at hot mail dot com wrote:

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.


You can test that hypothesis by creating and using a
function that generates a message and returns False. If you
get two messages per record then your theory is blown. Do
this on a very small table or you will be clicking message
boxes all day long.

Public Function Test(junk)
MsgBox Junk
Test = False
End Function

then create a query on a very small table:

SELECT table.*
FROM table
WHERE test(thisfield) AND text(thatfield)

The function must us a table fiel as an argument or the
query will be optimized to only evaluate the function one
time.

--
Marsh
MVP [MS Access]
.

No. Actually your test verifies your at least part of your
theory. When one of the Where conditions is False, the
other condition is not checked.

But, don't leap to conclusions about the condition that is
evaluated first. The condition that is tested first is
chosen by the (very powerful) query optimizer, which I
believe depends on what the optimizer thinks is the
easiest/fastest index to search. (Note that an index search
is always faster than a non index search.) If neither field
is indexed, I'm pretty sure a numeric field will be
evaluated before a text condition. And simple conditions
will be checked before more complex conditions.
--
Marsh
MVP [MS Access]


Jack Leach dymondjack at hot mail dot com wrote:

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.