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

Materiały na temat migawek baz danych już dostępne !!!

Właśnie udostępniłem materiały z sesji Database Snapshot Geek Dive, którą poprowadziłem w ramach pierwszego spotkania Trójmiejskiego oddziału Polish SQL Server Users Group.

Tych z was, którzy są zainteresowani ich przejrzeniem – zapraszam do działu materiały do pobrania 🙂

70-461: Querying Microsoft SQL Server 2012

Kolejny egzamin do przodu 🙂

Tym razem na tapetę wziąłem  70-461: Querying Microsoft SQL Server 2012. Egzamin całkiem przyjemny i raczej nie sprawił mi jakiś problemów. Co do samych pytań – zaczynam już się przyzwyczajać do kilku prawidłowych lub wszystkich złych odpowiedzi w teście wyboru ( oczywiście błędy w pytaniach to tylko moja opinia i zgodnie z umową prometric – wyrażam jedynie swoje odczucia 🙂  )

Potwierdzenie zdania egzaminu przedstawiam poniżej:

EGZAMIN_70461

Jako, że Microsoft wrócił do MCP i zrezygnował z umieszczenia nazwy dla tego tytułu, więc w tego typu przypadkach będę umieszczał wycinek transcript-u, który można zobaczyć powyżej 🙂

Prezentacja z planowania strategii odtwarzania baz danych już dostępna !!!

Właśnie umieściłem na stronie materiały z prezentacji jaką miałem okazję poprowadzić w Akademii Marynarki Wojennej w Gdyni. Temat mojej prelekcji brzmiał:

Tworzenie strategii przywracania baz danych na przykładzie SQL Server 2012

Tych z was, którzy chcieliby przejrzeć slajdy zapraszam do działu materiały do pobrania 🙂

Pierwsze spotkanie PLSSUG Trójmiasto już wkrótce !!!

PLSSUG_LOGO

Już w czwartek 12 grudnia 2013 o godzinie 18:00 w siedzibie firmy Hempel w Gdańsku odbędzie się pierwsze spotkanie trójmiejskiego oddziału miłośników SQL Server (PLSSUG). Agenda spotkania przedstawia się następująco:

17:45 – 18:00: Rozpoczęcie spotkania

Prelegent: Łukasz Terlecki, lider PLSSUG Trójmiasto

18:00 – 19:00:  Pierwsze kroki w tworzeniu wymiarów za pomocą SSIS

Prelegent:  Maciej Skrzos

19:00 – 19:20: Pizza Time

19:15 – 20:20: Database Snapshot Geek Dive

Prelegent: Czarek Ołtuszyk

Tym z was, którzy są ciekawi o czym będę opowiadał, to mogę zdradzić, że są to różnego rodzaju mechanizmy wewnętrzne SQL Server-a związane z tworzeniem i używaniem migawek baz danych. Level sesji określiłbym na 400+, tak więc jeżeli podobnie jak ja jesteście geekami – powinno wam się spodobać 🙂

Link do strony spotkania znajduje się tutaj.

Na spotkanie obowiązuje wcześniejsza rejestracja.