TechRepublic’ s gratis SQL Server nieuwsbrief, geleverd elke dinsdag, bevat hands-on tips die u zullen helpen meer bedreven met deze krachtige relationele database management systeem. Schrijf je vandaag automatisch in!

de algemeen aanvaarde wijsheid over opgeslagen procedures (of sprocs) is dat omdat SQL ze kan optimaliseren en compileren,ze sneller draaien dan de equivalente SQL-statements uitgevoerd vanuit QueryAnalyzer (of misschien doorgegeven vanuit een front-end app zoals een webpagina of Vbprogramma). Dit is tot op zekere hoogte waar.

eerst moet u weten wat SQL Server doet met een nieuwe sproc. Tijdens het aanmaken controleert het de syntaxis. Als het geen fouten vindt, dan voegt het de sproc toe aan de systeemtabellen: sysobjects, sysdepends,en syscomments (de laatste slaat de body van de sproc op). Standaard compileert het sproc niet tijdens het aanmaken.

bij de eerste uitvoering van de sproc optimaliseert en compileert SQL Server het. Dit is wanneer SQL Server een queryplan ontwerpt en het opslaat in de procedure cache. Bij volgende aanroepingen zoekt SQL eerst in de cache, vindt de sproc daar, en compileert het niet. Als de sproc niet in de cache zit,dan compileert SQL Server het en plaatst het in de cache.

mijn ervaring met de optie met hercompileren

een tijdje terug ondersteunde ik een zoekpagina die gebruikers toestaat te zoeken op een van de verschillende kolommen. Dan de pagina genoemd een sproc, het passeren van een parameter aan te geven welke kolom te zoeken. Ik onderzocht de parameter met behulp van een CASE blok, en vervolgens uitgevoerd een vanseveral queries, afhankelijk van de kolom te zoeken.

Ik wist dat er iets niet klopte toen ik mijn opgeslagen procedure begon te testen. In theorie zouden de prestaties van elke zoekopdracht tenminste ongeveer hetzelfde moeten zijn, maar dat is niet wat er gebeurde. Toen ik meerdere zoekopdrachten uitvoerde, ongeacht de volgorde, zou de eerste snel zijn, en de daaropvolgende zoekopdrachten waren veel langzamer.

uiteindelijk realiseerde ik me dat de eerste keer dat de procedure werd aangeroepen, een query plan werd bedacht en opgeslagen in de cache. Zolang ik op die kolom zocht, zou alles werken zoals verwacht. Het moment dat ik van kolom wisselde, echter, de prestaties kelderde. Waarom is dit gebeurd?

De eerste zoekopdracht die ik uitvoerde maakte een query plan en storedit in de cache. Bijvoorbeeld, stel dat ik zocht op de kolom besteldatum. Als ik overschakelde de zoekopdracht naar de bedrijfsnaam kolom, SQL zou blindelings gebruik maken van de cache queryplan, zoeken naar de naam van het doelbedrijf met behulp van de OrderDateindex. Geen wonder dat de prestaties zo dramatisch zouden kelderen.

de oplossing is vrij eenvoudig. Ik heb de sprocsupplying optie met hercompileren uitgevoerd:

EXEC MySproc_Select '12/31/2004' WITH RECOMPILE

Dit vertelt SQL Server om het bestaande query plan weg te gooien en een andere te bouwen–maar slechts deze keer.

U kunt de WITH RECOMPILE ook direct toevoegen aan de storedprocedure vlak voor het sleutelwoord AS. Dit vertelt SQL Server om het query plan te gooien op elke uitvoering van de sproc.

er is ook een derde optie. Ik had een aparte sproc kunnen maken voor elke zoekmethode, en vervolgens uitzoeken welke men moet uitvoeren binnen de CASE block. Op die manier, de query planassocieerd met de sub-tandwielen blijft in de cache,waar SQL kan profiteren van hen. Aangezien elk van de sprocssonderzocht precies één kolom, is het niet nodig om opnieuw te compileren.

SQL Server ‘ s vermogen om een storedprocedure te optimaliseren en compileren is geweldig, maar als je niet voorzichtig bent, kan het je bijten als je het niet verwacht. Nu dat je weet hoe om te gaan met het probleem, misschien zijn er enkele nieuwe situaties in uw eigen database die u zou willen herzien.