newsletter-ul gratuit SQL Server TechRepublic, livrate în fiecare marți, conține hands-on sfaturi care vă vor ajuta să devină mai adept cu acest puternic sistem de gestionare a bazelor de date relaționale. Abonați-vă automat astăzi!

înțelepciunea general acceptată cu privire la procedurile stocate (sau sprocs) este că,deoarece SQL le poate optimiza și compila, acestea rulează mai repede decât declarațiile SQL echivalente executate din QueryAnalyzer (sau poate transmise de la o aplicație front-end, cum ar fi o pagină Web sau VBprogram). Acest lucru este adevărat într-o oarecare măsură.

În primul rând, trebuie să știți ce face SQL Server cu un nou sproc. La momentul creării, verifică sintaxa. Dacă nu găsește erori, atunci adaugă sproc latabelele de sistem: sysobjects, sysdepends și syscomments (acesta din urmă stochează corpul sproc). În mod implicit, nu compilează sproc la momentul creării.

la prima execuție a sproc,SQL Server optimizează și compilează-l. Acest lucru este atunci când SQL Server concepe un queryplan și stochează în cache-ul său procedură. La invocările ulterioare, SQL se uită mai întâi în cache, găsește sproc acolo și nu o compilează. Dacă sproc nu este în cache,atunci SQL Server îl compilează și îl plasează în cache.

experiența mea cu opțiunea de recompilare

cu ceva timp în urmă, susțineam o pagină de căutare care permitea utilizatorilor săi să caute după oricare dintre mai multe coloane. Apoi, pagina a numit un sproc, trecând un parametru pentru a indica care coloană lacăutare. Am examinat parametrul folosind un bloc de caz și apoi am executat una dintremai multe interogări, în funcție de coloana de căutare.

știam că ceva nu este în regulă când am început să-mi testez procedura stocată de allegedlyclever. În teorie, performanța fiecărei căutări ar trebui să fie cel puțin aproximativ aceeași, dar nu asta s-a întâmplat. Când am efectuatcăutări multiple, indiferent de ordine, prima ar fi rapidă și căutările ulterioare au fost mult mai lente.

în cele din urmă, mi-am dat seama că prima dată când a fost apelată procedura, a fost conceput un plan de interogare și stocat în memoria cache. Atâta timp cât am căutat pe acea coloană special, totul ar funcționa asexpected. Cu toate acestea, în momentul în care am schimbat coloanele, performanța a scăzut. De ce s-a întâmplat asta?

prima căutare am efectuat creat un plan de interogare și storedit în memoria cache. De exemplu, spun că am fost în căutarea pe coloana OrderDate. Dacă am trecut căutarea în coloana CompanyName, SQL ar folosi orbește queryplan cache, căutând numele companiei țintă folosind OrderDateindex. Nu e de mirare că performanța ar scădea atât de dramatic.

remedierea este destul de simplă. Am executat sprocsupplying cu RECOMPILE opțiune:

EXEC MySproc_Select '12/31/2004' WITH RECOMPILE

Acest lucru spune SQL Server pentru a arunca planand interogare existente construi un altul-dar numai acest lucru o dată.

puteți adăuga, de asemenea, cu RECOMPILE direct la storedprocedure chiar înainte de cuvântul cheie AS. Acest lucru spune SQL Server pentru a arunca thequery plan pe fiecare execuție a sproc.

există și o a treia opțiune. Aș fi putut crea un sproc separat pentru fiecare metodă de căutare și apoi să decid care să execute în blocul de caz. În acest fel, planul de interogareasociate cu sub-sprocurile rămân în cache,unde SQL poate profita de ele. Deoarece fiecare dintre pinioanele căutat exact o coloană, nu este nevoie să recompilați.

capacitatea SQL Server de a optimiza și compila un storedprocedure este mare, dar, dacă nu ești atent, te poate musca atunci când least expect. Acum, că știi cum să se ocupe cu problema, poate că există afew situații în propria bază de date pe care ați putea dori să revizuiască.