Posledních pár měsíců bylo načítání titulní stránky VbNetu relativně pomalé. Před pár týdny mě to konečně naštvalo tak, že jsem zjistil, co to dělá (seznam Moje témata), ale protože jsem neměl čas, jen jsem ho schoval.
Dnes jsem konečně začal zjišťovat, čím to bylo, a proč sice složitý, ale nikterak zapeklitý dotaz trval na lokálu kolem 15 sekund (na ostrém serveru to bylo o trochu rychlejší).
Ten dotaz se díval do tabulky s tématy ve fóru, pro každé téma si joinem vytáhl první příspěvek a v něm hledal shodu na autora nebo IP adresu (aby to nějak fungovalo i nepřihlášeným uživatelům). Bylo mi jasné, že při několika tisících témat ve fóru nebude hledání podle IP, což je pole typu string, nikterak rychlé, proto jsem samozřejmě na sloupci udělal index. Nepomohlo to ovšem vůbec.
Zkopíroval jsem tedy SELECT z aplikace do SQL Server Management Studia, před něj nadeklaroval a zinicializoval ty dva parametry, které uvnitř měl. Když to celé spustím, je to hotové hned, žádných 15 sekund. Nechápavě na to koukám a po chvíli spouštím profiler.
Dotaz stejný, akorát aplikace ho spouští přes SP_EXECUTESQL, hodnoty parametrů stejné, ale z management studia je to hned a z aplikace za 15 sekund. Nechápu.
Až teprve potom jsem si přečetl, na co že vlastně ta SP_EXECUTESQL, jíž předhodíte jako jeden parametr dotaz, jako druhý deklarace parametrů a jako další parametry jejich hodnoty. Slouží k cacheování exekučních plánů. Když totiž na SQL Server pošleme dotaz, přijde na řadu optimalizátor, který dotaz sestaví, zkompiluje a protože si o tabulkách uchovává dost podrobné statistiky, upraví a zoptimalizuje dotaz tak, aby byl rychlý.
No jo, ale procedura SP_EXECUTESQL při kompilaci dotazu nebere v úvahu hodnoty parametrů – jde jí o to, aby se dotaz zkompiloval obecně (protože se určitě bude spouštět mnohokrát, akorát s jinými parametry). To je ovšem kámen úrazu – statistiky se moc hodí a optimalizátor je schopen dotaz zoptimalizovat mnohem lépe, pokud hodnoty parametrů zná.
Jaktože se ale SP_EXECUTESQL používá? On ji v .NETu používá samotný SqlCommand, takže pokud chcete vytvářet dotaz s parametry a děláte to klasickým způsobem, použije se právě tato procedura. Ano, v drtivé většině případů to má své opodstatnění, ale občas prostě narazíte na situaci, kde se to nehodí a kde by bylo lepší optimalizovat dotaz pro konkrétní hodnoty parametrů.
Řešení
Vytvoření indexu nepomohlo, i když vlastně mělo. SQL Server ale jen nenapadlo, že ho má použít. Přidal jsem do dotazu v aplikaci tedy hint, aby použil ten index, protože já vím, že když ho použije, najde ty stringy podstatně rychleji. Z 15 sekund jsem se dostal na 1 sekundu, což je ale ještě pořád dost. Pokud parametry vrazím před dotaz pomocí DECLARE @IdAddress VARCHAR(50) a nastavím jí hodnotu SETem, dostanu se i bez hintu pod 100ms.
Nejsem databázový expert a věřím tomu, že kdyby se dotaz ještě více zoptimalizoval (jakože to určitě jde, netvrdím, že je hezky napsaný), dostal bych se na stejný čas jako když dám optimalizátoru rovnou i hodnoty parametrů.
Až se budete divit, proč je dotaz v aplikaci pomalý a v Server Management Studiu ten samý dotaz proběhne hned, tak vězte, že za to může SP_EXECUTESQL a že byste měli svůj dotaz zoptimalizovat (např. přidat indexy, pokud je to vhodné) tak, aby nemusel příliš spoléhat na konkrétní hodnoty parametrů.