Stacked vs Multiple Inner Joins
in [Queries]
|
From: John Spencer on 6 May 2010 16:43 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!
From: Marshall Barton on 6 May 2010 18:36 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]
From: Jack Leach dymondjack at hot mail dot on 6 May 2010 19:41 > 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] > . >
From: Marshall Barton on 7 May 2010 16:26 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.
From: david on 7 May 2010 20:18
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) "Jack Leach" <dymondjack at hot mail dot com> wrote in message news:DDE4F42C-EF0C-4B97-8B8A-3FED93E9EE33(a)microsoft.com... > 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) > |