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)