locked
Please help me to optimize the sql to get the result very fast RRS feed

  • Question

  • I have written an sql to find the attendance and also to get the minimum time and maximum time of clocking only if the employee clocked for that date. My table structure is as follows

    Create table Employee
    DepotNo int
    Department int
    Empid int 
    EmpName varchar(max)

    Create table Attendance
    Empid int
    AttendanceDate datetime

    Create View ViewEmpMinMaxClocking
    Empid int
    ClockDate datetime
    MinClockTime  varchar(10)
    MaxClockTime varchar(10)

    Record Contain is as follows

    Sql

    The sql is working fine and the result is correct  but it is very slow to get the result if the  number of employee in a depot is more.  The slow is coming due to the inner select from cteclokin virtual table . Is it possible to avoid inner select and to give left outer join . I tried by using left outer join but the result is coming wrongly.

    set @DepotNo =2 
    set @DepartmentID =1
    set @dateclocked = '03-01-2020'
    
    ,cteclockin as
    	(SELECT  emp.EmployeeID, MinClockTime,MaxClockTime  FROM ViewEmpMinMaxClocking  clock    
    	inner Join goEmployee emp
    	on  clock.EmpCode = emp.EmployeeID   
    	Where 
    	emp.DepotNo=@DepotNo
    	And emp.DepartmentID=@DepartmentID
    	and clock.ClockDate =  @dateclocked )
    		SELECT  
    			,[AttendanceDate]
    			,Employee.DepotNo
    			,Employee.DepartmentID
    			,Employee.EmployeeID
    			,Employee.EmployeeName
    			,ISNULL((SELECT  MinClockin FROM cteclockin   WHERE  cteclockin.EmployeeID = Employee.EmployeeID),'00:00') ClockedIn
    			,ISNULL((SELECT  MaxClockOut FROM cteclockin   WHERE  cteclockin.EmployeeID = Employee.EmployeeID),'00:00') ClockedOut
    		     ,CASE 
    			WHEN  ( select Minclockin FROM cteclockin   WHERE  cteclockin.EmployeeID = Employee.EmployeeID) is null Then 0 else 1 end IsClockedIn
    			 
    	  FROM dbo.Employee Employee 
    	  INNER JOIN [dbo].[ Attendance] Attendancelog  ON Employee.EmployeeID = Attendancelog.EmployeeID 
    	  WHERE Employee.DepotNo  = @DepotNo
    	  AND Employee.DepartmentID = @DepartmentID  
     	  AND Attendancelog.AttendanceDate = @Date
    
    UNION ALL
    	SELECT 0
    			,@Date
    			,Employee.DepotNo
    			,Employee.DepartmentID
    			,Employee.EmployeeID
    			,Employee.EmployeeNo
    			,ISNULL((SELECT  MinClockin FROM cteclockin   WHERE  cteclockin.EmployeeID = Employee.EmployeeID),'00:00') ClockedIn
    			,ISNULL((SELECT  MaxClockOut FROM cteclockin   WHERE  cteclockin.EmployeeID = Employee.EmployeeID),'00:00') ClockedOut
    		     ,CASE 
    			WHEN  ( select Minclockin FROM cteclockin   WHERE  cteclockin.EmployeeID = Employee.EmployeeID) is null Then 0 else 1 end IsClockedIn
    	   FROM dbo.Employee Employee 
    	  WHERE Employee.DepotNo  = @DepotNo
    	  AND Employee.DepartmentID = @DepartmentID  
     	AND Employee.EmployeeID NOT IN(SELECT EmployeeID FROM [dbo].[Attendance] Attendancelog WHERE Attendancelog.AttendanceDate =  @Date)
    	order by Employee.EmployeeName;

    The expected Result would be as given below. Please can you help me with suggested sql



    polachan

    Wednesday, March 18, 2020 11:17 PM

All replies

  • Hi polachan,

    Inner Join and Left Outer Join connect different parts of tables, outer join includes what inner join would return but also includes other rows for which no corresponding match is found in the other table.

    You could kindly share us the execution plan on Paste The Plan.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, March 19, 2020 2:38 AM
  • Is that any help if I give indexed view for ViewEmpMinMaxClocking with its empcode and date? 

    polachan

    Thursday, March 19, 2020 9:10 AM
  • Can you create a stored procedure  with accepted parameters and check the performance?

    It would be great if you post here an execution plan of that query..


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, March 19, 2020 9:13 AM
    Answerer
  • Hi polachan,

    Using the suitable indexes could speed up the query and make the row unique, you could give it a try.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, March 20, 2020 8:29 AM