'Move Functions from Where Clause to Select Statement
I have a union query that runs abysmally slow I believe mostly because there are two functions in the where clause of each union. I am pretty sure that there is no getting around the unions, but there may be a way to move the functions from the where of each. I won't post ALL of the union sections because I don't think it is necessary as they are all almost identical with the exception of one table in each. The first function was created by someone else but it takes a date, and uses the "frequency" value like "years, months, days, etc." and the "interval" value like 3, 4, 90 to calculate the new "Due Date". For instance, a date of today with a frequency of years, and an interval of 3, would produce the date 4/21/2025. Here is the actual function:
ALTER FUNCTION [dbo].[ReturnExpiration_IntervalxFreq](@Date datetime2,@int int, @freq int)
RETURNS datetime2
AS
BEGIN
declare @d datetime2;
SELECT @d = case when @int = 1 then null-- '12-31-9999'
when @int = 2 then dateadd(day,@freq,@date)
when @int = 3 then dateadd(week,@freq,@date)
when @int = 4 then dateadd(month,@freq,@date)
when @int = 5 then dateadd(quarter,@freq,@date)
when @int = 6 then dateadd(year,@freq,@date)
end
RETURN @d;
The query itself is supposed to find and identify records whose Due Date has past or is within 90 days of the current date. Here is what each section of the union looks like
SELECT
R.RequirementId
, EC.EmployeeCompanyId
, EC.CompanyId
, DaysOverdue =
CASE WHEN
R.DueDate IS NULL
THEN
CASE WHEN
EXISTS(SELECT 1 FROM tbl_Training_Requirement_Compliance RC WHERE RC.EmployeeCompanyId = EC.EmployeeCompanyId AND RC.RequirementId = R.RequirementId AND RC.Active = 1 AND ((DATEDIFF(DAY, R.DueDate, GETDATE()) > -91 OR R.DueDate Is Null ) OR (DATEDIFF(DAY, dbo.ReturnExpiration_IntervalxFreq(TRC.EffectiveDate, R.IntervalId, R.Frequency), GETDATE()) > -91)) OR R.IntervalId IS NULL)
THEN
DateDiff(day,ISNULL(dbo.ReturnExpiration_IntervalxFreq(TRC.EffectiveDate, R.IntervalId, R.Frequency), '12/31/9999'),getdate())
ELSE
0
END
ELSE
DATEDIFF(day,R.DueDate,getdate())
END
,CASE WHEN
EXISTS(SELECT 1 FROM tbl_Training_Requirement_Compliance RC WHERE RC.EmployeeCompanyId = EC.EmployeeCompanyId AND RC.RequirementId = R.RequirementId AND RC.Active=1 AND (GETDATE() > dbo.ReturnExpiration_IntervalxFreq(RC.EffectiveDate, R.IntervalId, R.Frequency) OR R.IntervalId IS NULL))
THEN
CONVERT(VARCHAR(12),dbo.ReturnExpiration_IntervalxFreq(TRC.EffectiveDate, R.IntervalId, R.Frequency), 101)
ELSE
CONVERT(VARCHAR(12),R.DueDate,101)
END As DateDue
FROM
@Employees AS EC
INNER JOIN dbo.tbl_Training_Requirement_To_Position TRP ON TRP.PositionId = EC.PositionId
INNER JOIN @CompanyReqs R ON R.RequirementId = TRP.RequirementId
LEFT OUTER JOIN tbl_Training_Requirement_Compliance TRC ON TRC.EmployeeCompanyId = EC.EmployeeCompanyId AND TRC.RequirementId = R.RequirementId AND TRC.Active = 1
WHERE
NOT EXISTS(SELECT 1
FROM tbl_Training_Requirement_Compliance RC
WHERE RC.EmployeeCompanyId = EC.EmployeeCompanyId
AND RC.RequirementId = R.RequirementId
AND RC.Active = 1
)
OR (
(DATEDIFF(DAY, R.DueDate, GETDATE()) > -91
OR R.DueDate Is Null )
OR (DATEDIFF(DAY, dbo.ReturnExpiration_IntervalxFreq(TRC.EffectiveDate, R.IntervalId, R.Frequency), GETDATE()) > -91))
UNION...
It is supposed to exclude records that either don't exist at all on the tbl_Training_Requirement_Compliance table, or if they do exist, once the frequency an intervals have been calculated, would have a new due date that is within 90 days of the current date. I am hoping that someone with much more experience and expertise in SQL Server can show me a way, if possible, to remove the functions from the WHERE clause and help the performance of this stored procedure.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
