Simplifying Query to make it run faster

Question

I have this extremely long winded query below. I am having issues with running it as it takes forever and keeps timing out on me:

    with t as 
   (
select a.ID, 
       a.Date_Reported AS [Date Sent],  
       b.Date_Received AS [Date Returned], 

(datediff(dd, a.date_reported, b.date_received) 
      + CASE WHEN Datepart(dw, b.date_received) = 7 THEN 1 ELSE 0 END 
       - (Datediff(wk, a.date_reported, b.date_received) * 2 ) 
       - CASE WHEN Datepart(dw, b.date_received) = 1 THEN 1 ELSE 0 END + 
       - CASE WHEN Datepart(dw, b.date_received) = 1 THEN 1 ELSE 0 
       END) AS [Overall_Time_Spent]

from [Transactions_External] a 
join [Transactions] b on b.id like '%'+a.id+'%'
where a.customer = 'AA'
AND a.Date_Reported >= DATEADD(MONTH,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0))
AND a.Date_Reported <  DATEADD(d,1,EOMONTH(GETDATE(),-1)) 
AND a.ID IS NOT NULL
AND a.ID <> ''
AND b.ID not like '%_H'

    )


    select V.*
    from 
    (
        select 
           sum(case when Overall_Time_Spent < 0 then 1 else 0 end) as Errors,
           sum(case when Overall_Time_Spent between 0 and 3 then 1 else 0 end) as _0_3_days,
           sum(case when Overall_Time_Spent = 4 then 1 else 0 end) as _4_days,
           sum(case when Overall_Time_Spent = 5 then 1 else 0 end) as _5_days,
           sum(case when Overall_Time_Spent between 6 and 8 then 1 else 0 end) as _6_8_days,
           sum(case when Overall_Time_Spent >= 9 then 1 else 0 end) as more_than_9_days,
           count(Overall_Time_Spent) as Total
    from t

    ) T1
    cross apply 
    ( values 
      ('Count', convert(int, [Errors]), convert(int, [_0_3_days]), convert(int, [_4_days]), convert(int, [_5_days]), convert(int, [_6_8_days]),  convert(int, [more_than_9_days]), convert(int, [Total]))
      )
    v([Time Taken (days)], [Errors], [0-3],[4],[5],[6-8],[9+], [Total])

The query is essentially looking at two tables, joining on id (which is slightly different on either table hence the like on the join) and then finding the difference in two dates to find the overall time spent. Then later on the times are split up into ranges. The query is restricted to last month only.

Any ideas what I can do to make this run faster or change the query about to help it run faster. I think the issue may be in the original select:

datediff(dd, a.date_reported, b.date_received) 
      + CASE WHEN Datepart(dw, b.date_received) = 7 THEN 1 ELSE 0 END 
       - (Datediff(wk, a.date_reported, b.date_received) * 2 ) 
       - CASE WHEN Datepart(dw, b.date_received) = 1 THEN 1 ELSE 0 END + 
       - CASE WHEN Datepart(dw, b.date_received) = 1 THEN 1 ELSE 0 
       END) AS [Overall_Time_Spent]

I may be selecting on all the database rather than last month?

one important thing to note is i am unable to create any tables or split the query up- so i really need to run selects and do it in one query. I am not sure this is possible.

You are doing a ton of casts and case statementns. You can run the query after the FROM statement into a temp table and include only the fields you need. Then run a select from the temp table with all the logic/case, and other operations on only the temp table. This will speed it up to only run the functions on the number of records you need. — Brad, Jun 06 '18 at 13:58
Thanks for your quick response. Unfortunately i am unable to create tables on my software as i do not have permission, hence i need to do selects and run the above as ONE query if possible — Taz, Jun 06 '18 at 14:00
Even local temp tables (they are only in scope during your process you are currently running)? You can try moving all your logic out of the CTE to the below select. — Brad, Jun 06 '18 at 14:05
Why do you use CTE and then subquery instead of two consecutive CTEs? This makes it harder for database engine to produce effective query plan — avb, Jun 06 '18 at 14:12
Sorry what do you mean by CTE? @avb can you give me an example of what you mean — Taz, Jun 06 '18 at 14:21
You have join and where predicates that nonSARGable. When you have a leading wildcard like that you negate the ability to utilize indexes. I get the feeling that the ID column in your Transactions table contains multiple values. Also you should get in the habit of using aliases that mean something instead a and b. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3 — Sean Lange, Jun 06 '18 at 14:53
Thank you but i am still lost in how i can trim or adjust this query. — Taz, Jun 07 '18 at 11:42

score 1 · Answer 1 · answered Jun 06 '18 at 14:05

1

join with like and "%" first is not recommended

join [Transactions] b on b.id like '%'+a.id+'%'

Index will not be used on a.id (if any) and it would require full scan. Maybe try to do an EXPLAIN of your query to see number of row scanned

answered Jun 06 '18 at 14:05

Johann

25
4

Yes, why would you do like on an ID column as well? – Brad Jun 06 '18 at 14:06
also `b.ID not like '%_H'` is not [sargable](https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable) – avb Jun 06 '18 at 14:10
Thank you, i am still confused on how i could make this easier. Sorry but having looked at this query for weeks it is doing my head and taking too long to run. – Taz Jun 06 '18 at 14:19

Simplifying Query to make it run faster

1 Answers1