SQLTeam.com | Weblogs | Forums

Please help me performance of query is very slow when use scalar value funtion

Query:
SELECT

        count(distinct Activity_ID) as dueactivity
           
        FROM
		(
 
 select  
  RA.Regulation_ID,
    
    RA.Activity_ID,
 RA.Second_Owner,
   RA.Third_Owner,
 RA.Frequency_ID,

RM.Compliance_Type_ID,

  RA.First_Owner,

    CASE 
        WHEN CA.Due_Date = [synprod].[Udf_CAS_CalFutureDuedt1](F.[datepart],
        F.[Days],
        RA.Test_Date) THEN synprod.Udf_CAS_CalDuedt1(F.[datepart],
        F.[Days],
        CA.Due_Date,
        RA.Test_Date) 
        ELSE synprod.Udf_CAS_CalFutureDuedt1(F.[datepart],
        F.[Days],
        RA.Test_Date) 
    END AS Due_Date,
   
    synprod.Udf_CAS_CalRAGNoOfDays(CASE 
        WHEN CA.Due_Date = synprod.Udf_CAS_CalFutureDuedt1(F.[datepart],
        F.[Days],
        RA.Test_Date) THEN synprod.Udf_CAS_CalDuedt1(F.[datepart],
        F.[Days],
        CA.Due_Date,
        RA.Test_Date) 
        ELSE synprod.Udf_CAS_CalFutureDuedt1(F.[datepart],
        F.[Days],
        RA.Test_Date) 
    END) AS RAG
 
FROM
    synprod.MST_CAS_Compliance_Master RA 
INNER JOIN
    synprod.MST_Cas_RegulationMaster RM 
        ON RA.Regulation_ID = RM.Regulation_ID 

INNER JOIN
    synprod.MST_CAS_Frequency_Testing F 
        ON F.Frequency_ID = RA.Frequency_ID 
 
LEFT JOIN
    synprod.Vw_CAS_Transdata CA 
        ON CA.Regulation_ID = RA.Regulation_ID 
        AND CA.Activity_ID = RA.Activity_ID 

INNER JOIN
    synprod.MST_Cas_TypeOfCompliance TC 
        ON TC.Compliance_ID = RM.Compliance_Type_ID 
WHERE
    RA.Active_Status = 1 
    AND RM.Active_Status = 1 

) D

        WHERE
            Due_Date NOT IN (
                SELECT
                    synprod.CAS_Compliance_Assurance_Trans.Due_Date 
                FROM
                    synprod.CAS_Compliance_Assurance_Trans 
                WHERE
                    synprod.CAS_Compliance_Assurance_Trans.Regulation_ID = D.Regulation_ID 
                    AND synprod.CAS_Compliance_Assurance_Trans.Activity_ID = D.Activity_ID 
                    AND synprod.CAS_Compliance_Assurance_Trans.Active_Status = 1
            )
			 and ( RAG >= 0 and  (D.RAG <= 15) )

Scalar value function:

ALTER FUNCTION [synprod].[Udf_CAS_CalDuedt1]
(
@chrInterval VARCHAR(11),
@intIncrement INT,
@sdtDate SMALLDATETIME,
@sdttest_date SMALLDATETIME
)
RETURNS DATETIME
AS

BEGIN

-- Declare the return variable here
DECLARE @NewDate DATETIME
DECLARE @Day varchar(10)
-- Add the T-SQL statements to compute the return value here
SELECT @NewDate = CASE
    WHEN @chrInterval IN ('year', 'yy', 'yyyy') THEN DATEADD(YEAR, @intIncrement, @sdtDate)
    WHEN @chrInterval IN ('quarter', 'qq', 'q') THEN DATEADD(QUARTER, @intIncrement, @sdtDate)
    WHEN @chrInterval IN ('month', 'mm', 'm') THEN DATEADD(MONTH, @intIncrement, @sdtDate)
    WHEN @chrInterval IN ('dayofyear', 'dy', '') THEN DATEADD(DAYOFYEAR, @intIncrement, @sdtDate)
    WHEN @chrInterval IN ('day', 'dd', 'd') THEN DATEADD(DAY, @intIncrement, @sdtDate)
    WHEN @chrInterval IN ('week', 'wk', 'ww') THEN DATEADD(WEEK, @intIncrement, @sdtDate)
    WHEN @chrInterval IN ('weekday', 'dw', 'w') THEN DATEADD(WEEKDAY, @intIncrement, @sdtDate)       
END

-- If testing date is 31
if  DATEPART(DD, @sdttest_date)='31'
BEGIN
SELECT @NewDate =DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,@NewDate)+1,0))
END


else

SET @NewDate=@NewDate   
 

 

RETURN @NewDate   

END

Scalar value function:
ALTER FUNCTION [synprod].[Udf_CAS_CalRAGNoOfDays]
( -- Add the parameters for the function here
@sdtDate SMALLDATETIME
)
RETURNS CHAR(10)
AS

BEGIN
-- Declare the return variable here
DECLARE @RAGDays CHAR(10)
SELECT @RAGDays= DATEDIFF(DAY,GETDATE(),@sdtDate)
-- Return the result of the function
RETURN @RAGDays
END

Scalar value function:
ALTER FUNCTION [synprod].[Udf_CAS_CalFutureDuedt1]
(
-- Add the parameters for the function here
@chrInterval VARCHAR(11),
@intIncrement INT,
@sdtDate SMALLDATETIME
)
RETURNS SMALLDATETIME
AS

BEGIN
-- Declare the return variable here
DECLARE @FutureDuedt smalldatetime
DECLARE @NewDuedt smalldatetime
DECLARE @RAGDays char(10)
SELECT @RAGDays= DATEDIFF(DAY,GETDATE(),@sdtDate)
DECLARE @CountDays INT
DECLARE @autoIncrement INT
SET @autoIncrement=1
IF @chrInterval='wk' and @intIncrement=1
SET @CountDays=10
ELSE
SET @CountDays=15
SET @NewDuedt = @sdtDate
WHILE (@RAGDays<=@CountDays)
BEGIN

SET @NewDuedt=@FutureDuedt
SET @FutureDuedt=[synprod].[Udf_CAS_CalDuedt1](@chrInterval,@intIncrement*@autoIncrement, @sdtDate,@sdtDate)    
SET @RAGDays= DATEDIFF(DAY,GETDATE(),@FutureDuedt)   
SET @autoIncrement=@autoIncrement+1

 
END;   
-- Return the result of the function
IF @NewDuedt is null 
SET @NewDuedt=@sdtDate    
RETURN @NewDuedt   

END

Every row returned has to go through those functions which is brutal. One row goes through the same function twice
CASE WHEN ca.due_date = [synprod].[Udf_cas_calfutureduedt1](f.[datepart], f.[Days],ra.test_date) THEN
synprod.Udf_cas_calduedt1(f.[datepart], f.[Days], ca.due_date, ra.test_date)
ELSE synprod.Udf_cas_calfutureduedt1(f.[datepart], f.[Days], ra.test_date)
END AS due_date,

and this function [synprod].[Udf_cas_calfutureduedt1] is calling [synprod].[Udf_cas_calduedt1].

I would start by removing the functions with some inline sql. Try skipping the views and just calculating what these 2 produce first

synprod.Udf_cas_calduedt1(f.[datepart], f.[Days], ca.due_date, ra.test_date)
synprod.Udf_cas_calfutureduedt1(f.[datepart], f.[Days], ra.test_date)

Then use the results to check ca.duedate. Seems like the columns due_date and rag are the same thing, so you can cut one of them out right there

1 Like

Thanks mike for your reply.
i had tried your suggested changes. but its slow due to scalar value function. is there any other alternative for this date calculation