NewUser NewUser - 7 months ago 19
SQL Question

Improvise SQL query

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.

SELECT Sum(CASE
WHEN UpdatedAdjustedOT IS NOT NULL
AND UpdatedAdjustedOT != ''
AND UpdatedAdjustedOT != '0'
THEN CONVERT(DECIMAL(18, 2), UpdatedAdjustedOT)
ELSE 0
END) AS OTHours
FROM tbl_OTAuthorization
WHERE EmployeeCodeFK = @EmployeeCode
AND month(OTDate) = Month(@FromDate)
AND year(OTDate) = Year(@FromDate)


Please suggest me how do I re-write this query in a better way

enter image description here

Answer

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.

Comments