Techrepublics gratis SQL Server-nyhetsbrev, levereras varje tisdag, innehåller praktiska tips som hjälper dig att bli mer skicklig med detta kraftfulla relationsdatabashanteringssystem. Prenumerera automatiskt idag!

den allmänt accepterade visdomen om lagrade procedurer (eller sprocs) är att eftersom SQL kan optimera och kompilera dem,kör de snabbare än motsvarande SQL-satser som körs från QueryAnalyzer (eller kanske skickas in från någon front-end-app som en webbsida eller VBprogram). Detta är sant till viss del.

först måste du veta vad SQL Server gör med en ny sproc. Vid skapande tid kontrollerar den syntaxen. Om det inte hittar några fel, lägger det till sproc tillsystemtabellerna: sysobjects, sysdepends och syscomments (den senare lagrar sproc: s kropp). Som standard kompilerar den inte sproc vid skapande tid.

vid första utförandet av sproc optimerar och sammanställer SQL Server den. Det här är när SQL Server utformar en queryplan och lagrar den i sin procedurcache. Vid efterföljande anrop ser SQL uti cachen först hittar sproc där ochkompilera inte det. Om sproc inte finns i cacheminnet sammanställer SQL Server den och placerar den i cacheminnet.

min erfarenhet med alternativet med kompilering

ett tag tillbaka stödde jag en söksida som tillåtedess användare att söka efter någon av flera kolumner. Då sidan kallas en sproc, passerar en parameter för att ange vilken kolumn tillsök. Jag undersökte parametern med ett FALLBLOCK och utförde sedan en avflera frågor, beroende på kolumnen att söka.

Jag visste att något var fel när jag började testa min påstådda lagrade procedur. I teorin bör utförandet av varje sökning åtminstone vara ungefär densamma, men det är inte vad som hände. När jag utfördeflera sökningar, oavsett ordning, skulle den första vara snabb ochefterföljande sökningar var mycket långsammare.

slutligen insåg jag att första gången proceduren kallades, utformades en frågeplan och lagrades i cacheminnet. Så länge som jag sökte på just den kolumnen skulle allt fungera som förväntat. I det ögonblick jag bytte kolumner sjönk dock prestandan. Varför hände detta?

den första sökningen jag utförde skapade en frågeplan och storedit i cacheminnet. Till exempel, säga att jag sökte på kolumnen OrderDate. Om jag bytte sökningen till kolumnen CompanyName skulle SQL blint använda den cachade queryplanen och söka efter målföretagets namn med OrderDateindex. Inte konstigt att prestanda skulle sjunka så dramatiskt.

fixen är ganska enkel. Jag utförde sprocsupplying med RECOMPILE alternativet:

EXEC MySproc_Select '12/31/2004' WITH RECOMPILE

detta berättar SQL Server att kasta bort den befintliga frågeplanenoch bygga en annan-men bara den här en gång.

Du kan också lägga till med RECOMPILE direkt till storedproceduren strax före as-sökordet. Detta berättar SQL Server att kasta ut thequery Planen på varje utförande av sproc.

det finns också ett tredje alternativ. Jag kunde ha skapat aseparate sproc för varje sökmetod, och sedan bestämma vilken som ska utföras inom FALLBLOCKET. På så sätt förblir frågeplanenassocierad med sub-sprocs kvar i cacheminnet,där SQL kan dra nytta av dem. Sedan var och en av kedjehjulensökte exakt en kolumn, Det finns inget behov av att kompilera om.

SQL Servers förmåga att optimera och kompilera en storedprocedure är bra men om du inte är försiktig kan den bita dig när du minst förväntar dig det. Nu när du vet hur du ska hantera problemet kanske det finns afew-situationer i din egen databas som du kanske vill besöka igen.