I have the below sql which takes 1 or 2 secs to return the result. I am calling this SQL inside cursor for 500 plus times. I am trying to re-write this query.
WHEN UpdatedAdjustedOT IS NOT NULL
AND UpdatedAdjustedOT != ''
AND UpdatedAdjustedOT != '0'
THEN CONVERT(DECIMAL(18, 2), UpdatedAdjustedOT)
END) AS OTHours
WHERE EmployeeCodeFK = @EmployeeCode
AND month(OTDate) = Month(@FromDate)
AND year(OTDate) = Year(@FromDate)
First, re-write the
WHERE clause to look like this:
SELECT . . . FROM tbl_OTAuthorization WHERE EmployeeCodeFK = @EmployeeCode AND OTDate >= DATEADD(day, 1 - DAY(@FromDate), @FromDate) AND OTDate < DATEADD(month, 1, DATEADD(@FromDate, 1 - DAY(@FromDate), @FromDate));
Second, create an appropriate index for the table:
CREATE INDEX tbl_OTAuthorization_2 ON tbl_OTAuthorization(EmployeeCodeFK, OTDate, UpdatedAdjustedOT);
Third, go back and fix your code so it is not looping over employees and from dates. You should be able to handle the logic in a single query. In general, avoid cursors if you want to optimize the performance of using a database.