More efficient query design.. (1 Viewer)

[CrystalSurfer]

I have written a query to calculate how many hours we have worked for our clients. This has a number of sub-queries which sum to make different columns in the main query ie: Hours invoiced so far, Hours to be invoiced, Hours remaining from their allocation, etc.
I am refreshing the queries due to a design change and now have a question..

Which is the better design:

To have those sub-queries with selection criteria from a join to the same table in each (to filter out records at a lower level)
OR
To have that same selection criteria and join just once in the main query? (which of course means its processing many more records but the join to the selection criteria is specified once)

answers on a postcard please..

 

[llkhoutx]

My experience is that queries can be very elegantly constructed with complex joins, but serial simple multiple queries execute faster and are easier to modify.

 

[CrystalSurfer]

Hey thanks.
Having selection criteria lower down in the multiple sub-queries does mean you have to amend all of them though, whereas if its in the main top query you just have to amend it once.

 

[llkhoutx]

A query is a query, regardless of the criteria. Build it once and you have it.