If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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) |
#2
|
|||
|
|||
Stacked vs Multiple Inner Joins
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) |
#3
|
|||
|
|||
Stacked vs Multiple Inner Joins
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! |
#4
|
|||
|
|||
Stacked vs Multiple Inner Joins
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) |
#5
|
|||
|
|||
Stacked vs Multiple Inner Joins
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) |
#6
|
|||
|
|||
Stacked vs Multiple Inner Joins
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! |
#7
|
|||
|
|||
Stacked vs Multiple Inner Joins
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] |
#8
|
|||
|
|||
Stacked vs Multiple Inner Joins
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] . |
#9
|
|||
|
|||
Stacked vs Multiple Inner Joins
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] . |
#10
|
|||
|
|||
Stacked vs Multiple Inner Joins
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. |
|
Thread Tools | |
Display Modes | |
|
|