DECLARE vs. Direct DateTime Conversion Performance Comparison

Question

I am trying to understand if there is any performance difference between declaring a datetime conversion variable and using it versus directly converting the date in a SQL Server query.

Here are two example queries: Query 1: Do DateTime Conversion Every Time

SELECT * 
FROM TestMessages
WHERE CreatedDate > CONVERT(DATETIME, '2023-12-18 00:00:00', 120);

Query 2: DECLARE DateTime Conversion Variable

DECLARE @yourDateString NVARCHAR(19) = '2023-12-18 00:00:00';
DECLARE @ConvertedDate DATETIME = CONVERT(DATETIME, @yourDateString, 120);

SELECT * 
FROM TestMessages
WHERE CreatedDate > @ConvertedDate;

Is there any noticeable performance difference between these two queries? How does declaring a variable for datetime conversion impact the execution time of the query? Additionally, what SQL Server tools or techniques can be used to analyze and measure this performance difference?

I've been experimenting with two different approaches in my SQL Server queries for datetime conversion. In one query, I directly convert the datetime value, and in another, I use DECLARE to create a conversion variable. I expected to observe a potential difference in performance between these two methods, but the results were not as clear as I anticipated. I would like insights on the performance implications of these approaches and any recommended practices for optimizing datetime conversions in SQL Server queries.

When I examine the Execution Plans, there seems to be a difference in the plan, but it's unclear whether it's significant. Additionally, when I look at the statistics, it shows '88 rows affected' for the first query, while it shows '1 rows affected' for the other. The cost is shown as 100% and 0.000s for the first query with '88 of 88 (0%) rows affected.' However, for the second query, the cost is also 100%, but '0.000s 0 of 855 (0%)' rows are affected. Is this discrepancy significant?

Answer 1

With

WHERE CreatedDate > CONVERT(DATETIME, '2023-12-18 00:00:00', 120);

the cardinality estimates will likely be more accurate.

This will improve the chances of getting an appropriate plan and, potentially, memory grant.

For the CreatedDate > @ConvertedDate predicate it will just assume 30% will match unless you also use OPTION (RECOMPILE) to allow it to sniff the variable's value.

For CreatedDate > CONVERT(DATETIME, '2023-12-18 00:00:00', 120) it can look that value up in the histogram and get estimates from there.

CREATE TABLE TestMessages
             (
                          CreatedDate DATETIME INDEX ix_CreatedDate,
                          Filler      CHAR(1000) NULL
             )
INSERT TestMessages
       (CreatedDate)
SELECT DATEADD(HOUR, value, '1990-01-01 00:00:00')
FROM   generate_series(1, 300000) 

WHERE CreatedDate > CONVERT(DATETIME, '2023-12-18 00:00:00', 120);

WHERE CreatedDate > @ConvertedDate

WHERE CreatedDate > @ConvertedDate OPTION (RECOMPILE);

Answer 2

For exploring the performance impact of parameters in queries:

WHERE CreatedDate > @ConvertedDate

When a variable is used in the WHERE clause, the optimizer creates an execution plan for the first parameter value that is passed. For example, when @ConvertedDate='2020-03-10 10:30:00.000' is passed for the first time, then the optimal execution plan is created by the optimizer. This plan might just be the best fit for this value. Next time, for another value of @ConvertedDate, the previous plan is used, which may not be optimal for that value.

In other words, the created plan, which was optimal for the first value, may be sub-optimal for other values.

So, the query may run poorly and slowly. This is known as parameter sniffing. There are several ways to overcome this problem:

Parameter Sniffing

Anyway, the execution plan is similar for both queries:

WHERE CreatedDate > @ConvertedDate

or

WHERE CreatedDate > '2020-03-10 10:30:00.000'

Here is a sample with the execution plans