Подстановка скалярных UDF

Применимо к: SQL Server 2019 (15.x) База данных Azure SQL Управляемый экземпляр Azure SQL Конечная точка аналитики SQL в Microsoft Fabric Хранилище в Microsoft Fabric База данных SQL в Microsoft Fabric

В этой статье представлено встраивание скалярных UDF — одна из функций набора Интеллектуальная обработка запросов в базах данных SQL. Эта функция повышает производительность запросов, использующих скалярные пользовательские функции (UDF), в SQL Server 2019 (15.x) и более поздних версиях.

Скалярные пользовательские функции T-SQL

Определяемые пользователем функции ,реализованные в Transact-SQL и возвращающие одно значение данных, называются скалярными пользовательскими функциями T-SQL. Пользовательские функции T-SQL — это элегантный способ обеспечить повторное использование кода и модульность в разных запросах Transact-SQL. Некоторые вычисления, такие как сложные бизнес-правила, проще выразить в императивной форме UDF. Пользовательские функции (UDF) помогают создавать такую логику без необходимости разбираться в написании SQL-запросов. Дополнительные сведения об определяемых пользователем функциях см. в разделе "Создание определяемых пользователем функций" (ядро СУБД).

Производительность скалярных пользовательских функций

Скалярные определяемые пользователем функции обычно имеют низкую производительность по следующим причинам:

Итеративный вызов. SQL ядро СУБД итеративно вызывает определяемые пользователем функции (UDF), по одному разу для каждого подходящего кортежа. Этот процесс добавляет дополнительные затраты из-за повторного переключения контекста из-за вызова функции. Пользовательские функции, выполняющие запросы Transact-SQL в своем определении, существенно затрагиваются.
Отсутствие затрат. Во время оптимизации ядро СУБД оценивает стоимость только реляционных операторов, но не скалярных операторов. До появления скалярных пользовательских функций (UDF) другие скалярные операторы, как правило, имели низкую стоимость и не требовали оценки стоимости. Достаточно было добавить небольшие затраты ресурсов ЦП для скалярной операции. Существуют сценарии, в которых фактические затраты являются значительными, но оптимизатор по-прежнему недооценивает его.
Интерпретируемое выполнение. Ядро базы данных обрабатывает пользовательские функции (UDF) как пакет инструкций и выполняет их инструкцию за инструкцией. Каждая инструкция компилируется, а скомпилированный план кэшируется. Хотя эта стратегия кэширования экономит некоторое время, избегая перекомпиляции, каждая инструкция выполняется в изоляции. Ядро СУБД не выполняет перекрестные оптимизации.
Последовательное выполнение. SQL Server не поддерживает внутризапросный параллелизм в запросах, вызывающих UDF.

Автоматическое встраивание скалярных функций, определяемых пользователем

Цель функции встраивания скалярных UDF — повысить производительность запросов, вызывающих скалярные пользовательские функции T-SQL, в тех случаях, когда выполнение UDF является основным узким местом.

При использовании функции встраивания UDF ядро СУБД автоматически преобразует скалярные определяемые пользователем функции в скалярные выражения или скалярные подзапросы. Ядро СУБД заменяет эти выражения или вложенные запросы в вызывающем запросе вместо оператора UDF. Оптимизатор запросов оптимизирует эти выражения и вложенные запросы. В результате план запроса больше не имеет определяемого пользователем оператора функции, но вы можете наблюдать за его эффектами в плане, например представлениями или встроенными табличными значениями функций (TVFs).

Автоматическое встраивание скалярных функций, определяемых пользователем, в хранилище данных Microsoft Fabric

В хранилище данных Microsoft Fabric скалярные определяемые пользователем функции (UDF) (в настоящее время в предварительной версии) автоматически встраиваются на этапе компиляции, когда текст функции и вызывающий запрос соответствуют требованиям для встраивания. Дополнительные сведения см. в разделе CREATE FUNCTION и встраивание скалярных UDF.

Примеры

В примерах этого раздела используется база данных теста TPC-H. Дополнительные сведения см. на домашней странице TPC-H.

А. Скалярная UDF с одним оператором

Обратите внимание на следующий запрос.

SELECT L_SHIPDATE,
       O_SHIPPRIORITY,
       SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT))
FROM LINEITEM
     INNER JOIN ORDERS
         ON O_ORDERKEY = L_ORDERKEY
GROUP BY L_SHIPDATE, O_SHIPPRIORITY
ORDER BY L_SHIPDATE;

Он вычисляет сумму цен на позиции с учетом скидок и выводит результаты с группировкой по дате и приоритету отгрузки. Выражение L_EXTENDEDPRICE *(1 - L_DISCOUNT) служит для расчета цены позиции со скидкой. Выделение таких формул в отдельные функции позволяет повысить модульность кода и упрощает его повторное использование.

CREATE FUNCTION dbo.discount_price
(
    @price DECIMAL (12, 2),
    @discount DECIMAL (12, 2)
)
RETURNS DECIMAL (12, 2)
AS
BEGIN
    RETURN @price * (1 - @discount);
END

Теперь запрос можно изменить так, чтобы он вызывал эту UDF.

SELECT L_SHIPDATE,
       O_SHIPPRIORITY,
       SUM(dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT))
FROM LINEITEM
     INNER JOIN ORDERS
         ON O_ORDERKEY = L_ORDERKEY
GROUP BY L_SHIPDATE, O_SHIPPRIORITY
ORDER BY L_SHIPDATE;

Запрос с UDF работает медленно по причинам, изложенным выше. При встраивании скалярного UDF скалярное выражение в теле UDF подставляется непосредственно в запрос. Результаты выполнения этого запроса показаны в следующей таблице:

Query	Запрос без UDF	Запрос с UDF (без инлайнинга)	Запрос со встраиванием скалярной пользовательской функции
`Execution time`	1,6 секунды	29 минут 11 секунд	1,6 секунды

Эти показатели получены для базы данных CCI размером 10 ГБ (использующей схему TPC-H), которая размещена на компьютере с двумя процессорами (12 ядер), 96 ГБ ОЗУ и дисками SSD. В них было учтено время компиляции и выполнения с холодным кэшем процедур и буферным пулом. Использовалась конфигурация по умолчанию. Другие индексы не создавались.

В. Скалярная UDF с несколькими операторами

Можно также встраивать скалярные пользовательские функции с несколькими инструкциями T-SQL, такими как присваивание переменных и условные ветвления. Рассмотрим приведённую ниже скалярную функцию UDF, которая по ключу клиента определяет категорию обслуживания этого клиента. Он относит клиента к категории, предварительно вычисляя общую стоимость всех заказов, оформленных клиентом, с помощью SQL-запроса. Затем на основе общей стоимости определяется категория посредством логики IF (...) ELSE.

CREATE OR ALTER FUNCTION dbo.customer_category (@ckey INT)
RETURNS CHAR (10)
AS
BEGIN
    DECLARE @total_price AS DECIMAL (18, 2);
    DECLARE @category AS CHAR (10);
    SELECT @total_price = SUM(O_TOTALPRICE)
    FROM ORDERS
    WHERE O_CUSTKEY = @ckey;
    IF @total_price < 500000
        SET @category = 'REGULAR';
    ELSE
        IF @total_price < 1000000
            SET @category = 'GOLD';
        ELSE
            SET @category = 'PLATINUM';
    RETURN @category;
END

Теперь рассмотрим запрос, который вызывает эту UDF.

SELECT C_NAME,
       dbo.customer_category(C_CUSTKEY)
FROM CUSTOMER;

План выполнения для этого запроса в SQL Server 2017 (14.x) (уровень совместимости 140 и более ранние версии) выглядит следующим образом:

Как показано в плане, SQL Server принимает следующую базовую стратегию: для каждого кортежа в таблице CUSTOMER вызывать функцию UDF и выводить результаты. Такой подход примитивен и неэффективен. С помощью встраивания такие пользовательские функции можно преобразовать в эквивалентные скалярные подзапросы, которые вызывающий запрос подставляет вместо этих UDF.

Для того же запроса план с вложенной функцией UDF выглядит следующим образом.

Как упоминалось ранее, план запроса больше не имеет определяемого пользователем оператора функции, но теперь его эффекты можно увидеть в плане, например представления или встроенные TVFs. Ниже приведены некоторые ключевые наблюдения из предыдущего плана:

SQL Server определяет неявное соединение между CUSTOMER и ORDERS делает его явным через оператор соединения.
SQL Server также определяет неявное GROUP BY O_CUSTKEY on ORDERS и использует IndexSpool + StreamAggregate для его реализации.
Параллелизм теперь применяется для всех операторов.

В зависимости от сложности логики в UDF результирующий план запроса также может быть более большим и более сложным. Как видно, операции внутри UDF теперь стали прозрачными для оптимизатора запросов, поэтому он может оценивать стоимость этих операций и оптимизировать их. Кроме того, поскольку UDF больше не входит в план, итеративный вызов UDF заменяется планом, который полностью исключает накладные расходы на вызов функции.

Требования к скалярным UDF, допускающим встраивание

Скалярный T-SQL UDF можно встроить, если определение функции использует разрешенные конструкции, а функция используется в контексте, который позволяет встраивание:

Все следующие условия определения UDF должны иметь значение true:

UDF записывается с помощью следующих конструкций:
- DECLARE, SET: объявление переменных и присвоение им значений;
- SELECT: SQL-запрос с одиночным/множественным присваиванием переменных ¹.
- IF / ELSE: ветвление с произвольными уровнями вложенности;
- RETURN: одна инструкция return или несколько; Начиная с SQL Server 2019 (15.x) CU5, функция UDF может считаться пригодной для встраивания, только если она содержит одну инструкцию RETURN ⁶.
- UDF: вызовы вложенной или рекурсивной функции ².
- прочее: реляционные операции, такие как EXISTS и IS NULL.
UDF не вызывает встроенную функцию, которая зависит от времени (например GETDATE()) или имеет побочные эффекты ³ (например NEWSEQUENTIALID(), ).
UDF использует EXECUTE AS CALLER предложение (поведение по умолчанию, если EXECUTE AS предложение не указано).
UDF не ссылается на переменные таблицы или параметры с табличным значением.
UDF не компилируется в машинный код (взаимодействие поддерживается).
UDF не ссылается на определяемые пользователем типы.
В UDF ⁹ не добавляются подписи.
UDF не является функцией секционирования.
UDF не содержит ссылки на общие выражения таблиц (CTEs).
UDF не содержит ссылок на встроенные функции, которые могут изменить результаты при вложении (например @@ROWCOUNT, ⁴).
UDF не содержит агрегатных функций, передаваемых в качестве параметров скалярной UDF ⁴.
UDF не ссылается на встроенные представления (например OBJECT_ID, ⁴).
UDF не ссылается на методы ^{XML 5}.
UDF не содержит SELECT с ORDER BY без предложения TOP 1⁵.
UDF не содержит запроса SELECT, который выполняет присваивание с использованием предложения ORDER BY (например, SELECT @x = @x + 1 FROM table1 ORDER BY col1) ⁵.
UDF не содержит несколько инструкций ^{RETURN 6}.
UDF не ссылается на функцию STRING_AGG⁶.
UDF не ссылается на удаленные таблицы ⁷.
UDF не ссылается на зашифрованные столбцы ⁸.
UDF не содержит ссылок на WITH XMLNAMESPACES⁸.
Если определение UDF выполняется в тысячах строк кода, SQL Server может не встраивать его.

¹SELECT с переменным накоплением или агрегированием не поддерживается для встраивания (например, SELECT @val += col1 FROM table1).

² Рекурсивные UDF встраиваются только до определенной глубины.

³ Зависимыми от времени являются такие встроенные функции, результаты которых зависят от текущего системного времени. Встроенная функция, которая может обновить некоторое внутреннее глобальное состояние, является примером функции с побочными эффектами. Такие функции при каждом вызове возвращают разные результаты в зависимости от внутреннего состояния.

^{Ограничение 4} , добавленное в SQL Server 2019 (15.x) CU 2

^{Ограничение 5} , добавленное в SQL Server 2019 (15.x) CU 4

^{Ограничение 6} , добавленное в SQL Server 2019 (15.x) CU 5

^{Ограничение 7} , добавленное в SQL Server 2019 (15.x) CU 6

^{Ограничение 8} , добавленное в SQL Server 2019 (15.x) CU 11

⁹ Поскольку после создания UDF подписи можно добавлять и удалять, решение о том, следует ли встраивать скалярную UDF, принимается при компиляции запроса, ссылающегося на неё. Например, большинство системных функций подписано с помощью сертификатов. Для определения подписанных объектов можно использовать sys.crypt_properties.

Все перечисленные ниже требования контекста выполнения должны быть истинными:

UDF не используется в ORDER BY предложении.
Запрос, вызывающий скалярную UDF, не содержит ссылки на вызов скалярной UDF в предложении GROUP BY.
Запрос, вызывающий скалярную функцию UDF в списке SELECT с предложением DISTINCT, не содержит предложения ORDER BY.
UDF не вызывается из инструкции ^{RETURN 1}.
Запрос, вызывающий UDF, не имеет общих табличных выражений (CTEs) ³.
Запрос на вызов UDF не использует GROUPING SETSCUBEили ROLLUP².
Запрос, вызывающий UDF, не содержит переменную, используемую в качестве параметра UDF для назначения (например, SELECT @y = 2@x = UDF(@y)^{) 2}.
Вы не используете UDF в вычисляемом столбце или определении ограничения проверки.

¹ Ограничение, добавленное в SQL Server 2019 (15.x) CU 5

^{Ограничение 2} , добавленное в SQL Server 2019 (15.x) CU 6

^{Ограничение 3} , добавленное в SQL Server 2019 (15.x) CU 11

Сведения о последних исправлениях, связанных со встраиванием скалярных UDF T-SQL, а также об изменениях в сценариях, в которых возможно встраивание, см. в статье базы знаний: ИСПРАВЛЕНИЕ: проблемы со встраиванием скалярных UDF в SQL Server 2019.

Проверьте, можно ли встраить UDF

Для каждой скалярной UDF-функции T-SQL представление каталога sys.sql_modules включает свойство с именем is_inlineable, которое указывает, может ли UDF быть встроена.

Свойство is_inlineable определяется на основе конструкций, содержащихся в определении UDF. Он не проверяет, действительно ли UDF может быть встроена в код на этапе компиляции. Дополнительные сведения см. в разделе Условия встраивания.

Значение 1 указывает, что UDF может быть встроена, а 0 — в противном случае. Это свойство также имеет значение для всех встроенных 1 ТВФ. Для всех остальных модулей значение равно 0.

Если скалярная UDF может быть встроена, это не означает, что она всегда встраивается. SQL Server решает отдельно для каждого запроса и каждой UDF, следует ли встроить UDF. Ознакомьтесь со списками требований, приведенных ранее в этой статье.

SELECT b.name,
       b.type_desc,
       a.is_inlineable
FROM sys.sql_modules AS a
     INNER JOIN sys.objects AS b
         ON a.object_id = b.object_id
WHERE b.type IN ('IF', 'TF', 'FN');

Проверьте, произошло ли встраивание

Если все предварительные условия соблюдены и SQL Server решает встроить функцию, он преобразует функцию UDF в реляционное выражение. По плану запроса можно определить, произошло ли встраивание:

XML плана не содержит узла XML <UserDefinedFunction> для UDF, которая успешно встроена.
Генерируются определённые события Extended Events.

Включение встраивания скалярных UDF

Можно автоматически сделать нагрузки пригодными для встраивания скалярных UDF, установив для базы данных уровень совместимости 150. Это можно сделать с помощью Transact-SQL. Например:

ALTER DATABASE [WideWorldImportersDW]
    SET COMPATIBILITY_LEVEL = 150;

Помимо этого шага, для использования этой функции не требуется вносить какие-либо изменения в пользовательские функции или запросы.

Отключить встраивание скалярных UDF без изменения уровня совместимости

Вы можете отключить скалярную структуру UDF в области базы данных, инструкции или UDF, сохраняя уровень совместимости базы данных 150 и выше. Чтобы отключить скалярную структуру UDF в области базы данных, выполните следующую инструкцию в контексте применимой базы данных:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Чтобы повторно включить скалярную структуру UDF для базы данных, выполните следующую инструкцию в контексте применимой базы данных:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

Если этот параметр ONзадан, он отображается как включенный в sys.database_scoped_configurations.

Вы также можете отключить встраивание скалярных пользовательских функций для определенного запроса, назначив DISABLE_TSQL_SCALAR_UDF_INLINING в качестве указания запроса USE HINT.

Указание запроса USE HINT имеет приоритет над конфигурацией, областью действия которой является база данных, или уровнем совместимости.

Например:

SELECT L_SHIPDATE,
       O_SHIPPRIORITY,
       SUM(dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT))
FROM LINEITEM
     INNER JOIN ORDERS
         ON O_ORDERKEY = L_ORDERKEY
GROUP BY L_SHIPDATE, O_SHIPPRIORITY
ORDER BY L_SHIPDATE
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));

Вы также можете отключить встраивание скалярной UDF для конкретной UDF с помощью предложения INLINE в инструкции CREATE FUNCTION или ALTER FUNCTION. Например:

CREATE OR ALTER FUNCTION dbo.discount_price
(
    @price DECIMAL (12, 2),
    @discount DECIMAL (12, 2)
)
RETURNS DECIMAL (12, 2)
WITH INLINE = OFF
AS
BEGIN
    RETURN @price * (1 - @discount);
END

После выполнения предыдущей инструкции этот UDF никогда не встраивается в любой запрос, вызывающий его. Чтобы повторно включить встраивание для этой UDF, выполните следующую инструкцию:

CREATE OR ALTER FUNCTION dbo.discount_price
(
    @price DECIMAL (12, 2),
    @discount DECIMAL (12, 2)
)
RETURNS DECIMAL (12, 2)
WITH INLINE = ON
AS
BEGIN
    RETURN @price * (1 - @discount);
END

Условие INLINE необязательно. Если предложение INLINE не указано, оно автоматически принимает значение ON или OFF в зависимости от того, может ли UDF быть встроена. Если указать INLINE = ON , но определяемая пользователем функция не имеет права на встраивание, возникает ошибка.

Замечания

Как описано в этой статье, встраивание скалярных UDF преобразует запрос со скалярными UDF в запрос с эквивалентным скалярным вложенным запросом. Из-за этого преобразования некоторые различия в поведении могут возникнуть в следующих сценариях:

Встраивание приводит к другому хэшу запроса для одного и того же текста запроса.
Некоторые предупреждения в инструкциях внутри UDF (например, о делении на ноль), которые ранее могли быть скрыты, могут появиться из-за встраивания.
Указания на присоединение на уровне запроса больше не могут быть допустимыми, так как встраивание может привести к новым соединениям. Вместо этого необходимо использовать локальные подсказки присоединения.
Нельзя индексировать представления, ссылающиеся на встроенные скалярные пользовательские функции. Чтобы создать индекс для таких представлений, отключите встраивание для соответствующих пользовательских функций.
В поведении динамического маскирования данных могут быть некоторые различия при встраивании UDF.

В некоторых ситуациях (в зависимости от логики, реализованной в UDF) встраивание может быть более консервативным при маскировании выходных столбцов. В сценариях, где столбцы, на которые ссылаются в UDF, не являются выходными столбцами, они не маскируются.
Если UDF ссылается на встроенные функции, такие как SCOPE_IDENTITY(), @@ROWCOUNT или @@ERROR, значение, возвращаемое встроенной функцией, изменяется при встраивании. Это изменение поведения связано с тем, что встраивание изменяет область видимости операторов внутри UDF. Начиная с SQL Server 2019 (15.x) CU2, встраивание блокируется, если UDF ссылается на определенные встроенные функции (например @@ROWCOUNT).
Если вы присваиваете переменной результат встроенной определяемой пользователем функции (UDF) и также используете её в качестве index_column_name в разделе FORCESEEKПодсказки запроса (Transact-SQL), это приводит к ошибке 8622. Эта ошибка указывает, что обработчик запросов не мог создать план запроса из-за указаний, определенных в запросе.

Обратная связь

Были ли сведения на этой странице полезными?

Last updated on 2026-06-29