Как я могу обойти SQL Server — изменение плана выполнения функции встроенного значения таблицы на основе параметров?

Вот ситуация:
У меня есть функция табличного значения с параметром datetime, если не сказать tdf(p_date) , которая фильтрует около двух миллионов строк, выбирая те, у которых дата столбца меньше p_date, и вычисляет некоторые агрегированные значения для других столбцов.< br> Это прекрасно работает, но если p_date является пользовательской функцией скалярного значения (в моем случае возвращающей конец дня), план выполнения изменяется, и время выполнения запроса увеличивается с 1 секунды до 1 минуты.

Таблица проверки концепции - 1K продуктов, 2M строк:

CREATE TABLE [dbo].[POC](
    [Date] [datetime] NOT NULL,
    [idProduct] [int] NOT NULL,
    [Quantity] [int] NOT NULL
) ON [PRIMARY]

Функция встроенного табличного значения:

CREATE FUNCTION tdf (@p_date datetime)
RETURNS TABLE 
AS
RETURN 
(
    SELECT idProduct, SUM(Quantity) AS TotalQuantity,
         max(Date) as LastDate
    FROM POC
    WHERE (Date < @p_date)
    GROUP BY idProduct
)

Функция скалярного значения:

CREATE FUNCTION [dbo].[EndOfDay] (@date datetime)
RETURNS datetime
AS
BEGIN
    DECLARE @res datetime
    SET @res=dateadd(second, -1,
         dateadd(day, 1, 
             dateadd(ms, -datepart(ms, @date),
                 dateadd(ss, -datepart(ss, @date),
                    dateadd(mi,- datepart(mi,@date),
                         dateadd(hh, -datepart(hh, @date), @date))))))
    RETURN @res
END

Запрос 1 - отлично работает

SELECT * FROM [dbo].[tdf] (getdate())

Конец плана выполнения: Совокупная стоимость потока 13% ‹--- Стоимость сканирования кластеризованного индекса 86%

Запрос 2 — не очень

SELECT * FROM [dbo].[tdf] (dbo.EndOfDay(getdate()))

Конец плана выполнения: Совокупная стоимость потока 4% ‹--- Стоимость фильтра 12% ‹--- Стоимость сканирования кластеризованного индекса 86%


sql-server tsql user-defined-functions sql-execution-plan
person Ovidiu Pacurar    schedule 16.01.2009    source источник
comment
Код SQL, чтобы точно проиллюстрировать, что вы имеете в виду, был бы полезен.   -  person Dimi Takis    schedule 16.01.2009
comment
Я разместил код, необходимый для репликации варианта плана выполнения.   -  person Ovidiu Pacurar    schedule 16.01.2009

Ответы (2)


arrow_upward
6
arrow_downward

Накладные расходы - это ваша скалярная функция.

TVF здесь расширен как встроенный макрос, поэтому

SELECT * FROM [dbo].[tdf] (getdate())

становится

SELECT     idProduct, SUM(Quantity) AS TotalQuantity, max(Date) as LastDate
    FROM         POC
    WHERE     Date < getdate()
    GROUP BY idProduct

Когда вы используете скалярную функцию конца дня, SQL не может оценивать EOD(GETDATE()) как константу. Я не могу быстро найти свою статью о том, как SQL оценивает этот материал, извините.

Я предполагаю, что он оценивается для каждой строки, а не заранее, как вы хотите.

Я бы вычислил оператор EOD отдельно:

DECLARE @eod datetime;
SET @eod = dbo.EndOfDay(getdate());
SELECT * FROM [dbo].[tdf] (@eod)

Я бы также использовал это для функции EOD:

DATEADD(second, -1, DATEADD(day, 1, (DATEDIFF(day, 0, @date))))

EDIT: другой вопрос, на который я ответил

person gbn    schedule 16.01.2009
comment
Согласитесь, скалярные функции почти всегда выполняются построчно, и поэтому их следует избегать. - person HLGEM; 16.01.2009

arrow_upward
1
arrow_downward

Вы также можете переписать EndOfDay как встроенный UDF и использовать вложенные встроенные UDF. Примеры:

Много вложенных встроенные UDF очень быстрые

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

person A-K    schedule 08.07.2009