Optimize Select Statement

March 26, 2024

In the query below, I am pulling a list of managers names that have had activity since 2023-01-01. The managers name comes from tblSAP and then I am joining to tblUsers2 to look for activity. In most cases the managers name is in the Supervisor or Level6 field BUT it could also be in Level5, 7 or 8 thus the multiple ‘OR’ conditions. This query is pulling the correct data back but takes around 90 seconds to run. tblUsers2 has 1.9 million rows and growing daily. My question is can this be optimized to run quicker? I am not sure what is going on in the background but am wondering if after the first occurrence of a managers name being found in tblUsers2 it can move on to the next manager? Thanks in advance for any guidance on this.

DECLARE
@StartDate AS Datetime
SET @StartDate = '2023-01-01'

SELECT Last + ', ' + First AS Manager_Name
FROM tblSAP
INNER JOIN tblUsers2 U2 ON Last + ', ' + First = U2.Supervisor OR
    (Last + ', ' + First = Level6) OR
    (Last + ', ' + First = Level5) OR
    (Last + ', ' + First = Level7) OR
    (Last + ', ' + First = Level8)
WHERE U2.ExtractDate >= @StartDate AND (Title LIKE '%manager%') AND (Title LIKE '%operations%') OR (Job_Desc LIKE '%manager%') AND (Job_Desc LIKE '%operations%') 
GROUP BY Last, First
ORDER BY Last, First;

>Solution :

Because you only select a name from the table tblSAP, you do not need to do an INNER JOIN to the table tblsUser, which could result in 1.9 million rows.

You only need to know if a user exist, so doing this might work (untested):

NOTE:

added extra () arround the OR part (two times!)
the EXISTS will sop searching the tblUsers when at least 1 user is found.

DECLARE @StartDate AS Datetime = '20230101'

SELECT 
   Last + ', ' + First AS Manager_Name
FROM tblSAP
WHERE EXISTS (
    SELECT 1 
    FROM  tblUsers2 U2 
    WHERE 
       Last + ', ' + First IN (U2.Supervisor, Level5, Level6, Level7, Level8)
       AND U2.ExtractDate >= @StartDate 
       AND (
              (Title LIKE '%manager%') AND (Title LIKE '%operations%') OR 
              (Job_Desc LIKE '%manager%') AND (Job_Desc LIKE '%operations%')
           ) 
  )
ORDER BY Last, First;