Function Efficiency

Question

I am a neophyte to creating stored procedures and functions and I just can't figure out why one of these versions runs so much faster than the other. This is a function that just returns a string with a description when called. The original function relies on supplying about 10 variables (Version running in about 4 seconds). I wanted to cut that down to a single variable (version running long).

The code below the declaration of the variables is identical, the only difference is that I'm attempting to pull the variables from the appropriate within the function itself rather than having to supply them on the query side.

i.e. dbo.cf_NoRateReason(V1) as ReasonCode rather than dbo.cf_NoRateReason(V1,V2,V3,V4,V5,V6,V7,V8,V9,V10,V11,V12)

I apologize up front if I am not supplying enough information, as I said, new to functions/stored procedures.

This version runs in about 2.5 minutes to run

    declare @Agencyid int
    declare @ServiceCode varchar(10)
    declare @Mod1 varchar(2)=null
    declare @Mod2 varchar(2)=null
    declare @Mod3 varchar(2)=null
    declare @Mod4 varchar(2)=null
    declare @POS int
    declare @ServiceDate datetime
    declare @ProvType varchar(1)
    declare @PayerID int
    declare @BirthDate datetime
    declare @RenderingStaffID int 
    declare @SupervisingStaffID int 
    Select @Agencyid=s.agencyid, @ServiceCode = ServiceCode, 
           @Mod1 = ModifierCodeId, @Mod2 = ModifierCodeId2, 
           @Mod3 = ModifierCodeId3, @Mod4 = ModifierCodeId4,
           @POS=PlaceOfServiceId, @ServiceDate = ServiceDate, 
           @RenderingStaffId=isnull(dbo.GetProviderStaffId('S',s.ServiceTransactionId,'82'),0),
           @SupervisingStaffId=isnull(dbo.GetProviderStaffId('C',ClaimId,'DQ'),0),
           @ProvType=s.servicetype, @Payerid=pmt.payerid,
           @BirthDate=i.birthdate
      From ServiceTransaction s
            join individual i on s.servicetransactionid = i.individualid
            join pmtadjdetail pmt on s.servicetransactionid = pmt.servicetransactionid

    declare @Result Varchar(100) = ''
    declare @Age int = dbo.getageatservicedate(@birthdate, @ServiceDate)
    declare @ModString varchar(8) = dbo.sortmodifiers(@Mod1, @Mod2, @Mod3, @Mod4)
    declare @DirectSupervision int = (iif(@Mod1 in ('U1','U6','U7','U9','UA') 
        or @Mod2 in ('U1','U6','U7','U9','UA') 
        or @Mod3 in ('U1','U6','U7','U9','UA')
        or @Mod4 in ('U1','U6','U7','U9','UA'),1,0))

'************************************************************************************'
'This version takes about 4 seconds to run'
'************************************************************************************'
begin
    declare @Result Varchar(100) = ''
    declare @Age int = dbo.getageatservicedate(@birthdate, @ServiceDate)
    declare @RenderingStaffID int = dbo.getstaffid(@STID,'DQ')
    declare @SupervisingStaffID int = dbo.getstaffid(@STID,'82')
    declare @ModString varchar(8) = dbo.sortmodifiers(@Mod1, @Mod2, @Mod3, @Mod4)
    declare @DirectSupervision int = (iif(@Mod1 in ('U1','U6','U7','U9','UA') 
        or @Mod2 in ('U1','U6','U7','U9','UA') 
        or @Mod3 in ('U1','U6','U7','U9','UA')
        or @Mod4 in ('U1','U6','U7','U9','UA'),1,0))

Answer 1

This kind of falls under "typo" or simple oversight, but....

When you see that big of a performance difference, for no discernible reason (those functions were used in the original version as well), that is usually when you need to start look for these kinds of mistakes: typos, missing conditions, incorrect conditions from leaning too hard on intellisense/code-completion, etc...

When replacing multiple parameters with one that can used to retrieve the others automatically, always make sure to actually use that parameter.

The version you listed first has no filter (no WHERE clause) on the SELECT it uses to get the "parameter" values it is normally passed. You're effectively getting the entire join resultset, with the cost of the function calls for every result row, and only taking last result's values.

Answer 2

You are correct - the only difference is using the function. Please see similar questions where this has been addressed.

In short, functions are going to be performed on a row-by-row basis whereas code on the query side is going to have other options with no overhead calls to the function. You may be able to use a scalar function with schema binding and nulls return nulls for better performance.

Additional consideration for the schema plan would be valuable. There are also joins and other embedded logics here that aren't clear without sample data.