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?

Why are you asking for this in the first place? What does GetNumTravels do and why is it slow? If you want to find each employee's travels you should be using joins or subqueries in the WHERE clause, not individual queries in the SELECT clause. You could use a view instead of a function and join it with Employees — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 7, 2017 at 12:53

Community · Accepted Answer · 2018-11-13 23:26:50Z

3

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:

edited Nov 13, 2018 at 23:26

CommunityBot

11 silver badge

answered Jun 7, 2017 at 12:47

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stepan Michalek Over a year ago

As a quick fix, if you do not want to convert your function from a scalar into a table-valued one, you can do "(SELECT dbo.SomeScalarFunc(a, b, c) as NumTravels) NumTravels" inside the FROM clause (or CROSS/OUTER APPLY). I do not know if this has any drawbacks in optimalization. Though it works and improved my query performance by limiting the number of function calls.

Jaydip Jadhav · Accepted Answer · 2017-06-07 12:50:53Z

1

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

edited Jun 7, 2017 at 12:50

answered Jun 7, 2017 at 12:42

Jaydip Jadhav

12.3k6 gold badges26 silver badges41 bronze badges

Comments

etsa · Accepted Answer · 2017-06-07 12:51:18Z

0

I am not sure about performances (anyway, do your tests considering other answers too), but I would like to test this. I tried to reduce use of function and use of CASE too. Pls let me know

SELECT A.*
    , 10*F0+99*~F0 AS FACTOR1
    , 15*F0-1*~F0 AS FACTOR2
    , 30*F0+70*~F0 AS FACTOR3
FROM (
    SELECT
     Id,
     Name,
     Surname,
    CAST(CASE WHEN (dbo.GetNumTravels(Id) >= 50) THEN  1  ELSE 0 END AS BIT) AS F0     
    FROM Employees
    WHERE DepartmentId = 100
     ) A

answered Jun 7, 2017 at 12:51

etsa

5,0501 gold badge9 silver badges18 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related