[ACCEPTED]-Why is a UDF so much slower than a subquery?-user-defined-functions

Accepted answer
Score: 35

The UDF is a black box to the query optimiser 12 so it's executed for every row. You are 11 doing a row-by-row cursor. For each row 10 in an asset, look up an id three times in 9 another table. This happens when you use 8 scalar or multi-statement UDFs (In-line 7 UDFs are simply macros that expand into 6 the outer query)

One of many articles on 5 the problem is "Scalar functions, inlining, and performance: An entertaining title for a boring post".

The sub-queries 4 can be optimised to correlate and avoid 3 the row-by-row operations.

What you really 2 want is this:

SELECT
   uc.id AS creator,
   uu.id AS updater,
   uo.id AS owner,
   a.[name]
FROM
    asset a
    JOIN
    user uc ON uc.user_pk = a.created_by
    JOIN
    user uu ON uu.user_pk = a.updated_by
    JOIN
    user uo ON uo.user_pk = a.owned_by

Update Feb 2019

SQL Server 2019 1 starts to fix this problem.

Score: 13

As other posters have suggested, using joins 12 will definitely give you the best overall 11 performance.

However, since you've stated 10 that that you don't want the headache of 9 maintaining 50-ish similar joins or subqueries, try 8 using an inline table-valued function as 7 follows:

CREATE FUNCTION dbo.get_user_inline (@user_pk INT)
RETURNS TABLE AS
RETURN
(
    SELECT TOP 1 id
    FROM ice.dbo.[user]
    WHERE user_pk = @user_pk
        -- AND active = 1
)

Your original query would then become 6 something like:

SELECT
    (SELECT TOP 1 id FROM dbo.get_user_inline(created_by)) AS creator,
    (SELECT TOP 1 id FROM dbo.get_user_inline(updated_by)) AS updater,
    (SELECT TOP 1 id FROM dbo.get_user_inline(owned_by)) AS owner,
    [name]
FROM asset

An inline table-valued function should have better performance 5 than either a scalar function or a multistatement 4 table-valued function.

The performance should 3 be roughly equivalent to your original query, but 2 any future changes can be made in the UDF, making 1 it much more maintainable.

Score: 2

To get the same result (NULL if user is 1 deleted or not active).

 select 
    u1.id as creator,
    u2.id as updater,
    u3.id as owner,
    [a.name]
 FROM asset a
        LEFT JOIN user u1 ON (u1.user_pk = a.created_by AND u1.active=1) 
        LEFT JOIN user u2 ON (u2.user_pk = a.created_by AND u2.active=1) 
        LEFT JOIN user u3 ON (u3.user_pk = a.created_by AND u3.active=1) 
Score: 0

Am I missing something? Why can't this work? You 4 are only selecting the id which you already 3 have in the table:

select created_by as creator, updated_by as updater, 
owned_by as owner, [name]
from asset

By the way, in designing 2 you really should avoid keywords, like name, as 1 field names.

More Related questions