A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Stacked vs Multiple Inner Joins



 
 
Thread Tools Display Modes
  #11  
Old May 8th, 2010, 01:18 AM posted to microsoft.public.access.queries
david
external usenet poster
 
Posts: 34
Default Stacked vs Multiple Inner Joins

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
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)



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:35 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.