Avoid same Table Join multiple times

Question

I have person table and summarydispense table. I am joining person to summarydispense multiple times to fetch various information from employee and the query is taking forever to load, is there any alternative instead of repeating the same table join multiple times, below is the test data and the query, is there anyway to rewrite the query

Create table #SummaryDispense
(
[DispensePharmacyUserID] varchar(255),
[PendVerifyPharmacyUserID] varchar(255),
[CanceledPendVerifyPharmacyUserID] varchar(255),
[VerifyPharmacyUserID] varchar(255)
)

insert into #SummaryDispense values('1','2','3','4')
insert into #SummaryDispense values('10','20','30','40')
insert into #SummaryDispense values('100','200','300','400')

Create table #Employee
(
UserID varchar(255),
UserName varchar(255)
)

Insert into #Employee values('1','A1')
Insert into #Employee values('2','A2')
Insert into #Employee values('3','A3')
Insert into #Employee values('4','A4')
Insert into #Employee values('10','A10')
Insert into #Employee values('20','A20')
Insert into #Employee values('30','A30')
Insert into #Employee values('40','A40')
Insert into #Employee values('100','A100')
Insert into #Employee values('200','A200')
Insert into #Employee values('300','A300')
Insert into #Employee values('400','A400')

SELECT
Emp1.UserName AS DispensePharmacyUserNM,
Emp2.UserName AS PendVerifyPharmacyUserNM,
Emp3.UserName AS CanceledPendVerifyPharmacyUserNM,
Emp4.UserName AS VerifyPharmacyUserNM
FROM #SummaryDispense SDD
LEFT OUTER JOIN #Employee Emp1
ON Emp1.UserID = SDD.[DispensePharmacyUserID]
LEFT OUTER JOIN #Employee Emp2
ON Emp2.UserID = SDD.[PendVerifyPharmacyUserID]
LEFT OUTER JOIN #Employee Emp3
ON Emp3.UserID = SDD.[CanceledPendVerifyPharmacyUserID]
LEFT OUTER JOIN #Employee Emp4
ON Emp4.UserID = SDD.[VerifyPharmacyUserID]

Answer 1

Hi,

Here is the solution for the new request:

Note! This will work only if the values do not include dot . since I use dot as the separator between the ID and the name. If there can be a dot in the values then we simply need to use different separator in the query but the solution is the same.

;With MyCTE_00 as (  
   SELECT   
   RN = ROW_Number() OVER (ORDER BY (SELECT NULL)),  
   e.CanceledPendVerifyPharmacyUserID,   
   e.DispensePharmacyUserID,   
   e.PendVerifyPharmacyUserID,  
   e.VerifyPharmacyUserID  
   FROM #SummaryDispense e  
  )  
  ,MyCTE_01 as (  
   select p.RN, p.ColumnName, p.UserID, e.UserName  
   from MyCTE_00  
       unpivot(UserID for ColumnName in (  
   [DispensePharmacyUserID],[PendVerifyPharmacyUserID], [CanceledPendVerifyPharmacyUserID],[VerifyPharmacyUserID]  
   )) as P  
   LEFT JOIN #Employee e ON p.UserID = e.UserID  
  )  
  , MyCTE02 as (  
  SELECT [DispensePharmacyUserID],[PendVerifyPharmacyUserID], [CanceledPendVerifyPharmacyUserID],[VerifyPharmacyUserID]  
  FROM    
  (  
    SELECT RN, ColumnName, UserName = UserName + '.' + CONVERT(VARCHAR(10), UserID)  
    FROM MyCTE_01  
  ) AS TableToPivot   
  PIVOT    
  (    
    MIN(UserName)    
    FOR ColumnName IN ([DispensePharmacyUserID],[PendVerifyPharmacyUserID], [CanceledPendVerifyPharmacyUserID],[VerifyPharmacyUserID])    
  ) AS PivotTable  
  )  
  SELECT   
 PARSENAME([DispensePharmacyUserID]            ,1) as [DispensePharmacyUserID]          , PARSENAME([DispensePharmacyUserID]            ,2) as [DispensePharmacyUserName]          ,  
 PARSENAME([PendVerifyPharmacyUserID]          ,1) as [PendVerifyPharmacyUserID]        , PARSENAME([PendVerifyPharmacyUserID]          ,2) as [PendVerifyPharmacyUserName]        ,   
 PARSENAME([CanceledPendVerifyPharmacyUserID]  ,1) as [CanceledPendVerifyPharmacyUserID], PARSENAME([CanceledPendVerifyPharmacyUserID]  ,2) as [CanceledPendVerifyPharmacyUserName],  
 PARSENAME([VerifyPharmacyUserID]              ,1) as [VerifyPharmacyUserID]            , PARSENAME([VerifyPharmacyUserID]              ,2) as [VerifyPharmacyUserName]              
  FROM MyCTE02  
  GO  

Answer 2

Good day,

You can avoid JOIN the same multiple times (which behind the scene lead to scanning the same table multiple times) by using UNPIVOT and PIVOT.

In this simple example when we are using a small tables without any index, the following execution plan seems better, but you should test the solution in your real database to make the decision.

Here is the solution:

;With MyCTE_00 as (  
 SELECT   
 RN = ROW_Number() OVER (ORDER BY (SELECT NULL)),  
 e.CanceledPendVerifyPharmacyUserID,   
 e.DispensePharmacyUserID,   
 e.PendVerifyPharmacyUserID,  
 e.VerifyPharmacyUserID  
 FROM #SummaryDispense e  
)  
,MyCTE_01 as (  
 select p.RN, p.ColumnName, p.UserID, e.UserName  
 from MyCTE_00  
     unpivot(UserID for ColumnName in (  
 [DispensePharmacyUserID],[PendVerifyPharmacyUserID], [CanceledPendVerifyPharmacyUserID],[VerifyPharmacyUserID]  
 )) as P  
 LEFT JOIN #Employee e ON p.UserID = e.UserID  
)  
SELECT [DispensePharmacyUserID],[PendVerifyPharmacyUserID], [CanceledPendVerifyPharmacyUserID],[VerifyPharmacyUserID]  
FROM    
(  
  SELECT RN, ColumnName, UserName  
  FROM MyCTE_01  
) AS TableToPivot   
PIVOT    
(    
  MIN(UserName)    
  FOR ColumnName IN ([DispensePharmacyUserID],[PendVerifyPharmacyUserID], [CanceledPendVerifyPharmacyUserID],[VerifyPharmacyUserID])    
) AS PivotTable;    
GO  

Notice that in the second Execution Plan (my query) you only scan the #Employee table once and the total relative cost is only 31% vs your original query which scan the #Employee 4 times and cost 69% (this is less than half the cost).

With that said, as mentioned you must check that this behaves the same in your real production table where you (hopefully) and indexes and probably other objects which might change the Execution Plan (check the IO and the CPU as well using SET STATISTICS).