Использование MSSQL 2005
Сегодня я играл со скалярным UDF в операторе where, чтобы увидеть некоторые затраты, связанные с выполнением вызовов, различий io и т. Д.
Я начинаю с двух основных таблиц. Клиент, у которого 1 миллион строк. и Покупки, у которых 100000. Оба имеют столбец автоматической идентификации в качестве первичного ключа. Другие индексы не определены.
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
SELECT * FROM Customer C
INNER JOIN Purchases P on C.[IDENTITY] = P.CustomerID
WHERE P.Amount > 1000
SET STATISTICS IO OFF
Это возвращает статистику ввода-вывода
Table 'Customer'. Scan count 0, logical reads 3295, physical reads 1, read-ahead reads 32, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Purchases'. Scan count 1, logical reads 373, physical reads 1, read-ahead reads 370, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Итак, чтобы увидеть влияние скалярной UDF, я просто переместил P.Amount> 1000 в UDF. Функция следующая:
CREATE FUNCTION [dbo].[HighValuePurchase]
(
@value int
)
RETURNS bit
AS
BEGIN
DECLARE @highValue bit
SET @highValue = '0'
IF @value > 1000
BEGIN
SET @highValue = '1'
END
RETURN @highValue
END
Итак, я выполнил следующий запрос:
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
SELECT * FROM Customer C
INNER JOIN Purchases P on C.[IDENTITY] = P.CustomerID
WHERE dbo.HighValuePurchase(P.Amount) = '1'
SET STATISTICS IO OFF
Я ожидал, что это будет хуже. Этот запрос вернул следующую статистику операций ввода-вывода:
Table 'Purchases'. Scan count 1, logical reads 373, physical reads 1, read-ahead reads 370, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customer'. Scan count 1, logical reads 35, physical reads 3, read-ahead reads 472, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Это также вернулось быстрее, чем запрос> 1000. В то время как те же самые строки были возвращены, порядок запроса UDF был автоматически отсортирован C. [IDENTITY], тогда как другой запрос оказался несортированным. Вероятно, это связано с тем, как комбайны были размещены в планах выполнения. Краткое изложение планов ниже.
План выполнения для не UDF показывает сканирование кластерного индекса для покупок и поиск кластерного индекса для клиентов, объединенных во вложенном соединении.
План выполнения для версии UDF показывает сканирование кластерного индекса для покупок, затем фильтр, а затем сортировку. Для клиента есть сканирование кластерного индекса. Затем результаты объединяются в объединение слиянием.
Я уверен, что это связано с отсутствием индексов и т. Д., Но я не уверен, почему эти результаты такие, какие они есть. Я видел, как UDF работает очень медленно, и все говорят, что их использование - плохая идея, поэтому я провел этот тест вместе. В настоящее время я не могу объяснить, почему версия UDF кажется намного лучше.
