tsql - Not Getting Partition Elimination on a Foreign Key Join in SQL Server -
i have rather large fact table in sql server partitioned foreign key date dimension. foreign key constraint both enabled , trusted. when add clause:
"f_clinicinvoicetransaction".servicedatekey>=40908 , "f_clinicinvoicetransaction".servicedatekey<42247 i partition elimination. when join on servicedatekey , filter on date range such:
"d_calendar"."calendarkey" ="f_clinicinvoicetransaction"."servicedatekey" , "d_calendar".startdt>='2012-01-01' , "d_calendar".startdt<'2015-10-01' the partition elimination goes away. there way partition elimination based on join or stuck filtering explicitly on values in fact table?
it easier answer these questions when give more details -- try , answer best can:
perform sub-query 2nd filter ("d_calendar".startdt>='2012-01-01' , "d_calendar".startdt<'2015-10-01' ) , min , max values of servicedatekey.
use min , max values of servicedatekey perform full query, has min , max values can partition elimination. using values in clause first query does.
while seems doing these 2 steps slower case partition elimination give faster results. esp. big data sets.
Comments
Post a Comment