0

I am comparing two queries. One query uses 

WHERE CAST(datetime_column AS DATE) = CAST('2019-06-24 00:00:00.000' AS DATE),

and the other does 

WHERE datetime_column >= '2019-06-24' AND datetime_column < '2019-06-25';

The first one does an index scan and runs slowly (estimate number of rows to be read = 560 million), the second one returns instantly and does an index seek (estimate number of rows to be read = 848).

Since they are both running on the same range, and the slower one does not use a function on the column, how come it is slower?

I have tried to run the slower query with a smaller range and it did run faster, but I can't rely on a smaller range.

Dale K
  • 25,246
  • 15
  • 42
  • 71
kati novikov
  • 71
  • 7
  • 1
    Casting is a function - so of course it will be slower. Any transformation of your raw data prevents the use of indexes and should be avoided if at all possible. – Dale K Jul 24 '19 at 09:15
  • 1
    The first query's condition is not [sargable](https://stackoverflow.com/q/799584/4137916). – Jeroen Mostert Jul 24 '19 at 09:16
  • 1
    Your title appears to be the reverse of your question though, your title says the range is slower than the cast, whereas your question says the cast is slower than the range. – Dale K Jul 24 '19 at 09:16
  • 2
    My understanding @JeroenMostert, is that it is SARGable, but not recommended: https://dba.stackexchange.com/a/34052/140734 – Thom A Jul 24 '19 at 09:25
  • @Larnu: right -- I knew some conversions are actually sargable, but I didn't think using two of them and comparing would be. But I'm wrong; the optimizer will convert the whole thing into a dynamic range query (tested on both SQL 2014 and 2017). It's still the case that the perf characteristics of this will be different to a simple range comparison (as explained in the answer). – Jeroen Mostert Jul 24 '19 at 09:39
  • The `CAST` on the string literal isn't actually a problem here, it's the `CAST` on the column `datetime_column`. Something like `WHERE CAST(datetime_column AS DATE) = '20190624'` *should* give the same query plan as the OP's initial query (as the string literal will be implicitly converted to a `date` anyway). – Thom A Jul 24 '19 at 09:43
  • To find the answer you examine the execution plans. Anything else is guessing - and all of the is dependent on knowledge of the DDL and the complete query which you did not provide. – SMor Jul 24 '19 at 12:22

0 Answers0