0

I am using sub queries using joins. For 11946 rows, the query is taking 18 seconds.

Select CONVERT(VARCHAR(10), [Date], 101) as schedDate, ClientName, 
Coalesce(Error1, '')  
+ CASE WHEN Error1 is not null and Error2 is not null THEN ' | ' else '' END  
+ Coalesce(Error2, '')  
+ CASE WHEN Error2 is not null and Error3 is not null THEN ' | ' else '' END  
+ Coalesce(Error3, '') as ErrorList  
From(  
select [Date], ClientName,  
CASE WHEN SchedPayor <> EligPayor THEN 'Payor No Match' END as Error1,   
CASE WHEN EligPayor IS NULL THEN 'No Eligibility' END as Error2,  
CASE WHEN StaffName IS NULL THEN 'Invalid Staff' END as Error3  
From  
(Select 
       [Date],  
       s.clientID,  
       c.DisplayName [ClientName],  
       si.PayorID [SchedPayor],  
       ce.PayorID [EligPayor],  
       stf.DisplayName [StaffName]  
From Schedule s  
       INNER JOIN ScheduleItem si ON s.ScheduleID = si.ScheduleID and s.AccountID = si.AccountID and si.Owner = 1
       INNER JOIN Client c ON s.ClientID = c.ClientID and s.AccountID = c.AccountID     
       OUTER APPLY                                                                 (Select ClientID, PayorID From ClientEligibility ce               Where s.ClientID = ce.ClientID and s.[Date] BETWEEN ce.StartDate and ce.EndDate) as ce 
       LEFT JOIN Staff stf ON s.StaffID = stf.StaffID and s.AccountID = stf.AccountID   
WHERE
       s.accountID = 1) as a) as b

Is there any other way to write query to optimize performance?

  • Please post (and link to) the XML for the ACTUAL execution plan - you can post it here - pastebin.com – Scott Hodgin Mar 13 '17 at 19:01
1

This is just formatting to try and understand

So far that OUTER APPLY is what I would look at

Select CONVERT(VARCHAR(10), [Date], 101) as schedDate, ClientName, 
       Coalesce(Error1, '')  
       + CASE WHEN Error1 is not null and Error2 is not null THEN ' | ' else '' END  
       + Coalesce(Error2, '')  
       + CASE WHEN Error2 is not null and Error3 is not null THEN ' | ' else '' END  
       + Coalesce(Error3, '') as ErrorList  
From ( select [Date], ClientName,  
              CASE WHEN SchedPayor <> EligPayor THEN 'Payor No Match' END as Error1,   
              CASE WHEN EligPayor IS NULL THEN 'No Eligibility' END       as Error2,  
              CASE WHEN StaffName IS NULL THEN 'Invalid Staff' END        as Error3  
              From  
             ( Select s.[Date],  
                      s.clientID,  
                      c.DisplayName   [ClientName],  
                      si.PayorID      [SchedPayor],  
                      ce.PayorID      [EligPayor],  
                      stf.DisplayName [StaffName]  
                 From Schedule s  
                 JOIN ScheduleItem si 
                   ON s.ScheduleID = si.ScheduleID 
                  and s.AccountID  = si.AccountID  
                  and s.accountID  = 1
                  and si.Owner = 1
                 JOIN Client c 
                   ON s.ClientID  = c.ClientID 
                  and s.AccountID = c.AccountID     
                OUTER APPLY 
                      ( Select ClientID, PayorID 
                          From ClientEligibility ce  
                         Where s.ClientID = ce.ClientID 
                           and s.[Date] BETWEEN ce.StartDate and ce.EndDate 
                      ) as ce 
                 LEFT JOIN Staff stf 
                   ON s.StaffID   = stf.StaffID 
                  and s.AccountID = stf.AccountID                    
             ) as a
     ) as b

My guess is this may get you what you need

Select CONVERT(VARCHAR(10), [Date], 101) as schedDate, ClientName, 
       Coalesce(Error1, '')  
       + CASE WHEN Error1 is not null and Error2 is not null THEN ' | ' else '' END  
       + Coalesce(Error2, '')  
       + CASE WHEN Error2 is not null and Error3 is not null THEN ' | ' else '' END  
       + Coalesce(Error3, '') as ErrorList  
From ( select [Date], ClientName,  
              CASE WHEN SchedPayor <> EligPayor THEN 'Payor No Match' END as Error1,   
              CASE WHEN EligPayor IS NULL THEN 'No Eligibility' END       as Error2,  
              CASE WHEN StaffName IS NULL THEN 'Invalid Staff' END        as Error3  
              From  
             ( Select s.[Date],  
                      s.clientID,  
                      c.DisplayName   [ClientName],  
                      si.PayorID      [SchedPayor],  
                      ce.PayorID      [EligPayor],  
                      stf.DisplayName [StaffName]  
                 From Schedule s  
                 JOIN ScheduleItem si 
                   ON s.ScheduleID = si.ScheduleID 
                  and s.AccountID  = si.AccountID  
                  and s.accountID  = 1
                  and si.Owner     = 1
                 JOIN Client c 
                   ON s.ClientID  = c.ClientID 
                  and s.AccountID = c.AccountID     
                 left join ClientEligibility ce  
                   on s.ClientID = ce.ClientID 
                  and s.[Date] BETWEEN ce.StartDate and ce.EndDate 
                 LEFT JOIN Staff stf 
                   ON s.StaffID   = stf.StaffID 
                  and s.AccountID = stf.AccountID                    
             ) as a
     ) as b
  • Rather than outer apply and one sub query this is using left join.Query is now taking 15 seconds. – somu Mar 13 '17 at 19:28
  • Maybe you are not going to get much better? I don't have any other ideas. – paparazzo Mar 13 '17 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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