Predicate pushdown on view to optimize query?

by Gabe   Last Updated July 11, 2018 23:06 PM

I have a view that references a fact table Cost with 300M records. That view also has a couple joins. The fact table has a clustered index on Date and the view definition is something like this:

select * 
from FactCost f
INNER JOIN SomeOtherTable b on a.id = f.id
where f.Date < getdate()-7

If I query the view like below, the optimizer first processes the predicate within the view and then the date in my query. So it actually reads 300M records and returns only 1 million. I can't figure out what kind of workaround I can try to optimize this.

SELECT
              *
    FROM [dbo].vwFactCost
    WHERE Date >= '2011-07-01'
        AND Date <= '2011-07-31'

see below enter image description here

Running SQL Server 2016 SP1. We'll be on 2017 in a couple months.



Related Questions