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

Статья
09/21/2024

Область применения: SQL Server 2019 (15.x) База данных SQL Azure Управляемый экземпляр SQL Azure

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

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

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

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

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

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

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

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

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

Примеры

В примерах этого раздела используется база данных теста 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

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

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 заменяется непосредственно в запросе. Результаты выполнения этого запроса показаны в следующей таблице:

Запрос:	Запрос без пользовательской функции	Запрос с пользовательской функцией (без встраивания)	Запрос со встраиванием скалярной пользовательской функции
Время выполнения:	1,6 секунды	29 минут 11 секунд	1,6 секунды

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

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

Скалярные пользовательские функции, которые реализуются с помощью нескольких инструкций T-SQL, таких как присвоение значений переменным и условное ветвление, также могут встраиваться. Рассмотрим приведенную ниже скалярную пользовательскую функцию, которая на основе ключа клиента определяет для него категорию обслуживания. Для этого она сначала вычисляет общую стоимость всех заказов, размещенных клиентом, с помощью 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

Теперь рассмотрим запрос, вызывающий эту пользовательскую функцию.

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

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

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

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

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

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

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

Встроенные скалярные требования К UDF

Скалярный UDF T-SQL может быть вложен, если все из следующих условий имеют значение true:

UDF записывается с помощью следующих конструкций:
- DECLARE, SET: объявление переменных и присвоение им значений;
- SELECT: SQL-запрос с однократными назначениями ^{переменных 1}.
- 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 в предложении GROUP BY .
Запрос, вызывающий скалярную UDF в списке выбора с DISTINCT предложением, не имеет ORDER BY предложения.
UDF не используется в ORDER BY предложении.
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 не вызывается из инструкции ^{RETURN 6}.
UDF не ссылается на функцию STRING_AGG ⁶.
UDF не ссылается на удаленные таблицы ⁷.
Запрос на вызов UDF не использует GROUPING SETSCUBEили ROLLUP ⁷.
Запрос на вызов UDF не содержит переменную, которая используется в качестве параметра UDF для назначения (например, SELECT @y = 2@x = UDF(@y)) ⁷.
UDF не ссылается на зашифрованные столбцы ⁸.
UDF не содержит ссылок на WITH XMLNAMESPACES ⁸.
Запрос, вызывающий UDF, не имеет общих выражений таблиц (CTEs) ⁸.

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

² Рекурсивные определяемые пользователем функции встраиваются только в определенную глубину.

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

^{Ограничение 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 T-SQL см. в статье базы знаний: ИСПРАВЛЕНИЕ: скалярные проблемы с подстраиванием UDF в SQL Server 2019.

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

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

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

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

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

Если определение UDF выполняется в тысячах строк кода, SQL Server может не встраивать его.
Вызов UDF в предложении GROUP BY не встраиваются. Решение принимается при компиляции запроса, ссылающегося на скалярную пользовательскую функцию.
Пользовательская функция подписана с помощью сертификата. Так как подписи можно добавлять и удалять после создания UDF, решение о том, следует ли встраивать или не выполнять при компиляции скалярного UDF запроса. Например, большинство системных функций подписано с помощью сертификатов. Для определения подписанных объектов можно использовать sys.crypt_properties.
```
SELECT *
FROM sys.crypt_properties AS cp
     INNER JOIN sys.objects AS o
         ON cp.major_id = o.object_id;
```

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

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

Xml-код плана не имеет <UserDefinedFunction> XML-узла для UDF, который успешно встраиваются.
Создается ряд событий XEvent.

Включение скалярной встраивание UDF

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

ALTER DATABASE [WideWorldImportersDW]
    SET COMPATIBILITY_LEVEL = 150;

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

Отключение скалярной подстраивание UDF без изменения уровня совместимости

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

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

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

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'));

Встраивание скалярных пользовательских функций можно также отключить для определенной пользовательской функции с помощью предложения 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 никогда не встраивается в любой запрос, вызывающий его. Чтобы повторно включить встраивание для пользовательской функции, выполните следующую инструкцию.

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 ссылается на встроенные функции, такие как SCOPE_IDENTITY(), @@ROWCOUNTили @@ERRORзначение, возвращаемое встроенной функцией, изменяется с встраиванием. Связано это с тем, что встраивание меняет область инструкций внутри пользовательской функции. Начиная с SQL Server 2019 (15.x) CU2, встраивание блокируется, если UDF ссылается на определенные встроенные функции (например @@ROWCOUNT).
Если переменная назначается результатом вложенной UDF, а также используется в качестве index_column_name в указаниях запросов FORCESEEK, это приводит к ошибке Msg 8622, указывающей, что обработчик запросов не может создать план запроса из-за указаний, определенных в запросе.

Поделиться через

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

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

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

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

Примеры

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

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

Встроенные скалярные требования К UDF

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

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

Включение скалярной встраивание UDF

Отключение скалярной подстраивание UDF без изменения уровня совместимости

Замечания

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

Дополнительные ресурсы

Поделиться через

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

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

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

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

Примеры

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

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

Встроенные скалярные требования К UDF

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

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

Включение скалярной встраивание UDF

Отключение скалярной подстраивание UDF без изменения уровня совместимости

Замечания

Связанный контент

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

Дополнительные ресурсы