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'
I encountered this other scenario where the solution was unintuitive.
FactRegistration with 300m records, 30 columns,
RegDate is date column
below is view definition:
SELECT <columns> FROM FactRegistration fr WHERE FR.RegDate < CAST(DATEADD(DAY,-7,GETDATE()) AS DATE)
SELECT <columns> INTO #tmp FROM edw.dbo.vwDemo_slow fr WHERE fr.RegDate >= CAST('20140501' AS DATE) AND fr.RegDate <= CAST('20140531' AS DATE)
Results in this plan (https://gist.github.com/gflores1023/f0f0089315841d21ab072837cf12145d):
If I change the view definition to use this WHERE clause instead:
WHERE FR.RegDate < CAST(CAST(DATEADD(DAY,-7,GETDATE()) AS DATE) AS DATETIME)
I get a much better plan (https://gist.github.com/gflores1023/e3904609c98babbbbc646eaec76ebba4):
Running SQL Server 2016 SP1