For one of our databases the query like below is extremely slow.

Due to security reasons I am not able to share the actual query or plan but just to give an idea how query is written is below

  FROM [StackOverflow2010].[dbo].[Users]
  WHERE DisplayName IN (

 SELECT DisplayName from dbo.Users
 WHERE CAST(LastAccessDate AS DATE) = CAST ('20160814' AS DATE)
 AND CreationDate>= DATEADD (DAY, -30,LastAccessDate)
 AND CreationDate<= LastAccessDate)

In stackoverflow database this does not return any rows but for our existing 6 TBS database its really slow.

CreationDate and LastAccessdate columns are both datetime (10)

and DisplayName is Varchar(50)

Please suggest how can I improve performance if above can be re-written


You can improve things greatly without rewriting the query by just adding indexes, from what I can tell.

Here's the execution plan I get on SQL Server 2019 (note that I changed the date to '20090814' just so it would return some results):

enter image description here

Table 'Users'. Scan count 18, logical reads 15430

 SQL Server Execution Times:
   CPU time = 297 ms,  elapsed time = 94 ms.

This scans the entire Users table once to get the users that meet the date criteria, and then several more times to get the rest of the users that have matching display names.

Not ideal.

The query only takes 94 ms on my machine, mostly because everything is in RAM and the query is running at degrees of parallelism (DOP) 8.

However, adding these two indexes helps the situation a lot:

ON dbo.Users (LastAccessDate)
INCLUDE (CreationDate, DisplayName);

ON dbo.Users (DisplayName);

Now I get this execution plan:

screenshot of execution plan in plan explorer showing index seeks instead of scans

Table 'Users'. Scan count 15, logical reads 153

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

SQL Server is able to produce a seek plan here by using the internal GetRangeThroughConvert function to determine the range of possible datetime values that are equivalent to CAST(LastAccessDate AS DATE) = CAST ('20160814' AS DATE). Essentially, it rewrites the query in the background to match the suggestion made by Mo64.

Note: you are probably better off rewriting to explicitly use that range query, rather than depending on the hidden implicit conversion

You can read more details about this sort of execution plan on Paul White's blog: Dynamic Seeks and Hidden Implicit Conversions

The situation could be improved even further if you eliminate the key lookups - either by selecting less columns, or including all the necessary columns in the first index (on LastAccessDate).

I realize this is kind of a toy example, but hopefully it illustrates the general solution that can be applied to your real situation:

  • Add an index that will allow SQL Server to jump to the right date in the subquery
  • potentially rewrite the CAST from datetime to date to be two inequality conditions. In other words, replaced this:
WHERE CAST(LastAccessDate AS DATE) = CAST ('20160814' AS DATE)

With this:

WHERE LastAccessDate >= '20160814' AND LastAccessDate < '20160815' 
  • Add an index that will allow SQL Server to jump to the matching varchar(50) column values

You will always have a full table/index scan (no seek) anyway.

There is no way for SQL to "guess" on "CreationDate<= LastAccessDate" so it will have to read every single row to compare both date.

If your table is big, it will take time.

You could try with a Computed column on "CreationDate<= LastAccessDate", this could give SQL some stats.

If you put an index on top of it, then you may see huge improvement.

  • You're right about not being able to get a seek on that specific condition, but you can narrow things down (potentially by a lot) by getting a seek on LastAccessDate. – Josh Darnell Jul 2 '20 at 13:41
  • Agree... the less page you read, the fastest it will be. I did have a huge improvment with a computed column for a similar case. SQL is bright enough to use it even if you do not include that column in you query and ended up with a seek in this index that contained the computed column and the other required field. If you can alter the table, I guess it's the option that could give the best performance improvment. (specially if there is a lot of row in the table) – Dominique Boucher Jul 2 '20 at 13:43
  • 1
    Oh for sure, I think the computed column is a great idea with a lot of potential. – Josh Darnell Jul 2 '20 at 13:50
  • @DominiqueBoucher: Thanks and do you think "IN" part of query can use temptable here? – Newbie-DBA Jul 3 '20 at 17:33
  • I'm not sure I understand your question. Do you mean you want to populate a temp table first (with the content of the subquery) and then use that temp table into your main query ? – Dominique Boucher Jul 3 '20 at 17:43

You will get a table scan because you have a CAST() function on your date column.
I would make sure that the date column is indexed and then change the where clause to:

WHERE LastAccessDate >= '20160813' AND LastAccessDate < '20160815' 

...that way you should get an index seek.

Probably even better if you turn the hard coded dates into datetime variables and either pass the values as SP parameters or assign them as local variables - depends on your coding style etc.


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