CODEWITHSUNDEEP
sql-server-2008t-sqluser-defined-functions
Willy
Willy

Reputation: 10650

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

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?

Upvotes: 3

Views: 2229

Answers (3)

etsa
etsa

Reputation: 5060

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

Upvotes: 0

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

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

Upvotes: 1

SqlZim
SqlZim

Reputation: 38063

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:

Upvotes: 3

Related Questions