Stacked vs Multiple Inner Joins
in [Queries]
|
From: Jack Leach dymondjack at hot mail dot on 6 May 2010 14:15 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)
From: KARL DEWEY on 6 May 2010 15:15 >>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) >
From: John Spencer on 6 May 2010 15:17 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!
From: Jack Leach dymondjack at hot mail dot on 6 May 2010 16:00 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) >
From: Jack Leach dymondjack at hot mail dot on 6 May 2010 16:21
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) > |