2

I have the following query in SQL server:

declare @timestamp_start date = getdate() - 1
declare @timestamp_end date = getdate()

SELECT  
    my_date = a.itemTimestamp
FROM
    my_table a with(nolock)
WHERE 
    (@timestamp_start is null OR a.itemTimestamp >= @timestamp_start)
    AND (@timestamp_end is null OR a.itemTimestamp < @timestamp_end)

It's extremely slow (90s for < 2000 records), because the execution plan uses the clustered index of the PK column for 'my_table':

enter image description here

Why is this happening and how can I optimize the query performance?

5

What is the datatype of itemTimestamp? Is that column indexed? Is the column nullable?

Assuming the answers are datetime, Yes, and No respectively you might consider

WHERE  a.itemTimestamp >= isnull(@timestamp_start, '17530101')
       AND a.itemTimestamp < isnull(@timestamp_end, '99991231') 

If you were to change to datetime2 and actually store dates before 1753 you would need to change the lower bound condition to 00010101. This also won't bring back dates matching the higher bound of 9999-12-31 - but in the real world this is unlikely to cause a problem.

|improve this answer|||||
1

Is my_table.itemTimestamp an actual DATE datatype or DATETIME? Seems an odd column name for a datatype that has no time component ;-). If it is a DATETIME, then step 1 is to use the proper datatype for the variable. But, if it truly is a DATE column, then try the following query. It breaks out the 4 permutations so that SQL Server can have simple, concise queries instead of one do-everything query with a bunch of ORs that often complicate query plans.

DECLARE @timestamp_start DATE = GETDATE() - 1;
DECLARE @timestamp_end DATE = GETDATE();

IF (@timestamp_start IS NULL AND @timestamp_end IS NULL)
BEGIN
  SELECT   a.itemTimestamp AS [my_date]
  FROM     my_table a WITH (NOLOCK);
END;
ELSE
BEGIN
  IF (@timestamp_start IS NOT NULL AND @timestamp_end IS NOT NULL)
  BEGIN
    SELECT   a.itemTimestamp AS [my_date]
    FROM     my_table a WITH (NOLOCK)
    WHERE    a.itemTimestamp >= @timestamp_start
    AND      a.itemTimestamp < @timestamp_end;
  END;
  ELSE
  BEGIN
    IF (@timestamp_start IS NULL)
    BEGIN
      SELECT   a.itemTimestamp AS [my_date]
      FROM     my_table a WITH (NOLOCK)
      WHERE    a.itemTimestamp < @timestamp_end;
    END;
    ELSE
    BEGIN
      SELECT   a.itemTimestamp AS [my_date]
      FROM     my_table a WITH (NOLOCK)
      WHERE    a.itemTimestamp >= @timestamp_start;
    END;
  END;
END;
|improve this answer|||||
0

Just to play devils advocate (in some circles this is frowned upon), if you a SURE you want it to use that index and it will improve things, add an index hint... just remember you put it there if you make changes to indexes or queries in the future. Add: WITH (INDEX (###YOUR INDEX NAME HERE##))

declare @timestamp_start date = getdate() - 1
declare @timestamp_end date = getdate()

SELECT  
    my_date = a.itemTimestamp
FROM
    my_table a WITH (INDEX (###YOUR INDEX NAME HERE##))
WHERE 
    (@timestamp_start is null OR a.itemTimestamp >= @timestamp_start)
    AND (@timestamp_end is null OR a.itemTimestamp < @timestamp_end)
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.