Zrychlení dotazů nad spatial indexy v SQL 2008
Na našem ambiciózním projektu www.ontheroad.to si už pár měsíců hrajeme se spatial indexy v SQL 2008.
Nedávno jsem intesivněji hledal, proč dotazy nad spatial indexy jsou někdy výrazně pomalejší, než bych očekával.
Úvod do situace
HW: 2xQuad Core 1.6 GHz, 8GB RAM, rychlé SATA disky v RAID 1
Data: 8 milionů bodů po celém světě, GPS souřadnice, geography data type
Typ dotazu: najdi mi body v okolí X kilometrů od libovolně zvolených GPS souřadnic
SQL dotaz po zjednodušení vypadá takto:
Select top(@count) ID from GeoObj where geo.STDistance(@point) < @km order by order by geo.STDistance(@point)
Výsledkem dotazu pro bod ukazující na Prahu a vzdálenost 30km vrací asi 20 000 záznamů.
Pohled do dokumentace praví, že přesně takto má vypadat SQL dotaz, aby se použil spatial index. Realita a pohled do execution planu však prozrazují úplně jiné věci.
Postup a řešení
1) pokud nepoužiju order by , spatial index se nepoužije vůbec a k hledání bodů se dělá table scan !!! Po 10-ti minutách 100% zatížení 8-mi CPU (vskutku nezvyklý pohled ;-) ) žádny výsledek
2) při použítí order by geo.STDistance(@point) se sice spatial index použije, ale nějak divně. Execution plan je velmi složitý, nejvíce času si bere clustered index seek nad primárním indexem tabulky (??) a agregační Hash Match. Dotaz trvá 30s poprvé, opakovaný kolem 12s. Pořád slabota.
3) Nezbývalo než rezignovat na evidetně blbně fungující vytváření execution planu u spatial indexu a trochu si "zahintovat" ;-)
dotaz
Select top(@count) ID from GeoObj with (index (SPATIAL_nameH))
where geo.STDistance(@point) < @km order by order by geo.STDistance(@point)
konečně dělá to, co má. V tomto primitivním dotazu používá spatial index tak jak má a výsledek dotazu je k dispozici vždy do 1.5 sekundy.
4) Zkoušel jsem, co s rychlostí dotazu udělá různá velikost tesselation gridu. Medium size pro všechny úrovně se ukázalo jako nejrychlejší řešení, i když v tomto konkrétním případě byly rozdíly max 20%.
Závěr
Vypadá to, že vytváření execution plánu pro dotaz nad spatial indexy nefunguje dobře. Pro všech naších 15 různých dotazů se spatial indexy bylo nutné použít HINT.
Takže příště u spatial indexů automaticky použít TABLE HINTs a počítat s časem na ladění dotazů.
PS: Musím poděkovat kolegovi Pavlovi Pěknicovi, bez kterého bych na toto řešení přišel o pár hodin později :-)