Hash join switch build table (statistics are not helping with estimates)
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*/
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 ) );