Be Careful Where You Use AT TIME ZONE In Your SQL Server Queries

Universal


Databases really do make you pay dearly for mistakes, and new linguistic functionality is not implemented with performance in mind.

I’ve written before about how to approach date math in where clauses: Where To Do Date Math In Your Where Clause

And it turns out that this lesson is brutally true if you need to pass time zones around, too.

Wherely


To make things as easy as possible on our query, let’s create an index up front:

CREATE INDEX c ON dbo.Comments(CreationDate);

Now let’s pretend that we need to query the Comments table with some time zone consideration:

DECLARE @d datetime = '20131201';

SELECT 
    COUNT_BIG(*) AS records
FROM dbo.Comments AS c
WHERE c.CreationDate AT TIME ZONE 'UTC+12' >= @d
OPTION(RECOMPILE);
GO

We’re going to wait a very long time. Assuming that a minute is a long time to you. It is to me, because every minute I spend waiting here is a minute I’m not doing something else I’d rather be doing.

SQL Server Query Plan
un momento por favor

Whyly


We made a bad decision, and that bad decision was to to try to convert every column value to some new time zone, and then compare it to a value that we could have very easily computed once on the fly and compared to the column.

DECLARE @d datetime = '20131201';

SELECT    
    COUNT_BIG(*) AS records
FROM dbo.Comments AS c
WHERE c.CreationDate >= DATEADD(HOUR, 1, @d) AT TIME ZONE 'UTC-11'
OPTION(RECOMPILE);
GO

When we make a good decision, the query runs in much shorter order.

SQL Server Query Plan
ooh la la

One may even accuse us of cheating time itself when looking at how much more quickly this runs without the aid of parallelism.

And that’s really the message in all of these posts, isn’t it? It’s not about left side vs. right side of the comparison. It’s about what we decide to make an expression out of.

When it’s data — the stuff that we physically store — converting that turns into a circus.

When it’s a value — something that we express once via a parameter or variable or some line of code — it’s far less of a burden.

Thanks for reading!

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.



One thought on “Be Careful Where You Use AT TIME ZONE In Your SQL Server Queries

Comments are closed.