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

Применимо к: даSQL Server 2019 (15.x) ДаБаза данных SQL Azure

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

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

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

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

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

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

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

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

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

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

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

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

Пример 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 по умолчанию не определено).
  • Функция не ссылается на табличные переменные или параметры с табличными значениями.
  • В запросе, вызывающем скалярную пользовательскую функцию, вызов такой функции не указан в предложении GROUP BY.
  • В запросе, вызывающем скалярную пользовательскую функцию в своем списке выборки с предложением DISTINCT, нет предложения ORDER BY.
  • UDF не используется в предложении ORDER BY.
  • Функция не скомпилирована в собственном коде (поддерживается взаимодействие).
  • Функция не используется в определении вычисляемого столбца или ограничения CHECK.
  • Функция не ссылается на пользовательские типы.
  • В функцию не добавлены сигнатуры.
  • Функция не является функцией секционирования.
  • Определяемая пользователем функция не содержит ссылок на обобщенные табличные выражения (CTE).
  • Определяемая пользователем функция не может содержать ссылки на встроенные функции, которые могут изменять результаты при встраивании (например, @@ROWCOUNT) 4.
  • Определяемая пользователем функция не может содержать агрегатные функции, передаваемые в качестве параметров в скалярную определяемую пользователем функцию 4.
  • Определяемая пользователем функция не ссылается на встроенные представления (например, OBJECT_ID) 4.
  • Определяемая пользователем функция не ссылается на XML-методы 5.
  • Определяемая пользователем функция не может содержать SELECT с ORDER BY без предложения TOP 1 5.
  • Определяемая пользователем функция не может содержать запрос SELECT, выполняющий назначение, в сочетании с предложением ORDER BY (например, SELECT @x = @x + 1 FROM table1 ORDER BY col1) 5.
  • Определяемая пользователем функция не может содержать несколько инструкций RETURN 6.
  • Определяемая пользователем функция не может вызываться из инструкции RETURN 6.
  • Определяемая пользователем функция не ссылается на функцию STRING_AGG 6.
  • Определяемая пользователем функция не ссылается на удаленные таблицы 7.
  • В запросе, вызывающем определяемую пользователем функцию, не используется GROUPING SETS, CUBE или ROLLUP 7.
  • В запросе, вызывающем определяемую пользователем функцию, не содержится переменная, используемая в качестве назначаемого параметра определяемой пользователем функции (например, SELECT @y = 2, @x = UDF(@y))7.
  • Определяемая пользователем функция ссылается на зашифрованные столбцы 8.
  • Определяемая пользователем функция содержит ссылки на WITH XMLNAMESPACES 8.
  • Запрос, вызывающий определяемую пользователем функцию, содержит обобщенные табличные выражения (CTE)8.

1 Встраивание инструкций SELECT с накоплением или агрегированием переменных (например, 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 плана не будет узла <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. Пример:

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

Совет

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

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

См. также:

Создание пользовательских функций (ядро СУБД)
Центр производительности для базы данных SQL Azure и ядра СУБД SQL Server
Руководство по архитектуре обработки запросов
Справочник по логическим и физическим операторам Showplan
Соединения
Демонстрация интеллектуальной обработки запросов
Устранение проблем встраивания скалярных пользовательских функций в SQL Server 2019