1

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

  • @AaronBertrand - source column is datetime2(2). The fix is to just explicitly cast the predicate values to datetime2(2). SELECT <columns> from [dbo].[vwFactCost] WHERE SpendDate >= CAST('2018-06-01' AS DATETIME2(2)) AND SpendDate <= CAST('2018-06-30' AS DATETIME2(2)) I'll make sure to use this explicit casting going forward, since I didn't realize it could make such a huge difference. If you want to copy your comment to an answer, I can select it. The Date column is datetime2(2) but only has date values, so yes it has the wrong data type. – Gabe Jul 12 '18 at 15:15
3

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.

| improve this answer | |
  • could also a columnstore index improve performance for a range of rows? in this case for WHERE f.date ? – Eduardo Pivaral Jul 12 '18 at 15:53
  • @EduardoPivaral Maybe, I don’t know, depends on the workload, data size, etc. A lot more to consider when implementing ColumnStore other than “make this one query a bit faster.” – Aaron Bertrand Jul 12 '18 at 16:56
  • @AaronBertrand Thanks! I edited my post with another related example with exec plans. The solution was to have the predicate in the view to be datetime on the date field and have the outer query that uses the view to use Date. Hopefully my wording makes sense. – Gabe Jul 17 '18 at 21:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.