Speed of multiple queries vs. one master query?

[wpilgri]

I have a database that is built as follows:

query1: gather all size info(numbers) for all parts
query2: Using query1, find average size for each part
query3: calculate tolerances for parts
query4: Use query2 and query3 to combine avg size and tolerances

query4 then gets used for reporting. When I want to work with a limited set of data I use a report filter (query4 where type=xyz123) to reduce the number of parts to the ones I am interested in. My question is:

Q1: Would my top level query (query4) be faster if I combined the other queries into one massive query rather than relying on calling this hierarchy of queries?

Q2: I think I see the error of my setup with reporting. Can someone confirm that I should do my filtering for reporting at the query level rather than the report level? The way it is now, the queries run and calculate all that information for ALL records in the database, then I apply the filter to show perhaps a small subset of data. It would be smarter/faster to filter the data in query1 and have the report show all data that ends up in query4. Can someone confirm that this is the best way to do things? I want to update it but it is a lot of work so I prefer to do the changes once.

Thank You

 

[PHV]

That make sense that filtering query1 will reduce time execution for all the 4 queries.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MichaelRed]

The overall speed probably depends on a number of factors, but filtering through a seperate select process could be useful for LARGE data sets. A potential down side is the need (for Ms. A.) to instantiate the seperate queries. You should be aware that Jet (and SQL database engines in general) are 'set orientated' (i.e. optimized to process groups of records) not to operate on individual objects, thus it is often better to combine (recordset) operations than to seperate them as "layeded" processes.




MichaelRed