di TechRepublic gratuito SQL Server newsletter, consegnato ogni Martedì, contiene hands-on suggerimenti che vi aiuteranno a diventare più abili con questo potente sistema di gestione di database relazionale. Iscriviti automaticamente oggi!

La saggezza generalmente accettata sulle stored procedure (o sprocs) è che poiché SQL può ottimizzarle e compilarle,vengono eseguite più rapidamente delle istruzioni SQL equivalenti eseguite da QueryAnalyzer (o forse passate da alcune app front-end come una pagina Web o VBprogram). Questo è vero in una certa misura.

Innanzitutto, è necessario sapere cosa fa SQL Server con un nuovo sproc. Al momento della creazione, controlla la sintassi. Se non trova errori, aggiunge lo sproc alle tabelle di sistema: sysobjects,sysdepends e syscomments (quest’ultimo memorizza il corpo dello sproc). Per impostazione predefinita, non compila lo sproc al momento della creazione.

Alla prima esecuzione dello sproc,SQL Server lo ottimizza e lo compila. Questo è quando SQL Server escogita un queryplan e lo memorizza nella sua cache di procedura. Nelle invocazioni successive, SQL guarda prima nella cache, trova lo sproc lì e non lo compila. Se lo sproc non è nella cache,SQL Server lo compila e lo inserisce nella cache.

La mia esperienza con l’opzione RICOMPILARE

Qualche tempo fa, stavo supportando una pagina di ricerca che consentiva agli utenti di cercare per una qualsiasi delle diverse colonne. Quindi la pagina ha chiamato uno sproc, passando un parametro per indicare quale colonna tosearch. Ho esaminato il parametro utilizzando un blocco CASE e quindi eseguito una delle diverse query, a seconda della colonna da cercare.

Sapevo che qualcosa non andava quando ho iniziato a testare la mia stored procedure allegedlyclever. In teoria, le prestazioni di ogni ricerca dovrebbero essere approssimativamente uguali, ma non è quello che è successo. Quando ho eseguito più ricerche, indipendentemente dall’ordine, la prima sarebbe stata veloce e le ricerche successive erano molto più lente.

Infine, mi sono reso conto che la prima volta che la procedura è stata chiamata, un piano di query è stato ideato e memorizzato nella cache. Aslong come ho cercato su quella colonna particolare, tutto avrebbe funzionato asexpected. Nel momento in cui ho cambiato colonne, tuttavia, le prestazioni sono crollate. Perché è successo?

La prima ricerca che ho eseguito ha creato un piano di query e storedit nella cache. Ad esempio, diciamo che stavo cercando sulla colonna OrderDate. Se ho commutato la ricerca alla colonna CompanyName, SQL userebbe ciecamente il queryplan memorizzato nella cache, cercando il nome della società di destinazione utilizzando OrderDateindex. Non c’è da stupirsi che le prestazioni precipiterebbero così drammaticamente.

La correzione è abbastanza semplice. Ho eseguito sprocsupplying WITH RECOMPILE option:

EXEC MySproc_Select '12/31/2004' WITH RECOMPILE

Questo dice a SQL Server di buttare via il piano di query esistente e costruirne un altro–ma solo questa volta.

È anche possibile aggiungere la RICOMPILAZIONE WITH direttamente alla storedprocedure subito prima della parola chiave AS. Questo dice a SQL Server di eliminare il piano query su ogni esecuzione dello sproc.

C’è anche una terza opzione. Avrei potuto creare uno sproc separato per ogni metodo di ricerca e quindi decidere quale eseguire all’interno del blocco CASE. In questo modo,il piano di interrogazioneassociato ai sub-sproc rimane nella cache, dove SQL può trarne vantaggio. Poiché ciascuno dei pignoricercato esattamente una colonna, non è necessario ricompilare.

La capacità di SQL Server di ottimizzare e compilare un storedprocedure è ottima ma, se non stai attento, può morderti quando lo aspetti meno. Ora che sai come affrontare il problema, forse ci sono nuove situazioni nel tuo database che potresti voler rivedere.