CODEWITHSUNDEEP
sqlsql-server-2008sql-server-2008-r2
user12158375
user12158375

Reputation: 11

How to optimize query to reduce execution time

My query's order by clause & datetime comparison of between causes the execution time to increase, where as I had indexed the datetime 

SELECT TOP(1) 
    @PeriodStart = DATEADD(SECOND, 1, dbo.tbl_WPT_AttendanceLog.ATDateTime) 
FROM         
    dbo.tbl_WPT_EmployeeMachineLink 
INNER JOIN
    dbo.tbl_WPT_Machine ON dbo.tbl_WPT_EmployeeMachineLink.FK_tbl_WPT_Machine_ID = dbo.tbl_WPT_Machine.ID 
RIGHT OUTER JOIN
    dbo.tbl_WPT_AttendanceLog ON dbo.tbl_WPT_EmployeeMachineLink.FK_tbl_WPT_Machine_ID = dbo.tbl_WPT_AttendanceLog.FK_tbl_WPT_Machine_ID 
                              AND dbo.tbl_WPT_EmployeeMachineLink.MachineEnrollmentNo = dbo.tbl_WPT_AttendanceLog.ATEnrollmentNo
WHERE     
    (dbo.tbl_WPT_EmployeeMachineLink.FK_tbl_WPT_Employee_ID = @EmpID) 
    AND (dbo.tbl_WPT_AttendanceLog.ATDateTime BETWEEN @ShiftEndPreviousInstance AND @ShiftStart) 
    AND dbo.tbl_WPT_AttendanceLog.ATInOutMode in (1,2,5)
    OR (dbo.tbl_WPT_AttendanceLog.ATDateTime BETWEEN @ShiftEndPreviousInstance AND @ShiftStart)
    AND (dbo.tbl_WPT_AttendanceLog.FK_tbl_WPT_Employee_ID = @EmpID) 
    AND dbo.tbl_WPT_AttendanceLog.ATInOutMode in (1,2,5)
ORDER BY
    dbo.tbl_WPT_AttendanceLog.ATDateTime DESC

Upvotes: 0

Views: 88

Answers (1)

ravioli
ravioli

Reputation: 3833

It looks like you're trying to get an employee's info from multiple sources (EmployeeMachineLink and AttendanceLog). Is that correct? If so, I think you just need to clean up the WHERE clause logic:

SELECT TOP(1) 
  @PeriodStart = DATEADD(SECOND, 1, dbo.tbl_WPT_AttendanceLog.ATDateTime) 
FROM dbo.tbl_WPT_EmployeeMachineLink eml
INNER JOIN dbo.tbl_WPT_Machine ON eml.FK_tbl_WPT_Machine_ID = dbo.tbl_WPT_Machine.ID 
RIGHT OUTER JOIN dbo.tbl_WPT_AttendanceLog ON eml.FK_tbl_WPT_Machine_ID = dbo.tbl_WPT_AttendanceLog.FK_tbl_WPT_Machine_ID 
  AND eml.MachineEnrollmentNo = dbo.tbl_WPT_AttendanceLog.ATEnrollmentNo
WHERE (
  eml.FK_tbl_WPT_Employee_ID = @EmpID OR
  dbo.tbl_WPT_AttendanceLog.FK_tbl_WPT_Employee_ID = @EmpID
) 
AND (dbo.tbl_WPT_AttendanceLog.ATDateTime BETWEEN @ShiftEndPreviousInstance AND @ShiftStart)
AND dbo.tbl_WPT_AttendanceLog.ATInOutMode IN (1,2,5)
ORDER BY dbo.tbl_WPT_AttendanceLog.ATDateTime DESC

Changes
- added table alias eml for readability
- removed duplicate reference to dbo.tbl_WPT_AttendanceLog.ATInOutMode IN (1,2,5)
- removed duplicate BETWEEN ... AND ... reference
- grouped OR conditions together

You have to be careful when mixing OR with AND without using parentheses. Otherwise that will lead to unexpected results and possibly poor performance.

Let me know if that helps.

Upvotes: 1

Related Questions