CODEWITHSUNDEEP
sqlsql-server
Anup
Anup

Reputation: 9738

SQL Query with non exists optimize

I have the following query which i am directly executing in my Code & putting it in datatable. The problem is it is taking more than 10 minutes to execute this query. The main part which is taking time is NON EXISTS.

SELECT 
    [t0].[PayrollEmployeeId], 
    [t0].[InOutDate], 
    [t0].[InOutFlag], 
    [t0].[InOutTime] 
FROM [dbo].[MachineLog] AS [t0] 
WHERE 
        ([t0].[CompanyId] = 1) 
    AND ([t0].[InOutDate] >= '2016-12-13')
    AND ([t0].[InOutDate] <= '2016-12-14') 
    AND 
    (   NOT (EXISTS(
        SELECT NULL AS [EMPTY] 
        FROM [dbo].[TO_Entry] AS [t1]  
        WHERE 
                ([t1].[EmployeeId] = [t0].[PayrollEmployeeId]) 
            AND ([t1]. [CompanyId] = 1) 
            AND ([t0].[PayrollEmployeeId] = [t1].[EmployeeId]) 
            AND (([t0].[InOutDate]) = [t1].[Entry_Date]) 
            AND ([t1].[Entry_Method] = 'M') 
        ))
    ) 
ORDER BY 
    [t0].[PayrollEmployeeId], [t0].[InOutDate]

Is there any way i can optimize this query? What is the work around for this. It is taking too much of time.

enter image description here

Upvotes: 0

Views: 54

Answers (2)

Eralper
Eralper

Reputation: 6612

You will realize that there is an informative message on the execution plan for your query

It is informing that there is a missing cluster index with an effect of 30% on the execution time

It seems that transaction data is occurring based on some date fields like Entry time. Dates fields especially on your case are strong candidates for clustered indexes. You can create an index on Entry_Date column I guess you have already some index on InOutDate You can try indexing this field as well

Upvotes: 2

Eralper
Eralper

Reputation: 6612

It seems that you can convert the NOT EXISTS into a LEFT JOIN query with second table returning NULL values

Please check following SELECT and modify if required to fulfill your requirements

SELECT 
    [t0].[PayrollEmployeeId], [t0].[InOutDate], [t0].[InOutFlag], [t0].[InOutTime] 
FROM [dbo].[MachineLog] AS [t0] 
LEFT JOIN [dbo].[TO_Entry] AS [t1]
    ON  [t1].[EmployeeId] = [t0].[PayrollEmployeeId]
    AND [t0].[PayrollEmployeeId] = [t1].[EmployeeId]
    AND [t0].[InOutDate] = [t1].[Entry_Date]
    AND [t1]. [CompanyId] = 1
    AND [t1].[Entry_Method] = 'M'
WHERE 
        ([t0].[CompanyId] = 1) 
    AND ([t0].[InOutDate] >= '2016-12-13')
    AND ([t0].[InOutDate] <= '2016-12-14') 
    AND [t1].[EmployeeId] IS NULL
ORDER BY 
    [t0].[PayrollEmployeeId], [t0].[InOutDate]

Upvotes: 2

Related Questions