Need help to understand tuning up a slow SQL server query [closed]

Question

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

SELECT [Id]
      ,[AboutMe]
      ,[Age]
      ,[CreationDate]
      ,[DisplayName]
      ,[DownVotes]
      ,[EmailHash]
      ,[LastAccessDate]
      ,[Location]
      ,[Reputation]
      ,[UpVotes]
      ,[Views]
      ,[WebsiteUrl]
      ,[AccountId]
  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

Answer 1

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

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:

CREATE NONCLUSTERED INDEX IX_LastAccessDate 
ON dbo.Users (LastAccessDate)
INCLUDE (CreationDate, DisplayName);

CREATE NONCLUSTERED INDEX IX_DisplayName
ON dbo.Users (DisplayName);

Now I get this execution plan:

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

Answer 2

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.

Answer 3

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.