Составная таблица ценная функция по сравнению со встроенной табличной функцией
Несколько примеров для показа просто упакуйте:
Встроенный табличный
CREATE FUNCTION MyNS.GetUnshippedOrders()
RETURNS TABLE
AS
RETURN SELECT a.SaleId, a.CustomerID, b.Qty
FROM Sales.Sales a INNER JOIN Sales.SaleDetail b
ON a.SaleId = b.SaleId
INNER JOIN Production.Product c ON b.ProductID = c.ProductID
WHERE a.ShipDate IS NULL
GO
Ценная составная таблица
CREATE FUNCTION MyNS.GetLastShipped(@CustomerID INT)
RETURNS @CustomerOrder TABLE
(SaleOrderID INT NOT NULL,
CustomerID INT NOT NULL,
OrderDate DATETIME NOT NULL,
OrderQty INT NOT NULL)
AS
BEGIN
DECLARE @MaxDate DATETIME
SELECT @MaxDate = MAX(OrderDate)
FROM Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID
INSERT @CustomerOrder
SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty
FROM Sales.SalesOrderHeader a INNER JOIN Sales.SalesOrderHeader b
ON a.SalesOrderID = b.SalesOrderID
INNER JOIN Production.Product c ON b.ProductID = c.ProductID
WHERE a.OrderDate = @MaxDate
AND a.CustomerID = @CustomerID
RETURN
END
GO
Существует ли преимущество для использования одного типа (встроенный или много оператор) по другому? Есть ли определенные сценарии, когда каждый лучше, чем другой, или действительно ли различия являются чисто синтаксическими? Я понимаю, что два запроса в качестве примера делают разные вещи, но существует ли причина, которую я записал бы им таким образом?
Чтение о них и преимуществах/различиях не было действительно объяснено.
4 ответа
Исследуя комментарий Мэтта, я пересмотрел свое первоначальное утверждение. Он прав, будет разница в производительности между встроенной функцией с табличным значением (ITVF) и функцией с несколькими инструкциями с табличным значением (MSTVF), даже если они оба просто выполняют оператор SELECT. SQL Server будет рассматривать ITVF как VIEW в том смысле, что он будет вычислять план выполнения, используя последнюю статистику по рассматриваемым таблицам. MSTVF эквивалентен помещению всего содержимого вашего оператора SELECT в табличную переменную и последующему присоединению к ней. Таким образом, компилятор не может использовать статистику таблиц для таблиц в MSTVF. Таким образом, при прочих равных условиях (что случается редко) ITVF будет работать лучше, чем MSTVF. В моих тестах разница в производительности во времени выполнения была незначительной, однако со статистической точки зрения она была заметна.
В вашем случае эти две функции не эквивалентны функционально. Функция MSTV выполняет дополнительный запрос при каждом вызове и, что наиболее важно, фильтрует идентификатор клиента. В большом запросе оптимизатор не сможет воспользоваться преимуществами других типов объединений, поскольку ему потребуется вызывать функцию для каждого переданного идентификатора клиента. Однако, если вы переписываете свою функцию MSTV следующим образом:
CREATE FUNCTION MyNS.GetLastShipped()
RETURNS @CustomerOrder TABLE
(
SaleOrderID INT NOT NULL,
CustomerID INT NOT NULL,
OrderDate DATETIME NOT NULL,
OrderQty INT NOT NULL
)
AS
BEGIN
INSERT @CustomerOrder
SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty
FROM Sales.SalesOrderHeader a
INNER JOIN Sales.SalesOrderHeader b
ON a.SalesOrderID = b.SalesOrderID
INNER JOIN Production.Product c
ON b.ProductID = c.ProductID
WHERE a.OrderDate = (
Select Max(SH1.OrderDate)
FROM Sales.SalesOrderHeader As SH1
WHERE SH1.CustomerID = A.CustomerId
)
RETURN
END
GO
В запросе оптимизатор сможет вызвать эту функцию один раз и построить лучший план выполнения, но он все равно не будет лучше, чем эквивалентный, непараметрический ITVS или ПРОСМОТР .
ITVF должны быть предпочтительнее, чем MSTVF, когда это возможно, потому что типы данных, допустимость значений NULL и сопоставление из столбцов в таблице, тогда как вы объявляете эти свойства в функции с табличным значением с несколькими инструкциями, и, что важно, вы получите лучшие планы выполнения из ITVF. По моему опыту, я не нашел много случаев, когда ITVF был бы лучшим вариантом, чем VIEW, но пробег может отличаться.
Спасибо Мэтту.
Дополнение
Поскольку я видел это недавно, вот отличный анализ, сделанный Уэйном Шеффилдом, сравнивающий разницу в производительности между встроенными таблично-значными функциями и функциями с несколькими операторами.
Если вы собираетесь выполнить запрос, вы можете присоединиться к функции Inline Table Valued, например:
SELECT
a.*,b.*
FROM AAAA a
INNER JOIN MyNS.GetUnshippedOrders() b ON a.z=b.z
, она потребует небольших накладных расходов и будет работать нормально.
если вы попытаетесь использовать таблицу с несколькими утверждениями, оцененную в аналогичном запросе, у вас возникнут проблемы с производительностью:
SELECT
x.a,x.b,x.c,(SELECT OrderQty FROM MyNS.GetLastShipped(x.CustomerID)) AS Qty
FROM xxxx x
поскольку вы будете выполнять функцию 1 раз для каждой возвращаемой строки, по мере увеличения набора результатов она будет беги все медленнее и медленнее.
Ваши примеры, я думаю, очень хорошо отвечают на вопрос. Первая функция может быть выполнена как одиночный select, и это хорошая причина использовать стиль inline. Вторая, вероятно, может быть выполнена в виде одного оператора (с использованием подзапроса для получения максимальной даты), но некоторые программисты могут счесть более удобным для чтения или более естественным выполнение ее в нескольких операторах, как это сделали вы. Некоторые функции просто не могут быть выполнены в одном операторе, поэтому для них требуется версия с несколькими операторами.
Я предлагаю использовать простейший вариант (inline), когда это возможно, и использовать несколько утверждений, когда это необходимо (очевидно) или когда личные предпочтения/читабельность позволяют обойтись без дополнительного набора текста.
Внутренне SQL Server обрабатывает встроенную табличную функцию так же, как и представление, и обрабатывает многооператорную табличную функцию аналогично хранимой процедуре.
Когда встроенная функция с табличным значением используется как часть внешнего запроса, обработчик запросов расширяет определение UDF и генерирует план выполнения, который обращается к базовым объектам, используя индексы этих объектов.
Для функции с табличным значением с несколькими операторами план выполнения создается для самой функции и сохраняется в кэше плана выполнения (после того, как функция была выполнена в первый раз). Если функции с табличными значениями с несколькими операторами используются как часть более крупных запросов, тогда оптимизатор не знает, что возвращает функция, и поэтому делает некоторые стандартные предположения - по сути, он предполагает, что функция вернет одну строку и что возврат доступ к функции будет осуществляться с помощью сканирования таблицы по таблице с одной строкой.
Функции с табличным значением с несколькими операторами могут работать плохо, когда они возвращают большое количество строк и объединяются во внешних запросах. Проблемы с производительностью в основном сводятся к тому, что оптимизатор будет создавать план, предполагая, что возвращается одна строка, что не обязательно будет наиболее подходящим планом.
В качестве общего практического правила мы обнаружили, что там, где это возможно, следует использовать встроенные функции с табличным значением, а не функции с несколькими операторами (когда UDF будет использоваться как часть внешнего запроса) из-за этих потенциальных проблем с производительностью.