Почему 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"), я подумал, что поместите их в пользовательскую функцию 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, чтобы мне не приходилось использовать так много подзапросов?

Редактировать:

Просто немного больше объяснения того, когда это полезно. Этот простой запрос (то есть get userid) становится намного сложнее, когда я хочу получить текст для пользователя, так как мне нужно объединиться с профилем, чтобы получить язык, с компанией, чтобы посмотреть, должен ли язык быть выбранным. ' оттуда вместо этого и с таблицей перевода, чтобы получить переведенный текст. И для большинства этих запросов производительность является второстепенной проблемой для удобочитаемости и удобства обслуживания.

Ответ 1

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

SQL Server 2019 начинает исправлять эту проблему.

Ответ 2

Как и другие плакаты, использование объединений, безусловно, даст вам наилучшую общую производительность.

Однако, поскольку вы заявили, что вам не нужна головная боль для поддержания 50-ичных аналогичных объединений или подзапросов, попробуйте использовать встроенную функцию табличной функции следующим образом:

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, что делает его гораздо более удобным.

Ответ 3

Чтобы получить тот же результат (NULL, если пользователь удален или не активен).

 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)

Ответ 4

Я что-то упустил? Почему это не работает? Вы выбираете только идентификатор, который у вас уже есть в таблице:

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

Кстати, при проектировании вам действительно следует избегать ключевых слов, таких как name, как имена полей.