Parametr dla procedury to nie to samo co zmienna w zapytaniu ad-hoc. Wykorzystanie planów wykonania.

29-paź-2011

Ten przykład pokaże, że co innego jest napisać i dobrze wykorzystać procedurę składowaną z parametrami, a co innego ten sam kod wykonać jako zapytanie ad-hoc, nawet jeżeli pewne wartości do zapytania będą przekazywane poprzez zmienne.

Będziemy pracować w testowej bazie danych:

use AdventureWorks2008R2

Zauważ, jak zachowuje się poniższe zapytanie:

SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '2005-07-21′ AND '2005-07-22′

jeśliby zajrzeć do planu wykonania zobaczysz, że robi table scan. Gdyby nasi użytkownicy mieli częściej wykonywać tego rodzaju analizy, to możemy dla nich utworzyć indeks:

CREATE NONCLUSTERED INDEX IX_SalesOrderHeader_OrderDate
ON Sales.SalesOrderHeader(OrderDate)

Zobaczmy czy nasze zapytanie zauważyło tę zmianę:

SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '2005-07-21′ AND '2005-07-22′

O tak. Tym razem optymalizator zaproponował, że wykona nonclustered index seek. Super! To zobaczmy, co się stanie, jeżeli sparametryzujemy kwerendę:

–zmieniam date na zmienna:
DECLARE @d1 DATETIME = '2005-07-21′
DECLARE @d2 DATETIME = '2012-07-22′
SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN @d1 AND @d2;

Zapytanie zadziałało, ale wykonany został table scan! Co najmniej, jakby serwer nie zauważył, że w zmiennych nadal znajdują sie te same dane. Na całe szczęście dysponujemy hintami (wskazówkami), którymi można wskazać serwerowi, że zapytanie ma wykonać tak, jakby zmienne miały określone wartości. Wspomniany hint to OPTIMIZE FOR. Zaobserwuj plan wykonania tego zapytania:

–dodaje hint OPTIMIZE FOR:
DECLARE @d1 DATETIME = '2005-07-21′
DECLARE @d2 DATETIME = '2012-07-22′
SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN @d1 AND @d2
OPTION ( OPTIMIZE FOR (@d1 = '2005-07-21′, @d2 = '2005-07-22′) );

Tym razem serwer zauważył, że zapytanie ma zoptymalizować dla dat tak, jakbym zawsze pytał o wskazane w OPTIMIZE FOR wartości. Jeżeli dorzucimy jeszcze przełącznik SET STATISTICS IO ON, to zobaczymy także ile stron było przy tym odczytywanych:

SET STATISTICS IO ON
DECLARE @d1 DATETIME = '2005-07-21′
DECLARE @d2 DATETIME = '2005-07-22′
SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN @d1 AND @d2
OPTION ( OPTIMIZE FOR (@d1 = '2005-07-21′, @d2 = '2005-07-22′) );

U mnie wynik wynisił 47 stron.

Co się jednak stanie jeżeli zmienimy wartości przekazanych zmiennych na mniej trafne? Zobacz analizę dla kilku lat:

SET STATISTICS IO ON
DECLARE @d1 DATETIME = '2005-07-21′
DECLARE @d2 DATETIME = '2012-07-22′
SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN @d1 AND @d2
OPTION ( OPTIMIZE FOR (@d1 = '2005-07-21′, @d2 = '2005-07-22′) );

W moim przypadku optymalizator nadal zdecydował się na wykonanie table seek, ale kosztowało go to odczytaniem 94093 stron.

To na zakończenie zobaczmy jeszcze, jak zachowa się to samo zapytanie 'opakowane’ w procedurę składowaną:

CREATE PROC GetOrders @d1 DATETIME, @d2 DATETIME
AS
SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN @d1 AND @d2
go

Wywołujemy przygotowaną procedurę i …

EXEC GetOrders '2005-07-21′, '2005-07-22′

bez żadnych kombinacji, bez dodawania hintów OPTIMIZE FOR procedura znalazła poprawny plan wykonania. Oczywiście pamiętajmy, że raz wygenerowany plan procedury, może sprawić, że przy kolejnych wywołaniach procedury wykona się ona nie optymalnie, bo z racji zmienionych parametrów plan wykonania powinien być inny. Pisałem o tym szerzej tutaj

Niemniej tym razem procedura składowana wygrała ponad zapytaniem ze zmiennymi!

Zainteresował Cię ten temat. Skorzystaj ze szkolenia 6232 w bit Polska, gdzie opowiadam między innymi na ten temat 🙂