如何优化查询以减少执行时间
如何优化查询以减少执行时间
提问于 2019-10-13 14:05:59
我的查询的order by子句和between的datetime比较导致执行时间增加，因为我已经为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回答 1
Stack Overflow用户
发布于 2019-10-13 15:03:05
看起来您正在尝试从多个来源(EmployeeMachineLink和AttendanceLog)获取员工的信息。对吗？如果是这样，我认为您只需要清理WHERE子句逻辑：
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更改
- 为readability
- removed添加了表别名一起重复引用
dbo.tbl_WPT_AttendanceLog.ATInOutMode IN (1,2,5) - removed duplicate
emlBETWEEN ... AND ...ORconditions
在不使用括号的情况下混合使用OR和AND时必须小心。否则，这将导致意想不到的结果，并可能导致糟糕的性能。
如果有帮助，请告诉我。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/58361008
复制相关文章
相似问题
腾讯云开发者
