1

WH.dbo.vw_FactTable is a huge table 100mill rows with good indexing on DateKey

This runs almost instantaneously:

1.

SELECT  COUNT(*)
FROM    WH.dbo.vw_FactTable bi
WHERE   DateKey >= 20130101 AND
        DateKey <= 20130110

This takes two minutes:

2.

SELECT  COUNT(*)
FROM    WH.dbo.vw_FactTable bi
WHERE   CONVERT(DATETIME,CONVERT(CHAR(8),DateKey,112)) >= '01 JAN 2013' AND
        CONVERT(DATETIME,CONVERT(CHAR(8),DateKey,112)) <= '10 JAN 2013'

Why so different?

The execution plans are quite different - 1 seems to choose Merge Join:

enter image description here

Whereas 2 goes for a Hash Match:

enter image description here

Is there an obvious way to tune query 2?

whytheq
  • 34,466
  • 65
  • 172
  • 267
  • 5
    By doing all those conversions on your constraint you are losing the ability to use the index on the DateKey column. You are better off converting the parameters vs the table data. – bmorgan21 Aug 30 '13 at 16:59
  • Don't do the date conversions on the database columns (as it has to do them for every row). Do the date conversion on your literal. – Jimmy Aug 30 '13 at 16:59
  • 5
    Query 1 is [sargable](http://en.wikipedia.org/wiki/Sargable) and uses index seeks. Query 2 isn't and gives you scans. You can't tune query 2 to give you the same plan as 1. In some situations creating a computed column and indexing that would allow an index to be used but this is not a deterministic conversion for a computed column anyway. – Martin Smith Aug 30 '13 at 17:10
  • @MartinSmith hi Martin - "sargable" ? Do you agree with bmorgan21 ? – whytheq Aug 30 '13 at 17:12
  • I added a wiki link for "sargable" and, yes, it is the same thing as @bmorgan21 says. – Martin Smith Aug 30 '13 at 17:18
  • To elaborate on @bmorgan21's answer, using functions instead of fields in the where clause will always slow down your query. It's not just date functions either. Upper() and lower() are very slow. – Dan Bracuk Aug 30 '13 at 17:24
  • @DanBracuk - if a conversion is required, a conversion is required! ...where do I apply the function to get a more efficient query? Better to fill a temp table with everything converted first? – whytheq Aug 31 '13 at 09:12
  • @MartinSmith - excellent - new word + very important lesson - wonder how it's pronounced - the g-sound could be similar to saag or sargent ? (probably the latter) – whytheq Aug 31 '13 at 09:20

2 Answers2

2

In the second query, as you are using the function in the where clause(Convert), outcome of this function is evaluated at the run time, and the SQL Server Query engine has to scan the whole table to get necessary data.

Sonam
  • 3,406
  • 1
  • 12
  • 24
2

People have hit it on the head already in comments, restructuring the field neuters the index, this would perform similarly to your first query:

SELECT  COUNT(*)
FROM    WH.dbo.vw_FactTable bi
WHERE   DateKey BETWEEN CAST(CAST(REPLACE(CAST('01 JAN 2013' AS DATE),'-','')AS VARCHAR(12))AS INT)
                    AND CAST(CAST(REPLACE(CAST('10 JAN 2013' AS DATE),'-','')AS VARCHAR(12))AS INT)
Hart CO
  • 34,064
  • 6
  • 48
  • 63