Hash join switch build table (statistics are not helping with estimates)

GokhanVarol 2013-04-09 20:04:53

link text
alt text

The query I also supplied a screenshot for has many joins but the two in the bottom right are the ones question refers to. the Trans.tTrans_Trans_PKC clustered index scan has higher estimation (because the filter used does not produce good estimation) and tTax_Property_PKC has higher estimation and optimizer chooses the Trans.tTrans_Trans_PKC flow for the hash build phase. I want to revert the table hash build phase to tTax_Property_PKC without affecting the rest of the query plan

If I eliminate the where clause applied to Trans.tTrans_Trans_PKC which is pasted below I get the query plan I would like to get but I could not hide the filters from query optimizer at build phase.
Is there any way I can change the hash join build table ?
Thank you

           tt.PrimaryCatCd IN ( 'A', 'B' ) /* Arms Length/Non-Arms Length Purchase      */
                AND ISNULL(tt.SaleDtDerived, tt.RecordingDtDerived) BETWEEN @StartDate AND @EndDate
                AND SUBSTRING(CAST(ISNULL(tt.SaleDt, tt.RecordingDt) AS VARCHAR), 5, 2) <> '00' /* Ignore sales for month 00*/
GokhanVarol 2013-04-10 00:05:01
I found one workaround it seems like it.
Modified statistics on temp table

UPDATE STATISTICS #CBSA WITH ROWCOUNT = 10000, PAGECOUNT = 10000

created a fake variable

@TransFilter_Fake BIT = 1

and modified the where clause to hide the filter that is causing estimates to be too low with optimize for query option.

      WHERE     ( @TransFilter_Fake = 0
                  OR ( tt.PrimaryCatCd IN ( 'A', 'B' ) /* Arms Length/Non-Arms Length Purchase */
                       AND ISNULL(tt.SaleDtDerived, tt.RecordingDtDerived) BETWEEN @StartDate AND @EndDate
                       AND SUBSTRING(CAST(ISNULL(tt.SaleDt, tt.RecordingDt) AS VARCHAR), 5, 2) <> '00' /* Ignore sales for month 00*/
                     )
                )
                AND ( ll_cnty.CntyCd IS NOT NULL
                      OR ll_default.DateTo IS NOT NULL
                    )
                AND ( tt.SaleDtDerived IS NOT NULL
                      OR tt.RecordingDtDerived IS NOT NULL
                    )
    ) cte

OPTION ( RECOMPILE, OPTIMIZE FOR ( @TransFilter_Fake = 0 ) );


link text