CODEWITHSUNDEEP
sql-server
Taz
Taz

Reputation: 169

Simplifying Query to make it run faster

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.

Upvotes: 0

Views: 59

Answers (1)

Johann
Johann

Reputation: 25

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

Upvotes: 1

Related Questions