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

Ответ 1

Нет никакой разницы в скорости между запросом, выполняемым внутри функции, и одним прогоном внутри процедуры.

У хранимых процедур есть проблемы с агрегированием результатов, они не могут быть составлены с другими хранимыми процедурами. Решение onyl действительно громоздко, поскольку оно включает в себя извлечение вывода процедуры в таблицу с помощью INSERT ... EXEC ..., а затем с использованием приведенной таблицы.

Преимущество функций состоит в том, что они очень сложны, поскольку функция значения таблицы может быть размещена в любом месте, где ожидаются табличные выражения (FROM, JOIN, APPLY, IN и т.д.). Но функции имеют очень серьезные ограничения в отношении того, что разрешено в функции, а что нет, именно потому, что они могут появляться в любом месте запроса.

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

Ответ 2

Не все UDF плохо работают.

Существует распространенное заблуждение, что UDF оказывают неблагоприятное влияние на производительность. Как общее утверждение, это просто неверно. Фактически, встроенные табличные значения UDF на самом деле являются макросами - оптимизатор очень хорошо способен переписывать запросы, связанные с ними, а также оптимизировать их. Однако скалярные UDF обычно очень медленные. Я приведу краткий пример.

Необходимые условия

Вот script для создания и заполнения таблиц:

CREATE TABLE States(Code CHAR(2), [Name] VARCHAR(40), CONSTRAINT PK_States PRIMARY KEY(Code))
GO
INSERT States(Code, [Name]) VALUES('IL', 'Illinois')
INSERT States(Code, [Name]) VALUES('WI', 'Wisconsin')
INSERT States(Code, [Name]) VALUES('IA', 'Iowa')
INSERT States(Code, [Name]) VALUES('IN', 'Indiana')
INSERT States(Code, [Name]) VALUES('MI', 'Michigan')
GO
CREATE TABLE Observations(ID INT NOT NULL, StateCode CHAR(2), CONSTRAINT PK_Observations PRIMARY KEY(ID))
GO
SET NOCOUNT ON
DECLARE @i INT
SET @i=0
WHILE @i<100000 BEGIN
  SET @i = @i + 1
  INSERT Observations(ID, StateCode)
  SELECT @i, CASE WHEN @i % 5 = 0 THEN 'IL'
    WHEN @i % 5 = 1 THEN 'IA'
    WHEN @i % 5 = 2 THEN 'WI'
    WHEN @i % 5 = 3 THEN 'IA'
    WHEN @i % 5 = 4 THEN 'MI'
    END
END
GO

Когда запрос с использованием UDF перезаписывается как внешнее соединение.

Рассмотрим следующий запрос:

SELECT o.ID, s.[name] AS StateName
  INTO dbo.ObservationsWithStateNames_Join
  FROM dbo.Observations o LEFT OUTER JOIN dbo.States s ON o.StateCode = s.Code

/*
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Observations'. Scan count 1, logical reads 188, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'States'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 187 ms,  elapsed time = 188 ms.
*/

И сравните его с запросом с встроенным табличным значением UDF:

CREATE FUNCTION dbo.GetStateName_Inline(@StateCode CHAR(2))
RETURNS TABLE
AS
RETURN(SELECT [Name] FROM dbo.States WHERE Code = @StateCode);
GO
SELECT ID, (SELECT [name] FROM dbo.GetStateName_Inline(StateCode)) AS StateName
  INTO dbo.ObservationsWithStateNames_Inline
  FROM dbo.Observations

Оба плана выполнения и затраты на его выполнение одинаковы - оптимизатор переписал его как внешнее соединение. Не недооценивайте силу оптимизатора!

Запрос с использованием скалярного UDF намного медленнее.

Вот скаляр UDF:

CREATE FUNCTION dbo.GetStateName(@StateCode CHAR(2))
RETURNS VARCHAR(40)
AS
BEGIN
  DECLARE @ret VARCHAR(40)
  SET @ret = (SELECT [Name] FROM dbo.States WHERE Code = @StateCode)
  RETURN @ret
END
GO

Очевидно, что запрос с использованием этого UDF дает те же результаты, но имеет другой план выполнения, и он значительно медленнее:

/*
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 3 ms.
Table 'Worktable'. Scan count 1, logical reads 202930, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Observations'. Scan count 1, logical reads 188, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 11890 ms,  elapsed time = 38585 ms.
*/

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

Не все UDF плохо работают.

Ответ 3

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

Однако UDF вызываются для КАЖДОЙ РЯДЫ, поэтому я буду осторожен, когда вы ее используете. Это вызвало у меня настоящую проблему. Настолько, что я никогда не забуду.

Ответ 4

Как только SQL увидит BEGIN или END, система не сможет упростить содержимое.

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

Лучше всего использовать либо представление, либо встроенную функцию, ориентированную на таблицу, так что SQL может упростить ее и только сделать ту часть, которая вам интересна. Посмотрите мой пост на тему "Опасности BEGIN и END" в моем блоге для получения дополнительной информации.

Ответ 5

Простые инструкции SELECT будут в наибольшей степени затронуты любыми индексами в запросах, которые вы запрашиваете.

Оптимизатор находится в основе выбранного вами механизма базы данных и отвечает за принятие важных решений о том, как выполняется запрос.

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

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

Функции могут быть Scalar (возвращает один результат) или возвращать табличные данные.

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

Если у вас еще нет книги Джо Селко, то теперь может быть время для инвестиций.

Ответ 6

В первый раз, когда я попытался использовать Inline Table Valued Function (TVF), он действительно занимал от 66 до 76% (от 1.147 до 1.2 против 0.683 сек.) дольше (по сравнению с хранимой процедурой (SP))!?! Это было в среднем 100 итераций с 89 строк на итерацию. Мой SP просто выполнял стандарт set nocount on, за которым следовал сложный (но все же одиночный) оператор select (с 5 inner join и 2 outer join (с одним из inner join, имеющим выражение on встроенный select (который сам имел выражение where (со встроенным select + inner join))) и a group by и order by с 5 столбцами и a count). Caller - это insert into таблица Temp (с столбцом identity, но без ключей или индексов) - Statement. Inline TVF занимал на 66% больше даже без order by, который выполнял SP. Когда я добавил его обратно (к select, вызывающему Inline TVF, так как вы не можете иметь order by в Inline TVF), потребовалось еще больше (76%)!?!