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

Применимо к:yes SQL Server 2019 (15.x) Да Azure SQL База данных да Управляемый экземпляр SQL Azure

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

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

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

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

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

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

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

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

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

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

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

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

Пример 1. Скалярная определяемая пользователем функция с одним оператором

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

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

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

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

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

Пример 2. Скалярная определяемая пользователем функция с несколькими операторами

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

План запроса без встраиваемого кода.

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

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

План запроса с встраиванием.

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

  • Сервер SQL Server определил наличие неявного соединения между CUSTOMER и ORDERS и сделал его явным с помощью оператора соединения.
  • Сервер SQL Server определил наличие неявного предложения GROUP BY O_CUSTKEY on ORDERS и реализовал его с помощью IndexSpool и StreamAggregate.
  • Теперь SQL Server применяет параллелизм для всех операторов.

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

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

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

  • Функция написана с использованием следующих конструкций:
    • DECLARE, SET: объявление переменных и присвоение им значений;
    • SELECT: SQL-запрос с присвоением значений одной переменной или нескольким 1;
    • IF/ELSE: ветвление с произвольными уровнями вложенности;
    • RETURN: одна инструкция return или несколько; Начиная с SQL Server 2019 (15.x) CU5, пользовательская функция может содержать только один оператор RETURN, который можно использовать для встраивания 6.
    • UDF: вызовы вложенных или рекурсивных функций 2;
    • прочее: реляционные операции, такие как EXISTS и ISNULL.
  • Определяемая пользователем функция не вызывает встроенную функцию, которая зависит от времени (например GETDATE()) или имеет побочные эффекты 3 (например NEWSEQUENTIALID()).
  • Определяемая пользователем функция использует EXECUTE AS CALLER предложение (поведение по умолчанию, если EXECUTE AS предложение не указано).
  • Определяемая пользователем функция не ссылается на табличные переменные или возвращаемые табличное значение параметры.
  • Запрос, вызывающий скалярную определяемую пользователем функцию, не ссылается на скалярный вызов UDF в своем GROUP BY предложении.
  • Запрос, вызывающий скалярную определяемую пользователем функцию в списке выбора с DISTINCT предложением, не имеет ORDER BY предложения.
  • Определяемая пользователем функция не используется в ORDER BY предложении.
  • Определяемая пользователем функция не компилируется в собственном коде (поддерживается взаимодействие).
  • Определяемая пользователем функция не используется в вычисляемом столбце или определении проверочного ограничения.
  • Определяемая пользователем функция не ссылается на определяемые пользователем типы.
  • В функцию не добавлены сигнатуры.
  • Определяемая пользователем функция не является функцией секционирования.
  • Определяемая пользователем функция не содержит ссылок на общие табличные выражения (CTEs).
  • Определяемая пользователем функция не содержит ссылок на встроенные функции, которые могут изменять результаты при встраивание (например @@ROWCOUNT) 4.
  • Определяемая пользователем функция не содержит агрегатные функции, передаваемые в качестве параметров скалярной определяемой пользователем функции 4.
  • Определяемая пользователем функция не ссылается на встроенные представления (например OBJECT_ID, 4).
  • Определяемая пользователем функция не ссылается на методы XML 5.
  • Определяемая пользователем функция не содержит инструкцию SELECT без ORDER BYTOP 1 предложения 5.
  • Определяемая пользователем функция не содержит запрос SELECT, который выполняет назначение с ORDER BY предложением (например SELECT @x = @x + 1 FROM table1 ORDER BY col1) 5.
  • Определяемая пользователем функция не содержит несколько инструкций RETURN 6.
  • Определяемая пользователем функция не вызывается из инструкции RETURN 6.
  • Определяемая пользователем функция не ссылается на функцию STRING_AGG6.
  • Определяемая пользователем функция не ссылается на удаленные таблицы 7.
  • Запрос, вызывающий определяемую пользователем функцию, CUBEне использует GROUPING SETSили ROLLUP7.
  • Запрос, вызывающий определяемую пользователем функцию, не содержит переменную, которая используется в качестве параметра UDF для назначения (например, SELECT @y = 2) @x = UDF(@y)7.
  • Определяемая пользователем функция не ссылается на зашифрованные столбцы 8.
  • Определяемая пользователем функция не содержит ссылок на WITH XMLNAMESPACES8.
  • Запрос, вызывающий определяемую пользователем функцию, не имеет общих табличных выражений (CTE) 8.

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

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

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

4 Ограничение добавлено в накопительный пакет обновления 2 для SQL Server 2019 (15.x)

5 Ограничение добавлено в накопительный пакет обновления 4 для SQL Server 2019 (15.x)

6 Ограничение добавлено в накопительный пакет обновления 5 для SQL Server 2019 (15.x)

7 Ограничение добавлено в накопительном пакете обновления 6 для SQL Server 2019 (15.x)

8 Ограничение добавлено в накопительном пакете обновления 11 для SQL Server 2019 (15.x)

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

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

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

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

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

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

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

  • Вызов определяемой пользователем функции в предложении GROUP BY не будет встраивание. Решение принимается при компиляции запроса, ссылающегося на скалярную пользовательскую функцию.

  • Пользовательская функция подписана с помощью сертификата. Так как подписи можно добавлять и удалять после создания определяемой пользователем функции, решение о том, следует ли встраивать или нет при компиляции скалярного определяемого пользователем запроса. Например, большинство системных функций подписано с помощью сертификатов. Для определения подписанных объектов можно использовать 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-файл плана не будет иметь xml-узел для пользовательской <UserDefinedFunction> функции, которая была успешно встраина.
  • Создается ряд событий XEvent.

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

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

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150;

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

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

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

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 в зависимости от того, можно ли встраивать определяемую пользователем функцию. Если указано INLINE = ON, но функция не подходит для встраивания, происходит ошибка.

Важные примечания

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

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

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

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

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

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

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

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

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

См. также раздел