Pozor na SP_EXECUTESQL

Tomáš Herceg       27. 8. 2009       SQL, Databáze       6910 zobrazení

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ů.

 

hodnocení článku

0       Hodnotit mohou jen registrované uživatelé.

 

Nový příspěvek

 

Diskuse: Pozor na SP_EXECUTESQL

Tak to je docela bída, že se sp_executeSQL takhle chová. Protože například LINQ to SQL používá interně na pozadí rovněž sp_executeSQL. Nicméně, co řešení ve formě přepsat dotaz na uloženou proceduru? Tam by měl kompilátor sestavit optimální plán snad i s ohledem na statistiky sloupců a indexů, není liž pravda? A pak bych ještě zkusil sp_updatestats.

Natahování webu se každopádně zrychlilo ŘÁDOVĚ.

nahlásit spamnahlásit spam 1 / 1 odpovědětodpovědět

U stored procedur dělá víceméně to samé, ale ještě hůře. Tam při prvním spuštení procedury parametrys zná, takže si často exekuční plán sestaví pro ně. A při příštím volání používá plán, který není pro jiné hodnoty parametrů optimální.

Jako sp_executesql má svůj význam, chápu, proč ji tam daly, protože většina aplikací použvá pár různých selectů a volá je tisíckrát pro rozličné hodnoty parametrů. Navíc když jsem do dotazu přidal hint, aby se použil index, který jsem jí vytvořil, tak se ta rychlost snížila skoro na úroveň perfektně optimalizovaného dotazu pro konkrétní hodnoty. Takže spíš za to mohlo nepoužití indexu než sp_executesql.

nahlásit spamnahlásit spam 0 odpovědětodpovědět
                       
Nadpis:
Antispam: Komu se občas házejí perly?
Příspěvek bude publikován pod identitou   anonym.

Nyní zakládáte pod článkem nové diskusní vlákno.
Pokud chcete reagovat na jiný příspěvek, klikněte na tlačítko "Odpovědět" u některého diskusního příspěvku.

Nyní odpovídáte na příspěvek pod článkem. Nebo chcete raději založit nové vlákno?

 

  • Administrátoři si vyhrazují právo komentáře upravovat či mazat bez udání důvodu.
    Mazány budou zejména komentáře obsahující vulgarity nebo porušující pravidla publikování.
  • Pokud nejste zaregistrováni, Vaše IP adresa bude zveřejněna. Pokud s tímto nesouhlasíte, příspěvek neodesílejte.

přihlásit pomocí externího účtu

přihlásit pomocí jména a hesla

Uživatel:  
Heslo:  

zapomenuté heslo

 

založit nový uživatelský účet

zaregistrujte se

 
zavřít

Nahlásit spam

Opravdu chcete tento příspěvek nahlásit pro porušování pravidel fóra?

Nahlásit Zrušit

Chyba

zavřít

feedback