Querying by relative dates and times in SQL

Querying by relative dates and times in SQL

Wanting to search for database records within a timeframe is relatively common, particularly if we want to examine error logs.

How we search can make a large difference. The two queries below search for the same set of records in the same table but the first query has the potential for far worse performance

-- Every event in the last day (DATEDIFF)
SELECT * FROM Event e
WHERE DATEDIFF(DAY, e.Timestamp, GETUTCDATE()) <= 1
-- Every event in the last day (DATEADD)
SELECT * FROM Event e
WHERE e.Timestamp BETWEEN DATEADD(DAY, -1, GETUTCDATE()) AND GETUTCDATE()

Because DATEDIFF uses a column of the table as a parameter the database cannot use any index that uses that column which has the potential to massively impact query performance.

The DATEADD doesn't use the column as a parameter of a function so the database can take advantage of any existing index.

This can result in a massive difference in the performance of a query.

A query that allows the database engine to use an index is Sargable (Search ARGument ABLE).

A query is most likely to be non-sargable if the WHERE clause contains a function operating on a column. Although a similar function in the ORDER BY, GROUP BY or HAVING clauses can also affect a query.

As a general rule, if you can at all, avoid using functions which operate in columns in clauses other than SELECT.


Share Tweet Send
0 Comments
Loading...
You've successfully subscribed to SimonReynolds.ie
Great! Next, complete checkout for full access to SimonReynolds.ie
Welcome back! You've successfully signed in
Success! Your account is fully activated, you now have access to all content.