Add as many Where's as possible

Hello,

This was my first query. This is a subquery in a join, the date used for the DaysalHead table is specified on the on conditions and is not a part of this sample.

SELECT COMPC_START, COUNT(DSH_ID) PERFORMANCES

FROM TS.DAYSALHEAD DSH

INNER JOIN GEN.COMPLEX_DATES CD ON CD.COMP_UI = DSH.COMP_UI AND CD.COMPD_DATE = DSH.DSH_DATE

INNER JOIN GEN.COMPLEX_CALENDAR CC ON CC.COMPC_UI = CD.COMPC_UI

INNER JOIN NATF.MOVIESLINKING ML ON ML.MOV_UI = DSH.MOV_UI

WHERE ML.NMOV_UI = DSD.NMOV_UI

AND DSH.COMP_UI = 168

GROUP BY COMPC_START

HAVING COUNT(DSH_ID) > 3

It runs fine and when i watch the executionplan it is using the correct indexes to scan, the most 'un-used' rows come from the complex_dates, although sql goes over the clustered index that is on comp_ui, compd_date.

Now i've changed the query to

SELECT COMPC_START, COUNT(DSH_ID) PERFORMANCES

FROM TS.DAYSALHEAD DSH

INNER JOIN GEN.COMPLEX_DATES CD ON CD.COMP_UI = DSH.COMP_UI AND CD.COMPD_DATE = DSH.DSH_DATE

INNER JOIN GEN.COMPLEX_CALENDAR CC ON CC.COMPC_UI = CD.COMPC_UI

INNER JOIN NATF.MOVIESLINKING ML ON ML.MOV_UI = DSH.MOV_UI

WHERE ML.NMOV_UI = DSD.NMOV_UI

AND DSH.COMP_UI = 168

AND CD.COMP_UI= 168 AND CD.COMPD_DATE = '01/01/2006'

GROUP BY COMPC_START

HAVING COUNT(DSH_ID) > 3

Results are the same, but when i examine the execution plan the costs are much lower.

This means that adding wherever you can extra where's (although they are known in this case through the join) increases speed ...

Any remarks ?

Harry_Leboeuf wrote:

AND CD.COMP_UI= 168 AND CD.COMPD_DATE = '01/01/2006'

I would expect that anytime you constrain the resultset down to one specific date, the execution would be faster. That seems, to me, to be a 'no-brainer'.

|||The query optimizer tries to generate the smallest set possible, with the constraints stated. So by using CD.COMP_UI= 168 AND CD.COMPD_DATE = '01/01/2006', I would guess it decided CD with that constriant made the smallest initial dataset.