请帮助我优化下面的内部查询代码

Please can you help me to optimise the inner query sql given


问题

请帮助我优化下面的sql,在加载超过5000条记录后,sql变得非常非常慢。如果我去掉内部查询,该语句变得非常快。请你告诉我,如何才能优化给定的sql。如果你能给我一个sql,我可以用优化后的sql替换现有的sql

以下是我的结构
Employee
[key]
EmployeeId int
EmployeeName varchar(max)
Depot int
Department int


ClockDevice
ID int
EmpCode nvarchar not null
DateTime datetime null
[LogDate] AS (CONVERT([date], [DateTime]),


雇员表列表
1, AAAAA, 1,1
2, BBBBB, 1,1
3, CCCCC, 1,1
4, DDDDD, 1,1
5, EEEEE, 1,1

ClockDevice表列表
1, '1' , '2019-05-10 07: 44:28. 000' ,1
2, '1' , '2019-05-10 17:44:28.000' ,1
3, '2' , '2019-05-10 07:57:16.000', 2
4, '2' , '2019-05-10 17:57:16. 000', 2

5, '4' , '2019-05-10 07:44:28.000' ,4
6, '4' , '2019-05-10 17:57:16.000', 4
7, '3' , '2019-05-10 07:57:16. 000', 3

下面是我给出的sqls
声明
@DepotNo int ,
@DepartmentID int ,
@Date smalldatetime

s et @UserID = 1805;
s et @DepotNo = 1;
s et @DepartmentID = 2;
s et @Date = '01-01-2020';

declare @dateclocked as date
SET NOCOUNT ON;
set @dateclocked = CAST(@Date as date)

    SELECT  
                      
                  Employee.DepotNo
                  ,Employee.DepartmentID
                  ,Employee.EmployeeID                   
                  ,Employee.EmployeeName             
                      
                        ,(Select COALESCE(CONVERT(CHAR 

, CAST(MIN(DateTime)AS TIME)),'00:00') From ClockDevice clock WHERE EmpCode = Employee.EmployeeID AND CAST(clock.DateTime AS DATE) = CAST(@Date AS DATE)) ClockedIn ,ISNULL((select COALESCE(CONVERT(CHAR


, CAST(MAX(DateTime)AS TIME)),'00:00') maxdt from ClockDevice clock where EmpCode=Employee.EmployeeID AND CAST(DateTime AS DATE) = CAST(@Date AS DATE) group by EmpCode having CAST(MIN(DateTime) AS TIME) <> CAST(MAX(DateTime) AS TIME)),'00:00') ClockedOut ,(Select CASE WHEN ( select CAST(MIN(DateTime)AS TIME) FROM ClockDevice WHERE ClockDevice.EmpCode = Employee.EmployeeID and CAST(DateTime AS DATE) = CAST(@Date AS DATE)) is null Then 0 else 1 end ) IsClockedIn ,(Select CASE WHEN ( select CAST(MAX(DateTime)AS TIME) FROM ClockDevice WHERE ClockDevice.EmpCode = Employee.EmployeeID and CAST(DateTime AS DATE) = CAST(@Date AS DATE) group by EmpCode having CAST(MIN(DateTime) AS TIME) <> CAST(MAX(DateTime) AS TIME) ) is null Then 0 else 1 end ) IsClockedOut FROM dbo.goEmployee Employee WHERE Employee.DepotNo = @DepotNo AND (@DepartmentID= 0 OR Employee.DepartmentID = @DepartmentID ) AND Employee.[IsDeleted] = 0 AND Employee.[IsHidden] = 0 AND Employee.[IsStopped] = 0;


输出将是 58283-capture.png


如果你能给出优化的sql以获得所需的结果,将非常感激

谢谢
Pol


你有什么更新吗?

Echo

答案1

你好 ,

你能不能提供你的执行计划和索引信息?

Echo