为什么UDF比子查询慢得多？

我有一个案例,我需要翻译(查找)同一个表中的几个值.我编写它的第一种方法是使用子查询:

SELECT
    (SELECT id FROM user WHERE user_pk = created_by) AS creator,
    (SELECT id FROM user WHERE user_pk = updated_by) AS updater,
    (SELECT id FROM user WHERE user_pk = owned_by) AS owner,
    [name]
FROM asset

因为我使用这个子查询很多(也就是说,我有大约50个带有这些字段的表),我可能需要在子查询中添加更多代码(例如,"AND active = 1")我想我d将这些放入用户定义的函数UDF并使用它.但使用该UDF的性能非常糟糕.

CREATE FUNCTION dbo.get_user ( @user_pk INT )
RETURNS INT
AS BEGIN 
    RETURN ( SELECT id
             FROM   ice.dbo.[user]
             WHERE  user_pk = @user_pk )
END

SELECT dbo.get_user(created_by) as creator, [name]
FROM asset

#1的性能不到1秒.#2的表现大约是30秒......

为什么,或者更重要的是,我有什么方法可以在SQL Server 2008中编码,这样我就不必使用这么多子查询了？

编辑:

只是对这个有用时的更多解释.当我想为用户提供文本时,这个简单的查询(即获取用户ID)会变得更加复杂,因为我必须加入配置文件来获取语言,并与公司一起查看该语言是否应该被提取'而是从那里编辑,并与翻译表,以获得翻译的文本.对于大多数这些查询,性能是可读性和可维护性的次要问题.

1> gbn..：

---

UDF是查询优化器的黑盒子,因此它针对每一行执行.你正在做一个逐行的游标.对于资产中的每一行,在另一个表中查找三次id.当您使用标量或多语句UDF时会发生这种情况(内联UDF只是扩展到外部查询的宏)

关于这个问题的许多文章之一是" 标量函数,内联和表现:一个无聊职位的有趣标题 ".

可以优化子查询以关联和避免逐行操作.

你真正想要的是这个:

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

2019年2月更新

SQL Server 2019开始解决此问题.

---

为了等同于他的原始查询,他实际上需要左连接,而不是内连接,因为嵌套查询可能返回null

---

devzero,join是绝对最快的解决方案,并且对于熟悉t-sql的人来说并不是不可读的.Joins几乎总是比子查询好得多,并且比UDF好得多,应该不惜一切代价避免.

2> LukeH..：

---

正如其他海报所建议的那样,使用连接肯定会给你最好的整体性能.

但是,既然您已经声明不需要保持50-beh类似连接或子查询的头痛,请尝试使用内联表值函数,如下所示:

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
)

您的原始查询将变为类似于:

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

一个内嵌表值函数应该比一个标量函数或者多语句表值函数更好的性能.

性能应该与原始查询大致相同,但是可以在UDF中进行任何未来的更改,使其更易于维护.