In a stored procedure, I have to do some calculation based on the previous record and a total. Currently I can achieve this using Cursor or a table variable, but its taking a lot of time as the number of records are huge.
How can I achieve this to improve the performance?

A part of my stored procedure:

        ID [INT],
        EmployeeName [VARCHAR](100),
        WorkDate [DATE],
        RegularHours [DECIMAL],
        OvertimeHours [DECIMAL],
        NightJob [BIT],
        JobId [INT]

    INSERT INTO @HoursTemp
    SELECT ID, EmployeeName, WorkDate, RegularHours,OvertimeHours, NightJob, JobId
            FROM @PayrollSheet              
            GROUP BY WorkDate, EmployeeName , JobIdType,  NightJob, RegularHours, OvertimeHours, ID, JobId
            ORDER BY WorkDate, EmployeeName, JobIdType, NightJob, JobId

     SELECT @count = COUNT(ID) FROM @HoursTemp WHILE (@i <= @count)
        Print @i
        SELECT @PayRollID = ID ,
        @EmployeeName = EmployeeName,
        @WorkDate = WorkDate,
        @RegularHours = RegularHours,
        @OverTimeHour = OvertimeHours,
        @NightHours = NightJob,
        @PayRollJobID = JobId
        FROM @HoursTemp     

        IF(@WorkDate = @PreviousDate AND @EmployeeName = @PreviousEmployeeName) -- If Current date and previous date matches

            SET @SumOfHours = @SumOfHours + @RegularHours;  -- Then Sum up the regular hour with previous hour.
            IF(@SumOfHours >= 8 AND @SumOverTimeHours = 0)
                    SET @SumOverTimeHours = @OverTimeHour + (@SumOfHours - 8); -- Sum up the overtime hour.
                    SET @RegularHours = 8 - (@SumOfHours - @RegularHours);  -- Get remainder regular hour.

                    UPDATE @PayrollSheet SET RegularHours = @RegularHours, OvertimeHours = @SumOverTimeHours Where ID = @PayRollID  -- Update the temp table.
                    --When sum of regular hours is greater than 8, further regular hours of the day will be considered as over time hours.
                    IF(@SumOfHours >=8)
                        UPDATE @PayrollSheet SET RegularHours = 0, OvertimeHours = @RegularHours Where ID = @PayRollID  --Update the over time hours with regular hour and set regular hour with 0.
                SET @SumOfHours = @RegularHours; -- If Dates does not match then reset the regular hours sum variable to current regular hours so it will be summed with next regular hours when dates match.
                SET @SumOverTimeHours = 0;  -- Sum up Over time hours present in existing records.
                --SET @SumOfNightHours = 0; -- Reset OverTime Sum and Night hours sum variable when dates does not match.

        -- Get counts of record with same work date,Job ID and Night Job true and match it with total records with same work date.          
        SELECT @RecordsWithWorkDateAndJobID = COUNT(*) from @PayrollSheet where WorkDate = @WorkDate AND JobID = @PayRollJobID AND NightJob = 1 AND EmployeeName = @EmployeeName
        SELECT @RecordsWithOnlyWorkDate = COUNT(*) from @PayrollSheet where WorkDate = @WorkDate AND EmployeeName = @EmployeeName --AND NightJob = 1

        --If both count matches then night hours are entered in same job on a day. Else night hours are entered in more than one job. Also if sum of night hours is less than 8 then only update night hours.
        IF(NOT(@RecordsWithWorkDateAndJobID > 0 AND @RecordsWithOnlyWorkDate > 0 AND @RecordsWithWorkDateAndJobID = @RecordsWithOnlyWorkDate)) --AND @SumOfNightHours < 8 
                UPDATE @PayrollSheet SET NightJob = 0 Where ID = @PayRollID -- Set regular hours as 0 and night hours as the regular hour.
                --SET @SumOfNightHours = @SumOfNightHours + @RegularHours; -- Maintain sum of night hours, so that if it is greater than 8 then night hours will not be added.

        SET @PreviousDate = @WorkDate   --Store previous work date.     
        SET @PreviousJobID = @PayRollJobID -- Store previous job id.
        SET @PreviousEmployeeName = @EmployeeName

        -- Fetch next record from temp table.    

        SELECT @i = @i + 1
  • 2
    Since you are on Sql 2014, have you looked into using the LEAD and LAG windowing functions that were introduce in 2012? - databasejournal.com/features/mssql/… - I'd also recommend refraining from using table variables if you are dealing with a large number of rows - the optimizer can often generate poor execution plans when using table variables – Scott Hodgin Feb 23 '17 at 11:54

Is there a reason why your code can not be simplified to something like this?:

insert into @HoursTemp (
    Id, EmployeeName, WorkDate
  , RegularHours, OvertimeHours, NightJob, JobId
  , EmployeeName
  , WorkDate
  , RegularHours  = case 
      when sum(RegularHours) > 8 
        then 8 
      else sum(RegularHours) 
  , OvertimeHours = case 
      when sum(RegularHours) > 8 
        then sum(RegularHours) - 8 
      else 0 
  , NightJob
  , JobId
from @PayrollSheet
group by 
  , EmployeeName
  , WorkDate
  , NightJob  
  --, JobIdType  /* Why are grouping by this but not inserting this into @HoursTemp */
  , JobId 
order by WorkDate, EmployeeName, NightJob, JobId

All of your code related to @SumOfNightHours is commented out, is that because it does not work yet or because the requirements changed?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.