Hi @Polachan Paily ,
Could you please provide your execution plan and index information?
Echo
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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