Partitioned View filter doesnt work correct

Question

I have next partition view: vwSalesOutH. It consists of several tables named "SalesOutH" in different databases.

When I use the next query, it works fine, searching is by one table vwSalesOutH:

select vwSalesOutH.* 
from vwSalesOutH
inner  join DatesImport on vwSalesOutH.DBTypeId=DatesImport.DBTypeId and
                          vwSalesOutH.CustId=DatesImport.CustId
where vwSalesOutH.DBTypeId=2 and
vwSalesOutH.Date>='2017-01-01 00:00:00' and
vwSalesOutH.Date<='2017-01-25 00:00:00';

Statistics IO:

   (6179 row(s) affected)
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOutH'. Scan count 1, logical reads 452, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DatesImport'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

When I use the next query, in which in block "where" used the same dates, I have problem - searching is by all tables in partition view:

select vwSalesOutH.* 
from vwSalesOutH
inner  join DatesImport on vwSalesOutH.DBTypeId=DatesImport.DBTypeId and
                          vwSalesOutH.CustId=DatesImport.CustId
where vwSalesOutH.DBTypeId=2 and
vwSalesOutH.Date>=DatesImport.Fact_Start and
vwSalesOutH.Date<=DatesImport.Fact_End;

Statistics IO:

(6179 row(s) affected)
Table 'DatesImport'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOutH'. Scan count 5, logical reads 452, physical reads 0, read-ahead reads 43, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOutH'. Scan count 5, logical reads 584, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOutH'. Scan count 5, logical reads 55717, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOutH'. Scan count 5, logical reads 12752, physical reads 0, read-ahead reads 3, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOutH'. Scan count 5, logical reads 11834, physical reads 0, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOutH'. Scan count 5, logical reads 11097, physical reads 0, read-ahead reads 10, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOutH'. Scan count 5, logical reads 51, physical reads 0, read-ahead reads 13, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Can you give me advices to fix the second query?

Answer 1

i have run into the same problem, the issue seems to be that unless the partitioning value is provided as a constant (not joined, not variable), then the optimalizer is not able to identify the partition to be used and instead scans the all tables

Even when using Option (Recompile), filtering on joined columns does not go to single table seek.

Using a variable with Option (recompile) goes to single table seek.