TechRepublic gratis SQL Server nyhetsbrev, levert hver tirsdag, inneholder hands-on tips som vil hjelpe deg å bli flinkere med denne kraftige relasjonsdatabase management system. Abonner automatisk i dag!den allment aksepterte visdom om lagrede prosedyrer (eller sprocs) er at FORDI SQL kan optimalisere og kompilere dem, kjører de raskere enn tilsvarende SQL-setninger utført Fra QueryAnalyzer (eller kanskje sendt inn fra noen front-end app som En Nettside eller VBprogram). Dette er sant til en viss grad.
først må du vite hva SQL Server gjør med en ny sproc. Ved opprettelsestid, kontrollerer den syntaksen. Hvis det ikke finner noen feil, legger det til sproc-systemtabellene: sysobjects, sysdepends og syscomments (sistnevnte lagrer kroppen til sproc). Som standard kompilerer den ikke sproc ved opprettelsestid.
VED første kjøring av sproc optimaliserer OG kompilerer SQL Server DEN. DETTE er når SQL Server utarbeider en queryplan og lagrer den i sin prosedyre cache. PÅ etterfølgende invokasjoner SER SQL uti hurtigbufferen først, finner sproc der, og kompilerer den ikke. Hvis sproc ikke er i hurtigbufferen, kompilerer SQL Server den og plasserer den i hurtigbufferen.
min erfaring med ALTERNATIVET MED REKOMPILERING
En stund tilbake støttet jeg en søkeside som tillot at brukerne kunne søke etter noen av flere kolonner. Da kalte siden en sproc, passerer en parameter for å indikere hvilken kolonne tilsøk. Jeg undersøkte parameteren ved hjelp av EN SAKSBLOKK, og utførte deretter en av flere spørringer, avhengig av kolonnen for å søke.
jeg visste at noe var galt da jeg begynte å teste min allegedlyclever lagret prosedyre. I teorien bør ytelsen til hvert søk i det minste være omtrent det samme, men det er ikke det som skjedde. Når jeg utførteflere søk, uavhengig av rekkefølgen, ville den første være rask, ogfølgende søk var mye tregere.
Til Slutt innså Jeg at første gang prosedyren ble kalt, ble en spørringsplan utarbeidet og lagret i hurtigbufferen. Så lenge jeg søkte på den aktuelle kolonnen, ville alt fungere som forventet. I det øyeblikket jeg byttet kolonner, derimot, ytelsen falt. Hvorfor skjedde dette?
det første søket jeg utførte, opprettet en spørringsplan og lagret i hurtigbufferen. For eksempel, si at jeg søkte på kolonnen OrderDate. HVIS jeg byttet søket Til CompanyName-kolonnen, VILLE SQL blindt bruke den bufrede queryplanen, og søke etter målet firmanavn ved Hjelp Av OrderDateindex. Ikke rart resultatene ville stuper så dramatisk.
løsningen er ganske enkel. Jeg utførte sprocsupplying MED RECOMPILE alternativet:
EXEC MySproc_Select '12/31/2004' WITH RECOMPILE
dette forteller SQL Server å kaste bort den eksisterende spørringsplanen og bygge en annen-men bare denne en gang.
Du kan også legge TIL MED RECOMPILE direkte til storedprocedure rett før som søkeord. DETTE forteller SQL Server å kaste utquery plan på hver utførelse av sproc.
Det er også et tredje alternativ. Jeg kunne ha opprettet aseparate sproc for hver søkemetode, og deretter bestemme hvilken som skal utføres i SAKSBLOKKEN. På den måten forblir spørringen planassociated med sub-sprocs i hurtigbufferen, DER SQL kan dra nytte av dem. Siden hver av kjedenesøkt nøyaktig en kolonne, det er ikke nødvendig å kompilere.SQL Server evne til å optimalisere og kompilere en storedprocedure er stor, men hvis du ikke er forsiktig, kan det bite deg når du minstforvent det. Nå som du vet hvordan du skal håndtere problemet, er det kanskje enfå situasjoner i din egen database som du kanskje vil besøke.