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'
Running SQL Server 2016 SP1. We'll be on 2017 in a couple months.