Avoid same Table Join multiple times

Rahul Polaboina 181 Reputation points
2022-11-11T06:01:08.827+00:00

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]

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,596 questions
{count} votes

Accepted answer
  1. Ronen Ariely 15,101 Reputation points
    2022-11-15T21:43:03.63+00:00

    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  
    
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Ronen Ariely 15,101 Reputation points
    2022-11-12T17:08:21.587+00:00

    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).

    259804-image.png

    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).