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

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

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

Лучше всего использовать либо представление, либо встроенную функцию с табличным значением, чтобы SQL мог упростить ее и выполнять только ту часть, которая вам интересна. Ищите мой пост на тему «Опасности НАЧАЛА и КОНЕЦ "в моем блоге для получения дополнительной информации.

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

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

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

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

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

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

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

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

Если у вас еще нет книги Джо Селко, возможно, сейчас самое время инвестировать.

Не все UDF плохо влияют на производительность.

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

Prerequisites

Here is the script to create and populate the tables:

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

When a request with a UDF is rewritten as outer join.

Consider the request:

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.
*/

And compare it to a request with a inline table value 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

Here is the script to create and populate the tables:

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

When a request with a UDF is rewritten as outer join.

Consider the next request:

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.
*/

And comparison it to a request with a inline table value 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

Both its execution plan and its execution costs are same - optimizer has rewritten it as an outer join. Не стоит недооценивать мощность оптимизатора!

Запрос с использованием скалярного 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 плохо сказываются на производительности.