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

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;

输出将是

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

谢谢
Pol

你有什么更新吗？

Echo

答案1

你好 ,

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

Echo

相同问题还可以参考: Please can you help me to optimise the inner query sql given

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

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

分享到

相关文章