Как заставить скалярный UDF оценивать только один раз в запросе?

У меня есть запрос, который нужно фильтровать против результата скалярного UDF. Запрос должен быть отправлен как один оператор (поэтому я не могу назначить результат UDF локальной переменной), и я не могу использовать TVF. Я знаю о проблемах производительности, вызванных скалярными UDF, которые включают в себя принудительное выполнение всего плана, чрезмерные объемы памяти, проблемы с оценкой мощности и отсутствие вложения. Для этого вопроса, пожалуйста, предположите, что мне нужно использовать скалярный UDF.

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

SELECT x1.ID
FROM dbo.X_100_INTEGERS x1
WHERE x1.ID >= dbo.EXPENSIVE_UDF();

Оператор фильтра в плане запроса предполагает, что функция была оценена один раз для каждая строка:

 план запроса 1

DDL и подготовка данных:

CREATE OR ALTER FUNCTION dbo.EXPENSIVE_UDF () RETURNS INT
AS
BEGIN
    DECLARE @tbl TABLE (VAL VARCHAR(5));

    -- make the function expensive to call
    INSERT INTO @tbl
    SELECT [VALUE]
    FROM STRING_SPLIT(REPLICATE(CAST('Z ' AS VARCHAR(MAX)), 20000), ' ');

    RETURN 1;
END;

GO

DROP TABLE IF EXISTS dbo.X_100_INTEGERS;

CREATE TABLE dbo.X_100_INTEGERS (ID INT NOT NULL);

-- insert 100 integers from 1 - 100
WITH
    L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
    L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT INTO dbo.X_100_INTEGERS WITH (TABLOCK)
SELECT n FROM Nums WHERE n <= 100;

Ниже приведено ссылка на db-скрипт для примера, хотя код около 18 секунд для выполнения там.

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

10 голосов | спросил Joe Obbish 6 MarpmMon, 06 Mar 2017 17:46:25 +03002017-03-06T17:46:25+03:0005 2017, 17:46:25

1 ответ


13

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

Если можно отредактировать код, первое, что нужно попробовать, - сделать функцию детерминированной, если это возможно. Пол Уайт указывает здесь , что функция должна быть создана с помощью опции SCHEMABINDING, а сам код функции должен быть детерминированным.

После внесения следующего изменения:

CREATE OR ALTER FUNCTION dbo.EXPENSIVE_UDF () RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @tbl TABLE (VAL VARCHAR(5));

    -- make the function expensive to call
    INSERT INTO @tbl
    SELECT [VALUE]
    FROM STRING_SPLIT(REPLICATE(CAST('Z ' AS VARCHAR(MAX)), 20000), ' ');

    RETURN 1;
END;

Запрос из вопроса выполняется в 64 мс:

SELECT x1.ID
FROM dbo.X_100_INTEGERS x1
WHERE x1.ID >= dbo.EXPENSIVE_UDF();

В плане запроса больше нет оператора фильтра:

 план запроса 1

Чтобы убедиться, что он выполняется только один раз, мы можем использовать новый sys. dm_exec_function_stats DMV, выпущенный в SQL Server 2016:

SELECT execution_count
FROM sys.dm_exec_function_stats
WHERE object_id = OBJECT_ID('EXPENSIVE_UDF', 'FN');

Выдача ALTER для функции сбросит значение execution_count для этого объекта. Вышеуказанный запрос возвращает 1, что означает, что функция была выполнена только один раз.

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

WITH cte (UDF_VALUE) AS
(
    SELECT DISTINCT dbo.EXPENSIVE_UDF() UDF_VALUE
)
SELECT ID
FROM dbo.X_100_INTEGERS
INNER JOIN cte ON ID >= cte.UDF_VALUE;

Добавлен избыточный DISTINCT, чтобы избавиться от оператора Filter. План выглядит многообещающим:

 план запроса 2

Исходя из этого, можно было бы ожидать, что UDF будет оцениваться один раз и использоваться как внешняя таблица в объединении вложенного цикла. Тем не менее, запрос занимает 6446 мс для запуска на моей машине. Согласно sys.dm_exec_function_stats функция выполнялась 100 раз. Как это возможно? В разделе Compute Scalars, Expressions и Execution Plan », Пол Уайт указывает, что оператор Compute Scalar можно отложить:

  

Чаще всего, Compute Scalar просто определяет выражение; фактическое вычисление откладывается до тех пор, пока результат выполнения плана не потребует результата.

Для этого запроса похоже, что вызов UDF был отложен до тех пор, пока он не понадобился, и в этот момент он был оценен 100 раз.

Интересно, что пример CTE выполняется на 71 мс на моей машине, когда UDF не определен с помощью SCHEMABINDING, как в исходном вопросе , Функция выполняется только один раз, когда выполняется запрос. Вот план запроса для этого:

 план запроса 3

Непонятно, почему Compute Scalar не откладывается. Это может быть связано с тем, что недетерминированность функции ограничивает перестановку операторов, которые может выполнять оптимизатор запросов.

Альтернативный подход состоит в том, чтобы добавить небольшую таблицу в CTE и запросить единственную строку в этой таблице. Любая маленькая таблица будет делать, но давайте использовать следующее:

CREATE TABLE dbo.X_ONE_ROW_TABLE (ID INT NOT NULL);

INSERT INTO dbo.X_ONE_ROW_TABLE VALUES (1);

Затем запрос будет выглядеть следующим образом:

WITH cte (UDF_VALUE) AS
(       
    SELECT DISTINCT dbo.EXPENSIVE_UDF() UDF_VALUE
    FROM dbo.X_ONE_ROW_TABLE
)
SELECT ID
FROM dbo.X_100_INTEGERS
INNER JOIN cte ON ID >= cte.UDF_VALUE;

Добавлениеdbo.X_ONE_ROW_TABLE добавляет неопределенность для оптимизатора. Если таблица имеет нулевые строки, CTE вернет 0 строк. В любом случае оптимизатор не может гарантировать, что CTE вернет одну строку, если UDF не является детерминированным, поэтому представляется вероятным, что UDF будет оцениваться до объединения. Я бы ожидал, что оптимизатор сканирует dbo.X_ONE_ROW_TABLE, использует агрегат потока, чтобы получить максимальное значение возвращенной одной строки (что требует, чтобы функция быть оцененным) и использовать это как внешнюю таблицу для вложенного цикла, присоединяющегося к dbo.X_100_INTEGERS в основном запросе. Это выглядит как что происходит :

 план запроса 4

Запрос выполняется примерно на 110 мс на моем компьютере, и UDF оценивается только один раз в соответствии с sys.dm_exec_function_stats. Было бы неправильно сказать, что оптимизатор запросов вынужден оценивать UDF только один раз. Тем не менее, трудно представить, что оптимизатор переписывается, что приведет к более дешевому запросу, даже с ограничениями вокруг UDF и вычисления скалярной калькуляции.

В заключение, для детерминированных функций (которые должны включать параметр SCHEMABINDING) попробуйте написать запрос как можно более простым способом , Если на SQL Server 2016 или более поздней версии убедитесь, что функция была выполнена только один раз, используя sys.dm_exec_function_stats. Планы выполнения могут вводить в заблуждение в этом отношении.

Для функций, которые не рассматриваются SQL Server как детерминированные, включая все, что отсутствует опция SCHEMABINDING, один из подходов состоит в том, чтобы поместить UDF в тщательно обработанную CTE или производную таблицу. Это требует небольшой осторожности, но тот же самый CTE может работать как для детерминированных, так и недетерминированных функций.

ответил Joe Obbish 6 MarpmMon, 06 Mar 2017 17:46:25 +03002017-03-06T17:46:25+03:0005 2017, 17:46:25

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132