How to improve perfomance on query NOT IN

Question

I have the following SQL Query.

SELECT em.employeeid, tsi.timestamp
FROM timesheet_temp_import tsi
JOIN employee emp ON emp.employeeid = tsi.credentialnumber
WHERE
tsi.masterentity = 'MASTER' AND
tsi.timestamp NOT IN
(
    SELECT ea.timestamp 
    FROM employee_attendance ea 
    WHERE 
    ea.employeeid = em.employeeid
    AND ea.timestamp =  tsi.timestamp
    AND ea.ismanual = 0
)
GROUP BY em.employeeid, tsi.timestamp

This query compares a import table (with employee time and attendance timestamps).

Sometimes timesheet_temp_import has more than 95,000 rows and my query has to show only the timestamps that are new for the employee. If the timestamp already exists for an employee then I have to exclude it.

The query is working but is taking more than 4 minutes so I want to know if I can improve the NOT IN statement with other that can help me to reduce this time.

Answer 1

Using NOT EXISTS might help you.

SELECT 
    em.employeeid,
    tsi.timestamp
    FROM timesheet_temp_import tsi
    join employee emp ON emp.employeeid = tsi.credentialnumber
    WHERE
    tsi.masterentity = 'MASTER' AND

    NOT EXISTS 
    (
        SELECT NULL  
        FROM employee_attendance ea 
        WHERE 
        ea.employeeid = em.employeeid
        AND ea.timestamp =  tsi.timestamp
        AND ea.ismanual = 0
    )
    GROUP BY 
    em.employeeid,
    tsi.timestamp