TechRepublic je zdarma SQL Server newsletter, dodávány každé úterý, obsahuje praktické tipy, které vám pomohou stát se více adept s tento výkonný relační databázový systém. Automaticky se přihlaste k odběru ještě dnes!

obecně přijímány moudrost o uložené procedury (nebo sprocs) je, že, protože SQL lze optimalizovat a sestavovat je,oni běží rychleji než ekvivalentní SQL příkazy provedeny od QueryAnalyzer (nebo snad prošel v některé z front-end aplikace, jako jsou Webové stránky nebo VBprogram). To je do jisté míry pravda.

nejprve musíte vědět, co SQL Server dělá s novým sproc. V době vytvoření kontroluje syntaxi. Pokud nenajde žádné chyby, přidá sproc do systémových tabulek: sysobjects, sysdepends a syscomments (ten ukládá tělo sproc). Ve výchozím nastavení není kompilovat sproc v době vytvoření.

při prvním spuštění sproc SQL Server optimalizuje a zkompiluje. To je, když SQL Server vymyslí queryplan a uloží jej do mezipaměti procedur. Při následných vyvoláních SQL nejprve vyhledá mezipaměť, najde tam sproc a nekompiluje jej. Pokud sproc není v mezipaměti, SQL Server jej zkompiluje a umístí do mezipaměti.

moje zkušenost s možností s REKOMPILEM

před chvílí jsem podporoval vyhledávací stránku, která umožnila uživatelům vyhledávat podle některého z několika sloupců. Pak se stránka nazývá sproc a předává parametr, který označuje, který sloupecvyhledávání. Zkoumala jsem to parametr, pomocí PŘÍPADOVÉ blok, a pak popraven jeden z řady dotazů, v závislosti na sloupci vyhledávat.

věděl jsem, že něco není v pořádku, když jsem začal testovat svůj údajně uložený postup. Teoreticky by výkon každého vyhledávání měl být přibližně stejný, ale to se nestalo. Když jsem provedl více vyhledávání, bez ohledu na pořadí, první by bylo rychlé anásledné vyhledávání bylo mnohem pomalejší.

nakonec jsem si uvědomil, že při prvním volání procedury byl navržen plán dotazu a uložen do mezipaměti. Jak dlouho jsem hledal v tomto konkrétním sloupci, všechno by fungovalo podle očekávání. Ve chvíli, kdy jsem prohodil kolony, však výkon klesal. Proč se to stalo?

první vyhledávání, které jsem provedl, vytvořilo plán dotazů a uložilo je do mezipaměti. Například, řekněme, že jsem hledal ve sloupci OrderDate. Pokud jsem přepnul vyhledávání do sloupce CompanyName, SQL by slepě použil queryplan uložený v mezipaměti a hledal název cílové společnosti pomocí OrderDateindex. Není divu, že výkon by se tak dramaticky propadl.

oprava je poměrně jednoduchá. Já proveden sprocsupplying S PŘEKOMPILOVAT option:

EXEC MySproc_Select '12/31/2004' WITH RECOMPILE

říká serveru SQL Server zahodit stávající dotazu plán postavit další–ale jen jednou.

můžete také přidat s RECOMPILE přímo do storedprocedure těsně před klíčovým slovem AS. To říká SQL Server vyhodit thequery plán na každém provedení sproc.

existuje také třetí možnost. Mohl jsem vytvořit samostatný sproc pro každou metodu vyhledávání a pakrozhodnout, který z nich se má spustit v bloku případů. Tímto způsobem zůstává plán dotazu spojený s dílčími sproky v mezipaměti, kde je SQL může využít. Protože každý z řetězcůvyhledal přesně jeden sloupec, není třeba rekompilovat.

schopnost SQL Serveru optimalizovat a zkompilovat storedprocedure je skvělá, ale pokud si nejste opatrní, může vás kousnout, když si to pronajmete. Nyní, když víte, jak se s tímto problémem vypořádat, možná existujímálo situací ve vaší vlastní databázi, které byste mohli chtít znovu navštívit.