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

Question

Please help me to optimise the sql given below, after loading more than 5000 records, the sql is become very very slow. If I remove the inner query , the sql become very fast. Please can you advise me how can I optimise the given sql . If you can give me an sql , I could replace the existing one with the optimised one

Here is my structure
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])),

List of Employee table
1, AAAAA, 1,1
2, BBBBB, 1,1
3, CCCCC, 1,1
4, DDDDD, 1,1
5, EEEEE, 1,1

List of ClockDevice table
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

Here is my sqls given
declare
@DepotNo int ,
@DepartmentID int ,
@apl smalldatetime

set @UserID = 1805;
set @DepotNo = 1;
set @DepartmentID = 2;
set @apl = '01-01-2020';

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

   SELECT    
                    
                 Employee.DepotNo  
                 ,Employee.DepartmentID  
                 ,Employee.EmployeeID                     
                 ,Employee.EmployeeName               
                    
                       ,(Select COALESCE(CONVERT(CHAR(5),  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(5),  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;  

The Output would be

it would be very appreciated if you can give optimised sql to get the desired result

Thanks
Pol

Answer 1

Hi @Polachan Paily ,

Could you please provide your execution plan and index information?

Echo