Performance comparison for date range query methods

Question

Of the two queries below, which one is faster? The table contains more than 100M records. So I just want to know whether using ISNULL in the where clause is the same as first assigning a value to the variable and only using the variable in the where clause?

declare @dt datetime
select COUNT(*) from pandl 
  where PaymentDate >= ISNULL(@dt, convert(nvarchar(10),getdate(), 121))

select @dt = ISNULL(@dt, convert(nvarchar(10),getdate(), 121))
select COUNT(*) from pandl 
  where PaymentDate >= @dt

Have you run them both to see which is faster? Have you used SQL Profiler to review the execution plans for each query? — Pondlife, Feb 17 '12 at 13:54
How many different values of "now" do you want to use? The second query captures the current date/time once, if needed at all, and then searches. — HABO, Feb 17 '12 at 14:04
@user92546 it's a good point, but just to add some info, even if `getdate()` is evaluated multiple times over the course of the first query (I would have to run some tests to verify), since the convert rounds it to the day, it would only really matter if the query started before midnight and was still processing (and assigning new values to `getdate()`) after midnight. I totally agree with you that assigning once is better, just wanted to clarify when it can be really bad compared to just "not best." — Aaron Bertrand, Feb 17 '12 at 14:18
Try them both and look at the [execution plans](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan). AFAIK both should be identical in your example. — Justin, Feb 17 '12 at 14:21

score 2 · Answer 1 · answered Feb 17 '12 at 13:57

2

Second one will be better. Calling functions from your where clause will make the query optimizer use scans instead of seeks. In other words you'll get a less than ideal execution plan

answered Feb 17 '12 at 13:57

Brandon leach

130
4

2

Calling functions *on the values you're checking* from the `where` clause definitely causes a scan. But i was under the impression that SQL Server knows enough to call the function once if the return value won't change over the life of the call. – cHao Feb 17 '12 at 14:06
@cHao yes, this is true, neither version in the question will cause scans. I was just giving Brandon his first +1 because I agreed with his first sentence. :-) – Aaron Bertrand Feb 17 '12 at 14:09

score 2 · Answer 2 · answered Feb 17 '12 at 14:07

I would suggest a slightly different approach, avoiding the slightly more expensive conversion to a string, and using a slightly smaller data type to store the date (since you don't need sub-minute granularity if you only care about day boundaries):

DECLARE @dt SMALLDATETIME;
SET @dt = DATEADD(DAY, DATEDIFF(DAY, '19000101', CURRENT_TIMESTAMP), '19000101'); --*

SELECT COUNT(*) FROM dbo.pandl 
  WHERE PaymentDate >= @dt;

If you're going to continue using the conversion to a string, use CHAR(10) - I don't think style 121 is going to produce any date formats that require Unicode support.

* yes you can do this without the outer DATEADD but not with new types like DATE

score 1 · Answer 3 · edited Jan 18 '21 at 12:30

FWIW, if you don't have an index, it isn't going to affect the plan:

https://data.stackexchange.com/stackoverflow/query/61684/http-stackoverflow-com-questions-9329461-ms-sql-query-performance

Run in text mode, check show execution plan.

I figured one of these date columns in StackExchange would have an index, but I didn't find one.

Other than that, I am with the other answers on this - don't use nvarchar unnecessarily, don't convert dates to strings for no good reason, don't use functions which can be evaluated more readably outside the query inside a query (and don't use functions in a query at all if you don't need to).

Performance comparison for date range query methods

3 Answers3