Почему производительность функции табличного значения лучше, чем прямая инструкция select?
Я использую AdventureWorks2012 и провожу тест. и мой вопрос: почему производительность оператора SELECT напрямую ниже, чем у функции значений таблицы. Я только помещаю SELECT statemnt в функцию значения таблицы и полностью противоположную производительность.
CREATE FUNCTION [dbo].[atest1]
(
@iBusinessEntityID INT
)
RETURNS @t TABLE
(
[BusinessEntityID] INT
, [NationalIDNumber] NVARCHAR(15)
, [JobTitle] NVARCHAR(50)
)
AS
BEGIN
INSERT INTO @t
SELECT
[e].[BusinessEntityID]
, [e].[NationalIDNumber]
, [e].[JobTitle]
FROM [HumanResources].[Employee] [e]
INNER JOIN [Person].[Person] [p]
ON [p].[BusinessEntityID] = [e].[BusinessEntityID]
WHERE [e].[BusinessEntityID] = @iBusinessEntityID;
RETURN;
END;
--TEST PERFORMANCE
SELECT
*
FROM [dbo].[atest1](5);
GO
SELECT
[e].[BusinessEntityID]
, [e].[NationalIDNumber]
, [e].[JobTitle]
FROM [HumanResources].[Employee] [e]
INNER JOIN [Person].[Person] [p]
ON [p].[BusinessEntityID] = [e].[BusinessEntityID]
WHERE [e].[BusinessEntityID] = 5;
Я говорю о стоимости запроса. Я ожидаю, что результаты оператора SELECT напрямую будут лучше, чем функция значения таблицы
Я предполагаю, что это могло быть из-за разной оценки мощности для функции и прямого запроса. Ваш запрос выполняется для значения «5», тогда как запрос внутри вашей функции для неизвестного значения, поэтому могут быть созданы разные планы. Попробуйте добавить OPTION подсказки запроса (OPTIMIZE FOR (@iBusinessEntityID = 5)), чтобы увидеть более точное сравнение.
Ответы 2
Обычно функции ведут себя хуже, чем прямые запросы, но возможно, что в этом случае, как и для чего-то предопределенного в качестве функции, система сохранит лучший план. В этом случае похоже, что для функции система выполняет сканирование таблицы, что иногда на небольших таблицах, как в случае с БД AdventureWorks, может работать лучше, чем поиск по индексу.
Кроме того, в вашем примере есть только один вызов функции. Что снижает производительность для функций, так это в том случае, если скалярные функции (приведенный пример относится к табличной функции), когда вы вызываете их повторно внутри запроса.
>>> В этом случае похоже, что для функции система выполняет сканирование таблицы <<< сканирование таблицы чего? Табличной переменной, созданной в функции? И что делает функция, чтобы ЗАПОЛНИТЬ эту табличную переменную?
Сканирование таблицы означает, что система не использует индексы, а читает таблицу и выполняет СОЕДИНЕНИЕ и ФИЛЬТРЫ в памяти.
@ Ангел М. Вы неправильно поняли мой вопрос. Я не спрашивал у вас, что означает сканирование, я спрашивал у вас сканирование КАКОГО ОБЪЕКТА вы видите на плане? И как это связано с исходными таблицами HumanResources.Employee и Person.Person. Я хочу сказать, что оба они ИСКЛЮЧЕНЫ и не сканировались в случае UDF.
Чтобы ответить, я должен воспроизвести точно такой же план выполнения, но у меня нет здесь базы данных AdventureWorks.
Я уже воспроизводил его и могу вас заверить, он выполняет поиск по индексу в обоих случаях. Дело в том, что это невозможно увидеть из плана, прикрепленного к этому посту. Таким образом, сканирование, которое вы видите, в этом случае не имеет значения.
Проблема здесь в том, что примерный план в SSMS часто показывает неправильный процент, в случае с UDFs почти всегда ошибается.
cost percentage - это ориентировочная стоимость операции по сравнению с другими операциями, но в случае UDFSSMS не проверяет внутреннее устройство UDF.
Я создал ваш UDF на своем сервере и добавил к нему текст GUID, чтобы я мог легко вернуть план для этого UDF:
CREATE FUNCTION [dbo].[atest1] (@iBusinessEntityID int)
RETURNS @t TABLE(BusinessEntityID int,NationalIDNumber nvarchar(15),JobTitle nvarchar(50)) AS
BEGIN
INSERT INTO @t /*3C6A985B-748B-44D4-9F76-1A0866342728*/ -- HERE IS MY GUID
SELECT e.BusinessEntityID, e.NationalIDNumber, e.JobTitle
FROM HumanResources.Employee e INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.BusinessEntityID = @iBusinessEntityID
RETURN
END
А теперь я выполняю эту функцию и получаю ее план следующим образом:
select p.query_plan
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) t
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
where cp.objtype = 'Proc'
and t.text like '%3C6A985B-748B-44D4-9F76-1A0866342728%'
Я изучил этот план, и он Точно ТАК ЖЕ, что и план вашего "прямого выступления". То же самое в его части SELECT, но есть еще INSERT в переменной таблицы и его scan в основном плане. Таким образом, вы можете ясно видеть, что стоимость вашего UDF не может быть ниже, она равна стоимости «прямого отчета» плюс стоимость INSERT плюс стоимость табличной переменной scan.
В этом случае таблицы маленькие, и есть только один вызов UDF, поэтому вы не можете заметить разницу во времени выполнения, но если вы сделаете цикл, в котором выполняете свой «прямой оператор» больше раз и вызываете UDF больше раз, вы, вероятно, увидите разница во времени выполнения, и «прямая инструкция» будет быстрее. Но SSMS все равно будет настаивать на более низкой стоимости UDF.
Что вы имеете в виду, говоря о производительности? Это время выполнения или количество чтений или что-то в этом роде?