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

  • Статья

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

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

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

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

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

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

  • Итеративное вызов. Пользовательские функции вызываются итеративно — однократно в соответствующем кортеже. Постоянные переключения контекста при вызове функций требуют дополнительных ресурсов. Особенно это касается определяемых пользователем пользователем запросов Transact-SQL в их определении.

  • Отсутствие затрат. Во время оптимизации стоят только реляционные операторы, а скалярные операторы не являются. До внедрения скалярных определяемых пользователем операторов другие скалярные операторы обычно были дешевыми и не требовали затрат. Достаточно было учитывать небольшое увеличение загрузки ЦП. Ниже представлены сценарии, в которых фактические затраты значительны, но по-прежнему не принимаются во внимание в полной мере.

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

  • Последовательное выполнение. SQL Server не разрешает параллелизм внутри запросов, вызывающих определяемые пользователем функции.

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

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

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

Пример 1. Скалярный 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 Inlining скалярное выражение в тексте UDF заменено непосредственно в запросе. Результаты выполнения этого запроса показаны в приведенной ниже таблице.

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

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

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

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

CREATE OR ALTER FUNCTION dbo.customer_category(@ckey INT)
RETURNS CHAR(10) AS
BEGIN
  DECLARE @total_price DECIMAL(18,2);
  DECLARE @category 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 и более ранние версии) выглядит следующим образом:

Query Plan without inlining.

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

Для этого же запроса план со встроенной пользовательской функцией выглядит так:

Query Plan with inlining.

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

  • Сервер 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 для встраивание 6.
    • UDF: вызовы вложенной или рекурсивной функции 2.
    • прочее: реляционные операции, такие как EXISTS и IS NULL.
  • UDF не вызывает встроенную функцию, которая зависит от времени (например GETDATE()) или имеет побочные эффекты 3 (например 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, 4).
  • UDF не содержит статистические функции, передаваемые в качестве параметров скалярной UDF 4.
  • UDF не ссылается на встроенные представления (например OBJECT_ID, 4).
  • UDF не ссылается на методы XML 5.
  • UDF не содержит select без ORDER BYTOP 1 предложения 5.
  • UDF не содержит запрос SELECT, выполняющий назначение с ORDER BY предложением (например SELECT @x = @x + 1 FROM table1 ORDER BY col1, 5).
  • UDF не содержит несколько инструкций RETURN 6.
  • UDF не вызывается из инструкции RETURN 6.
  • UDF не ссылается на функцию STRING_AGG6.
  • UDF не ссылается на удаленные таблицы 7.
  • Запрос на вызов UDF не использует GROUPING SETSCUBEили ROLLUP7.
  • Запрос на вызов UDF не содержит переменную, которая используется в качестве параметра UDF для назначения (например, SELECT @y = 2@x = UDF(@y)) 7.
  • UDF не ссылается на зашифрованные столбцы 8.
  • UDF не содержит ссылок на WITH XMLNAMESPACES8.
  • Запрос, вызывающий UDF, не имеет общих выражений таблиц (CTEs) 8.

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

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

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

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

Ограничение 5 , добавленное в ПАКЕТ обновления 4 (CU4) SQL Server 2019 (15.x)

Ограничение 6 , добавленное в ПАКЕТ ОБНОВЛЕНИЯ 5 (CU5) SQL Server 2019 (15.x)

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

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

Сведения о последних исправлениях и изменениях встраивание скалярных UDF T-SQL см. в статье базы знаний: FIX: Скалярная настройка UDF в SQL Server 2019.

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

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

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

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

Если скалярный UDF является встроенным, он не означает, что он всегда будет вложен. Целесообразность встраивания определяется в SQL Server для каждого конкретного запроса и каждой функции. Ниже приведены примеры ситуаций, в которых невозможно встраивание пользовательских функций:

  • Если определение 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 без изменения уровня совместимости

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

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

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

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = 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;

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

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 Inlining преобразует запрос с скалярными определяемых пользователем УДФ в запрос с эквивалентным скалярным вложенным запросом. Из-за этого пользователи могут заметить отличия в поведении в описанных ниже ситуациях.

  1. В результате встраивания хэш запроса для того же текста запроса будет другим.

  2. В инструкциях внутри пользовательской функции могут появляться предупреждения (например, о делении на ноль и другие), которые были скрыты до встраивания.

  3. Указания соединения на уровне запроса могут стать недействительными, так как в результате встраивания могут появиться новые соединения. Вместо этого следует использовать локальные указания соединения.

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

  5. При встраивании пользовательских функций может изменяться поведение динамического маскирования данных.

    В некоторых ситуациях (в зависимости от логики в UDF) встраивание может быть более консервативным в отношении маскирования выходных столбцов. В сценариях, где столбцы, на которые ссылаются в UDF, не являются выходными столбцами, они не будут маскированы.

  6. Если пользовательская функция ссылается на встроенные функции, такие как SCOPE_IDENTITY(),@@ROWCOUNT или @@ERROR, значение, возвращаемое встроенной функцией, меняется после встраивания пользовательской функции. Связано это с тем, что встраивание меняет область инструкций внутри пользовательской функции. Начиная с SQL Server 2019 (15.x) CU2, встраивание блокируется, если UDF ссылается на определенные встроенные функции (например @@ROWCOUNT).

  7. Если переменная назначена результатом встроенной UDF, а также используется в качестве index_column_name в подсказке запроса FORCESEEK, это приведет к ошибке Msg 8622, указывающей, что обработчик запросов не может создать план запроса из-за указаний, определенных в запросе.

См. также