Sql-server – Predicate pushdown on view to optimize query

optimizationsql server

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

EDIT

:

I encountered this other scenario where the solution was unintuitive.

table: 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)

query:

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):
enter image description here

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):

enter image description here

Running SQL Server 2016 SP1

Best Answer

I would suggest:

  • change the unrecommended shorthand getdate()-7 to the proper and explicit CONVERT(datetime2(2), DATEADD(DAY, -7, GETDATE()))
  • avoiding SELECT * everywhere, especially in the view
  • change your predicate to an open-ended range (>= July 1 and < August 1)
  • explicitly convert literals to avoid implicit conversions
  • avoid regional, unsafe formats like yyyy-mm-dd

So:

WHERE SpendDate >= CONVERT(datetime2(2), '20180601')
  AND SpendDate <  CONVERT(datetime2(2), '20180701');

Not only does this guard against both implicit conversions and ydm interpretation, it is much easier to find the beginning of the next month than the end of the current one.

Related Question