Monthly Archives: Styczeń 2014

Funkcje skalarne a zmienne użytkownika w SQL Server

Jakiś czas temu brałem udział w spotkaniu na którym padło następujące pytanie: „Czy użycie funkcji skalarnej zamiast zwykłej zmiennej może mieć wpływ na wydajność? ”. Pierwsza moja odpowiedź: „Raczej nie …”. Potem jednak zacząłem się zastanawiać nad znalezieniem przypadku, w którym taka zmiana mogłaby coś popsuć i jak już się pewnie domyślacie – znalazłem 😀

Zanim jednak zacznę, to chciałbym podkreślić, że poniższy post nie jest wpisem na temat wyższości danej konstrukcji T-SQL nad inną. Po prostu chciałbym podzielić się z wami moimi obserwacjami 🙂

Przygotowanie do testów

Do testów użyłem SQL Server 2012 z zainstalowanym SP1 oraz przykładowej bazy AdventureWorks2012. Baza ta powinna posiadać tabelę Sales.SalesOrderDetail, na której został stworzony następujący indeks:

USE [AdventureWorks2012]
GO
CREATE INDEX IX_SalesOrderDetail_ProductID
ON [Sales].[SalesOrderDetail]
(
 ProductID ASC
)

Dodatkowo powinniśmy stworzyć prostą funkcję skalarną:

CREATE FUNCTION dbo.test(@i int)
RETURNS int
AS
BEGIN
 RETURN @i
END

Pierwsze porównanie i pierwszy problem 🙂

Na początek do porównania wydajnościowego weźmy następujący kod:

-- zapytanie 1
DECLARE @i int = 707
SELECT *
FROM AdventureWorks2012.Sales.SalesOrderDetail
WHERE ProductID = @i
-- zapytanie 2
SELECT *
FROM AdventureWorks2012.Sales.SalesOrderDetail
WHERE ProductID = dbo.Test(707)

Porównanie planów zapytań oraz wydajności pokazuje, że koszt dla obu instrukcji SELECT jest identyczny:

ZMIENNE_A_FUNKCJE_01

Jednak jest pewne  „małe ale” …

Czy optymalizator wybrał odpowiedni plan wykonania?

Niestety okazuje się, że NIE. W obu przypadkach zastosowana została estymacja wierszy na poziomie około 456 rekordów, zaś wierszy było o kilkukrotnie więcej:

ZMIENNE_A_FUNKCJE_02

Jedną z technik optymalizacji w tego typu przypadkach jest wymuszenie rekompilacji zapytania:

SET STATISTICS IO ON
-- zapytanie 1
DECLARE @i int
SET @i = 707
SELECT *
FROM AdventureWorks2012.Sales.SalesOrderDetail
WHERE ProductID = @i 
OPTION (RECOMPILE)
-- zapytanie 2
SELECT *
FROM AdventureWorks2012.Sales.SalesOrderDetail
WHERE ProductID = dbo.Test(707)
OPTION (RECOMPILE)

ZMIENNE_A_FUNKCJE_03

Jak widać tym razem plan wykonania jest różny dla obu przypadków. Mimo tego, że Management Studio pokazuje, że druga instrukcja jest wydajniejsza, to porównanie odczytanych stron nie daje złudzeń – pierwsze z zapytań jest kilkukrotnie szybsze 🙂

ZMIENNE_A_FUNKCJE_04

Sprawą, o którą mi chodziło w tym poście nie jest jednak tylko i wyłącznie wydajność. Kluczowym aspektem jest fakt, że w drugim przypadku SQL Server nadal estymował niepoprawną ilość wierszy. Mimo rekompilacji na poziomie całej instrukcji T-SQL przewidywana ilość wierszy pozostała taka jak wcześniej:

ZMIENNE_A_FUNKCJE_05

Niestety nie mamy możliwości poprawy tego typu sytuacji … nawet jeżeli rekompilacja funkcji by pomogła, to zgodnie z SQL Server Books Online funkcje w odróżnieniu od procedur nie posiadają słówka „RECOMPILE” w dostępnych opcjach:

Function Options
<function_option>::=
{
[ ENCRYPTION ]
| [ SCHEMABINDING ]
| [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
| [ EXECUTE_AS_Clause ]
}

Podsumowanie

Zastosowanie funkcji skalarnej zamiast zmiennej użytkownika może mieć wpływ na wydajność. W analizowanym przypadku błędna estymacja rekordów zwracanych przez instrukcję SELECT stwarzała nieodpowiedni plan zapytania. Dla zmiennej użytkownika mogliśmy zastosować opcję RECOMPILE  i przyśpieszyć wykonanie. Z funkcją skalarną nie udało się tego efektu uzyskać – mimo rekompilacji  plan pozostał ten sam.

Reklamy