Minule jsme si ukázali jednoduchý plán, který byl vytvořen přímo v režii Query Optimizeru. Při psaní komplexnějších dotazů si určitě budete chtít testovat rychlost jejich běhu. Jinými slovy budete se zajímat o to, jak se změní rychlost zpracování dotazu, když např. přidáte, smažete, změníte index nad tabulkou, vynutíte parametrizaci dotazu, atp. Právě k laborování a zkoušení se výborně hodí možnost přesvědčit Query Optimizer, aby vytvořil plán trochu jinak.
Ukázka původního dotazu, který budeme postupně modifikovat a vynucovat si změny výsledného plánu
SELECT P.LastName ,P.FirstName ,E.EmailAddress FROM Person.Person AS P LEFT JOIN Person.EmailAddress AS E ON E.BusinessEntityID = P.BusinessEntityID WHERE P.LastName = 'Smith'
Originální plán vypadá takto
Existuje několik způsobů, jakými Optimizer spojuje tabulky. Na výše uvedeném obrázku vidíme Merge Join. Budeme-li chtít změnit způsob spojení tabulek, lze to provést pomocí tzv. Join Hints.
Merge Join
Je poměrně efektivní spojení dvou tabulek, máme-li na vstupu nejlépe dvě setříděné sady dat, které vůči sobě porovnáváme a v případě shody provedeme sloučení. V našem případě chceme sloučit informace z tabulky [Person].[Person] s informacemi z tabulky [Person].[EmailAddress]. Algoritmus tohoto spojení vezme nejprve první záznam ze setříděné tabulky [Person].[Person] a načte si hodnotu z pole [BusinessEntityID]. Vezme první záznam z tabulky [Person].[EmailAddress], načte si hodnotu z pole [BusinessEntityID]. Pokud je hodnota pole [BusinessEntityID] v tabulce [Person].[EmailAddress] menší, načte se další záznam z tabulky [Person].[EmailAddress]. Takto se pokračuje, dokud hodnota [BusinessEntityID] není v obou tabulkách stejná. Rovnají-li se hodnoty, jsou řádky sloučeny. Pokračuje se následujícím řádkem z tabulky [Person].[EmailAddress], dokud hodnota pole [BusinessEntityID] v tabulce [Person].[EmailAddress] není větší. Jakmile je větší, znamená to, že již neexistuje žádný další vyhovující záznam a pokračuje se novým záznamem z [Person].[Person].
Dotaz, kterým si vynutíme použití Merge Join je následující
SELECT P.LastName ,P.FirstName ,E.EmailAddress FROM Person.Person AS P LEFT MERGE JOIN Person.EmailAddress AS E ON E.BusinessEntityID = P.BusinessEntityID WHERE P.LastName = 'Smith'
Vygenerovaný plán naleznete na obrázku výše.
Celková náročnost dotazu pro porovnání
Jak je patrné, plán upraveného dotazu s použitím MERGE JOIN je stejný jako původní plán, který si Optimizer sestaví nativně. Pro tento typ spojení je limitujícím faktorem nutné setřídění dat podle stejných kritérií. Pokud je setřídění dat příliš nákladné, pak je použit jiný typ.
Hash Join
Prvním krokem je určení počtu řádků obou tabulek, které jsou na vstupu této operace. Počet řádků se určuje na základě statistik, které v případě jejich neaktuálnosti nemusí odpovídat realitě. Tabulka, která obsahuje méně řádků ([Person].[Person]), se nazývá build input a na jejím základě bude vytvořena hash tabulka. Hash tabulku si můžeme představit jednoduše tak, že se vezmou data z jednoho či více sloupců (záleží, kolik sloupců chceme porovnávat) a z dat se vytvoří hash, který se uloží do hash tabulky. Každému řádku v hash tabulce tedy odpovídá právě jeden řádek v originální tabulce. Je to jisté zjednodušení, kdy ve výsledku porovnáváme pouze jednu hodnotu v jednom sloupci z hash tabulky místo hodnot v několika sloupcích v originální tabulce. Hash tabulka může být uložena přímo v paměti v případě relativně malé hash tabulky. Je-li nedostatek volné paměti, je uložena do TempDB. Máme tedy připravenou a naplněnou hash tabulku. Druhá větší tabulka na vstupu ([Person].[EmailAddress]) se nazývá probe input. Z větší tabulky je postupně čte řádek po řádku, z příslušných sloupců počítán hash a porovnáván s hodnotami v hash tabulce. V případě shody jsou řádky poslány dále ke zpracování.
Dotaz, kterým si vynutíme použití Hash Join je následující
SELECT P.LastName ,P.FirstName ,E.EmailAddress FROM Person.Person AS P LEFT HASH JOIN Person.EmailAddress AS E ON E.BusinessEntityID = P.BusinessEntityID WHERE P.LastName = 'Smith'
Vygenerovaný plán
Celková náročnost dotazu pro porovnání
Nested Loops Join
Jedná se o mechanismus spojování dvou tabulek, kdy se prochází zdrojová tabulka řádek po řádku a dohledává se odpovídající řádky ve druhé připojované tabulce. Horní vstup se označuje jako outer input. v našem příkladě je to tabulka [Person].[Person]. V tabulce bylo nalezeno celkem 103 záznamů s LastName = ‚Smith‘.
Spodní tabulka ([Person].[EmailAddress]) je označována jako inner input. Pro každý řádek outer input tabulky je prohledána celá tabulka inner input, zda-li neexistují řádky odpovídající řádku z outer input.
Na obrázcích si povšimněte počet volání (Actual Executions) pro outer a inner input. Z algoritmu je vidět, že tento typ spojení je vhodný pro dvě tabulky, z nichž jedna obsahuje mnohem méně řádků než druhá a zároveň ve druhé tabulce je index, který pokrývá sloupce, podle nichž se filtruje.
Detail vlastní Nested Loop operace je na následujícím obrázku
Dotaz, kterým si vynutíme použití Loop Join je následující
SELECT P.LastName ,P.FirstName ,E.EmailAddress FROM Person.Person AS P LEFT LOOP JOIN Person.EmailAddress AS E ON E.BusinessEntityID = P.BusinessEntityID WHERE P.LastName = 'Smith'
Vygenerovaný plán
Celková náročnost dotazu pro porovnání
Porovnáme-li celkovou náročnost jednotlivých typů spojení tabulek, je vidět, že nejméně náročné je spojení typu Merge Join. Přesně to, které si původně vybral Optimizer.