2

I have the query of the following form:

DECLARE @date DATETIME = <some_date>

SELECT ....
FROM a
WHERE <some_conditions> AND a.LastEvent < @date

LastEvent column is of type DATE.

Query works pretty fast. But if I change @date type to DATE query execution seems not going to ever finish. If I comment out the condition 

AND a.LastEvent < @date

query works fast again.

I'm surprised how slight type change of the parameter can affect the performance but I have no idea why this happens.

Liath
  • 9,913
  • 9
  • 51
  • 81
Pavel Voronin
  • 13,503
  • 7
  • 71
  • 137
  • 4
    Compare the execution plans. – Aaron Bertrand Jan 15 '14 at 15:29
  • i read something that said date is just a datetime with the time truncated, or zero'd. might as well use datetime as the column type. I think the compiler does funny stuff with "date", whereas datetime is fairly straight forward. – Rich Jan 15 '14 at 15:33
  • are you clearing the buffer and cache before each execution to make sure it is not re using the previously stored execution plan? What indexes you have on Table? can you post Index Type and Column Order in indexes. you also need to post what is "" Implicit Datatype conversion happen on left side when data type is not matching with the Right side of WHER condition. in the second run you said you are making @date datatype to DATE and it is same datatype as a.LastEvent. – Anup Shah Jan 15 '14 at 15:44
  • add `option (with recompile)` on the end, and compare execution plans. What is data type of `a.LastEvent` ? –  Jan 15 '14 at 19:28
  • @SaUce LastEvent is DATE, recomilatiocn changes nothing – Pavel Voronin Jan 16 '14 at 06:32
  • @AnupShah Yes, types become the same and in this configuration query works slower. Conditons are just other fields checks. – Pavel Voronin Jan 16 '14 at 06:35
  • @AaronBertrand Frankly speaking I have no idea how to veiw the real (not estimated) execution plan of not completed query. – Pavel Voronin Jan 16 '14 at 06:55

1 Answers1

1

Likely this has to do with the fact that the comparison requires an internal cast for comparison and the evaluation of the two types is a much more expensive operation due to the nature of how each type is stored.

The other comments to your question all add valuable points about running the tests to ensure you are actually measuring what you think you are measuring.

Ideally, as much as possible you want to be matching your types as closely as possible because conversion operations among types are not among the least expensive things to do.

Additionally, the indices are based off of the stored type as well, so THAT may actually be where your bottleneck is for this query and NOT in the type conversion.

The query execution plan should help you infer between the two opinions.

Norman H
  • 2,248
  • 24
  • 27