CODEWITHSUNDEEP
sql-serverperformanceoptimization
user1216379
user1216379

Reputation: 21

Performance comparison for date range query methods

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

Upvotes: 2

Views: 360

Answers (3)

Cade Roux
Cade Roux

Reputation: 89661

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).

Upvotes: 1

anon
anon

Reputation:

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

Upvotes: 2

Brandon leach
Brandon leach

Reputation: 130

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

Upvotes: 2

Related Questions