CODEWITHSUNDEEP
sqlsql-server
Aryan M
Aryan M

Reputation: 657

SQL Query optimization without Not In and In

I'm trying to write a below query and it is fetching me the required results. But i have read that using NOT IN and IN is not good for performance.

Can one please suggest, what would be the best way to modify this query as EmailLog table contains a lot of data and is there a way i can avoid calling this table twice, to optimize the query performance.

Select E.EmployeeID, E.GovtYN From EMployee E
    INNER JOIN RentalAgreement RA
    ON RA.EmployeeID = E.EmployeeID AND RA.DateTo = Cast(DATEADD(day, 21 , 
    GetDate()) As Date)
AND E.EmployeeID NOT IN (
    Select EmployeeID From EmailLog
    Where DateLogged = Cast (GetDate() As Date)  AND TemplateID = 100
)
AND E.EmployeeID IN
(
    Select EmployeeID From EmailLog
    Where DateLogged Between Cast(DateAdd(day, -21, GetDate()) AS Date) AND 
    Cast(GetDate() As Date) AND TemplateID = 200
)

I have been trying to fetch all employee details where their rental agreement is about to expire in next 21 days. So for each of the employee record fetched, there would be an entry made in EmailLog table.

So i need to make sure that the same employee record is not picked again on the same day making sure it is not in EmailLog table and i need to verify one more entry on the EmailLog table for the employee saying there is an entry against him in the EmailLog table with the TemplateID = 200, so that only those employee details are fetched.

Upvotes: 1

Views: 292

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

I would write the logic as:

select ra.EmployeeID, ra.GovtYN
from RentalAgreement ra
where ra.DateTo = Cast(DATEADD(day, 21, GetDate()) As Date) and
      not exists (select 1
                  from emaillog el
                  where el.EmployeeID = ra.EmployeeID and
                        el.DateLogged = Cast(GetDate() As Date)  
                 ) and
      exists (select 1
              from emaillog el
              where el.EmployeeID = ra.EmployeeID and
                    el.DateLogged = Cast(dateadd(day, -21, GetDate()) As Date)  
             ) ;

Then, I would include indexes on RentalAgreement(DateTo, EmployeeId) and emaillog(EmployeeID, DateLogged).

Note that for the query you have in your question, you don't seem to need the Employee table. The only column you are using from that table is the matched column in RentalAgreement.

Upvotes: 2

Related Questions