select subqueries with join

Question

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?

Answer 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