SQL Server (T-SQL): Avoid call scalar function multiple times

Question

I have a stored procedure which does a SELECT to return some rows. Within the SELECT, I need to check a condition in order to return the correct value for some columns. This condition consists on a scalar function. All times scalar function is called with the same parameter for the row being processed, see below:

SELECT
    Id,
    Name,
    Surname,
    CASE WHEN (dbo.GetNumTravels(Id) >= 50)
        THEN 10
        ELSE 99
    END as Factor1,
    CASE WHEN (dbo.GetNumTravels(Id) >= 50)
        THEN 15
        ELSE -1
    END as Factor2,
    CASE WHEN (dbo.GetNumTravels(Id) >= 50)
        THEN 30
        ELSE 70
    END as Factor3
FROM 
    Employees
WHERE 
    DepartmentId = 100

I am worried about performance, I mean, I do not like to call scalar function dbo.GetNumTravels multiples times, so how to avoid this and only call it once and then used it all the times I need it?

Answer 1

Scalar user defined functions are infamous for poor performance. If you can convert it to an inline table-valued function you can expect to see performance gains.

If you convert your scalar function to an inline table-valued function you can call it once for each row using cross apply() like so:

select
     Id,
     Name,
     Surname,
     case when x.NumTravels >= 50
        then 10
        else 99
     end as Factor1,
     case when x.NumTravels >= 50)
        then 15
        else -1
     end as Factor2,
     case when x.NumTravels >= 50
        then 30
        else 70
     end as Factor3
from Employees
  cross apply dbo.GetNumTravels_itvf(e.Id) x
where DepartmentId = 100

Reference:

• When is a sql function not a function? "If it’s not inline, it’s rubbish." - Rob Farley
• Inline Scalar Functions - Itzik Ben-Gan
• Scalar functions, inlining, and performance: An entertaining title for a boring post - Adam Machanic
• tsql User-Defined Functions: Ten Questions You Were Too Shy To Ask - Robert Sheldon

Answer 2

You can achieve this by using derived table concept, In derived table we once called to function dbo.GetNumTravels(Id) only once and used its output in outer query, this may help to gaining performance at some level by avoiding multiple calls to same function.

SELECT 
       Id,
       Name,
       Surname,
       CASE WHEN (NumTravelsID >= 50)  THEN 10  ELSE 99 END as Factor1,
       CASE WHEN (NumTravelsID >= 50)  THEN 15  ELSE -1 END as Factor2,
       CASE WHEN (NumTravelsID >= 50)  THEN 30  ELSE 70 END as Factor3
FROM (
         SELECT
            Id,
            Name,
            Surname,
            dbo.GetNumTravels(Id) as NumTravelsID
         FROM Employees
         WHERE DepartmentId = 100  
 )M