CODEWITHSUNDEEP
sql-serverperformancet-sqlsqldatetime
Pavel Voronin
Pavel Voronin

Reputation: 14015

Why does date type hugely affect performance of query execution?

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.

Upvotes: 2

Views: 367

Answers (1)

Norman H
Norman H

Reputation: 2262

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.

Upvotes: 1

Related Questions