Distinct sort issue

Arvind 2017-10-16 09:11:21

Hello,

when i execute this query, it takes too much of tempdb usage and executing forever. Last week the same query taken 10mins to execute. I have done stats update on the columns but it seems distinct sort is the issue. Can you assistance me in tuning this query. I do not have a actual execution plan as of now.

-Kevin

SDyckes2 2017-10-17 01:12:52
First thing I spot is the SELECT MAX(AsOfDate) FROM Time.AsOfDate (nolock) is used twice in the WHERE clause. If you could run it once and pass it as a variable, that would decrease the cost by the one query. Additionally, do you really need to scan the entire table (clustered index) to find the MaxDate? Or could you further reduce the number of records you need to check with a where AsOfDate > DATEADD(D,120,GETDATE()), where the date is more recent? That date would depend upon the data stored in the AsOfDate table.

The next thing of note is all the tables are performing Clustered Index Scans. These scans are funneling into Hash Matches. I would consider creating 2 joins to each table currently using a CASE statement in the ON clause. This will allow a nonclustered index to be created for each join condition and allow the optimizer to more accurately estimate the rows and increase the performance of the new query.