Function Consuming High CPU in SQL Server 2017-CodePudding

I have came across one function on our client environment which is consuming CPU.

Below is the query.

CREATE function [dbo].[fnEncClaimType](@in_ApptOrEncId numeric(8),@in_bool_IsApptId numeric(2)) 
returns varchar(20)
as
begin 
    declare @returnVal varchar(20)
    set @returnVal = ''
    if @in_bool_IsApptId = 1 begin 
        SELECT TOP 1 @returnVal = CASE 
        WHEN BLH_PATINV_TEXT = 'AUTO' THEN 'Auto Accident' 
        WHEN BLH_PATINV_TEXT = 'EMPLOYER' THEN 'Employer' 
        WHEN BLH_PATINV_TEXT = 'PATCLM' THEN 'Self Pay' 
        WHEN BLH_PATINV_TEXT = 'PATINV' THEN 'Penalty Invoice' 
        WHEN BLH_PATINV_TEXT = 'PI' THEN 'Penalty Invoice' 
        WHEN BLH_PATINV_TEXT = 'INJURY' THEN 'Personal Accident' 
        WHEN BLH_PATINV_TEXT = 'PROF' THEN 'Professional' 
        WHEN BLH_PATINV_TEXT = 'TPA' THEN 'TPA' 
        WHEN BLH_PATINV_TEXT = 'UB04' THEN 'Institutional' 
        WHEN BLH_PATINV_TEXT = 'WORKCOMP' THEN 'Work Comp' 
        WHEN BLH_PATINV_TEXT = 'DMERC' THEN 'DMERC' ELSE '' 
        END FROM TRN_BILLING_HEAD,TRN_ENCOUNTERS 
        WHERE BLH_ENC_ID = ENC_ID 
        AND (BLH_APPT_ID = @in_ApptOrEncId OR ENC_APPT_ID = @in_ApptOrEncId) 
        AND BLH_BOOL_INACTIVE = 0 
        ORDER BY BLH_ID end 
    else begin 
        SELECT TOP 1 @returnVal = CASE 
        WHEN BLH_PATINV_TEXT = 'AUTO' THEN 'Auto Accident'
        WHEN BLH_PATINV_TEXT = 'EMPLOYER' THEN 'Employer' 
        WHEN BLH_PATINV_TEXT = 'PATCLM' THEN 'Self Pay' 
        WHEN BLH_PATINV_TEXT = 'PATINV' THEN 'Penalty Invoice'
        WHEN BLH_PATINV_TEXT = 'PI' THEN 'Penalty Invoice' 
        WHEN BLH_PATINV_TEXT = 'INJURY' THEN 'Personal Accident' 
        WHEN BLH_PATINV_TEXT = 'PROF' THEN 'Professional' 
        WHEN BLH_PATINV_TEXT = 'TPA' THEN 'TPA' 
        WHEN BLH_PATINV_TEXT = 'UB04' THEN 'Institutional' 
        WHEN BLH_PATINV_TEXT = 'WORKCOMP' THEN 'Work Comp' 
        WHEN BLH_PATINV_TEXT = 'DMERC' THEN 'DMERC' ELSE '' END 
        FROM TRN_BILLING_HEAD 
        WHERE BLH_ENC_ID = @in_ApptOrEncId AND BLH_BOOL_INACTIVE = 0 
        ORDER BY BLH_ID
    end

    return @returnVal 
end;

I had checked the execution plan to check for indexes and it is using appropriate indexes performing index seek.

Can anyone please suggest any query level changes required to optimise this. I am not a developer hence finding it difficult to check for any query level optimisation.

CodePudding user response：

Scalar functions are slow, inline functions are fast. Let's start with that. You can rewrite your scalar udf as an inline table valued function that returns one row/one column:

CREATE function [dbo].[fnEncClaimType_itvf](@in_ApptOrEncId numeric(8),@in_bool_IsApptId numeric(2)) 
returns table as return
(
    SELECT TOP 1 returnVal = CASE 
        WHEN BLH_PATINV_TEXT = 'AUTO' THEN 'Auto Accident' 
        WHEN BLH_PATINV_TEXT = 'EMPLOYER' THEN 'Employer' 
        WHEN BLH_PATINV_TEXT = 'PATCLM' THEN 'Self Pay' 
        WHEN BLH_PATINV_TEXT = 'PATINV' THEN 'Penalty Invoice' 
        WHEN BLH_PATINV_TEXT = 'PI' THEN 'Penalty Invoice' 
        WHEN BLH_PATINV_TEXT = 'INJURY' THEN 'Personal Accident' 
        WHEN BLH_PATINV_TEXT = 'PROF' THEN 'Professional' 
        WHEN BLH_PATINV_TEXT = 'TPA' THEN 'TPA' 
        WHEN BLH_PATINV_TEXT = 'UB04' THEN 'Institutional' 
        WHEN BLH_PATINV_TEXT = 'WORKCOMP' THEN 'Work Comp' 
        WHEN BLH_PATINV_TEXT = 'DMERC' THEN 'DMERC' ELSE '' 
        END 
    FROM TRN_BILLING_HEAD,TRN_ENCOUNTERS 
    WHERE @in_bool_IsApptId = 1
      AND BLH_ENC_ID = ENC_ID 
      AND (BLH_APPT_ID = @in_ApptOrEncId OR ENC_APPT_ID = @in_ApptOrEncId) 
      AND BLH_BOOL_INACTIVE = 0 
    ORDER BY BLH_ID
)
UNION ALL
    SELECT TOP 1 returnVal = CASE 
      WHEN BLH_PATINV_TEXT = 'AUTO' THEN 'Auto Accident'
      WHEN BLH_PATINV_TEXT = 'EMPLOYER' THEN 'Employer' 
      WHEN BLH_PATINV_TEXT = 'PATCLM' THEN 'Self Pay' 
      WHEN BLH_PATINV_TEXT = 'PATINV' THEN 'Penalty Invoice'
      WHEN BLH_PATINV_TEXT = 'PI' THEN 'Penalty Invoice' 
      WHEN BLH_PATINV_TEXT = 'INJURY' THEN 'Personal Accident' 
      WHEN BLH_PATINV_TEXT = 'PROF' THEN 'Professional' 
      WHEN BLH_PATINV_TEXT = 'TPA' THEN 'TPA' 
      WHEN BLH_PATINV_TEXT = 'UB04' THEN 'Institutional' 
      WHEN BLH_PATINV_TEXT = 'WORKCOMP' THEN 'Work Comp' 
      WHEN BLH_PATINV_TEXT = 'DMERC' THEN 'DMERC' ELSE '' END 
    FROM TRN_BILLING_HEAD 
    WHERE BLH_ENC_ID = @in_ApptOrEncId AND BLH_BOOL_INACTIVE = 0 
    ORDER BY BLH_ID
GO

This:

SELECT dbo.fnEncClaimType(1,2);

Becomes:

SELECT returnValue FROM dbo.fnEncClaimType_itvf(1,2);

This:

SELECT dbo.fnEncClaimType(1,2)
FROM   <some table>

Becomes:

SELECT      cl.returnValue 
FROM        <some table>
CROSS APPLY dbo.fnEncClaimType_itvf(1,2) AS cl;