L’assegnazione dei parametri di input della stored procedure alle variabili locali aiuta a ottimizzare la query?

Ho una stored procedure che accetta 5 parametri di input. La procedura è un po ‘complicata e richiede circa 2 minuti per essere eseguita. Sono in fase di ottimizzazione della query.

Quindi, la mia domanda è, aiuta sempre ad assegnare i parametri di input alle variabili locali e quindi utilizzare le variabili locali nella procedura?

Se sì, come aiuta?

Non cercherò di spiegare tutti i dettagli dello sniffing dei parametri, ma in breve, non sempre non aiuta (e può ostacolare).

Immaginate una tabella (T) con una chiave primaria e una colonna Date indicizzata (A), nella tabella ci sono 1.000 righe, 400 hanno lo stesso valore di A (diciamo oggi 20130122), le restanti 600 righe sono i successivi 600 giorni , quindi solo 1 record per data.

Questa query:

 SELECT * FROM T WHERE A = '20130122'; 

Produrrà un piano di esecuzione diverso per:

 SELECT * FROM T WHERE A = '20130123'; 

Poiché le statistiche indicheranno che per le prime 400 righe su 1.000 verranno restituite, l’ottimizzatore dovrebbe riconoscere che una scansione della tabella sarà più efficiente di una ricerca di segnalibri, mentre la seconda produrrà solo 1 righe, quindi la ricerca di un segnalibro sarà molto più efficiente.

Ora, tornando alla tua domanda, se abbiamo fatto questa procedura:

 CREATE PROCEDURE dbo.GetFromT @Param DATE AS SELECT * FROM T WHERE A = @Param 

Quindi corri

 EXECUTE dbo.GetFromT '20130122'; --400 rows 

Verrà utilizzato il piano di query con la scansione della tabella, se la prima volta che viene eseguito si utilizza ‘20130123’ come parametro per memorizzare il piano di ricerca dei segnalibri. Fino a quando la procedura non verrà ricompilata, il piano rimarrà lo stesso. Fare qualcosa del genere:

 CREATE PROCEDURE dbo.GetFromT @Param VARCHAR(5) AS DECLARE @Param2 VARCHAR(5) = @Param; SELECT * FROM T WHERE A = @Param2 

Quindi questo viene eseguito:

 EXECUTE dbo.GetFromT '20130122'; 

Mentre la procedura è compilata in un colpo solo, non scorre correttamente, quindi il piano di query creato alla prima compilazione non ha idea che @ Param2 diventerà lo stesso di @param, quindi l’ottimizzatore (senza sapere quante righe su expect) presumerà 300 verrà restituito (30%), in quanto tale considererà una scansione della tabella più efficiente di una ricerca di segnalibri. Se si esegue la stessa procedura con ‘20130123’ come parametro, si otterrebbe lo stesso piano (indipendentemente dal parametro con cui è stato prima richiamato) perché le statistiche non possono essere utilizzate per un valore non riconosciuto. Quindi, l’esecuzione di questa procedura per ‘20130122’ sarebbe più efficiente, ma per tutti gli altri valori sarebbe meno efficiente rispetto ai parametri locali (supponendo che la procedura senza parametri locali sia stata invocata per la prima volta con ‘20130122’)


Alcune query da demonstate in modo da poter visualizzare i piani di esecuzione per te

Creare schema e dati di esempio

 CREATE TABLE T (ID INT IDENTITY(1, 1) PRIMARY KEY, A DATE NOT NULL, B INT,C INT, D INT, E INT); CREATE NONCLUSTERED INDEX IX_T ON T (A); INSERT T (A, B, C, D, E) SELECT TOP 400 CAST('20130122' AS DATE), number, 2, 3, 4 FROM Master..spt_values WHERE type = 'P' UNION ALL SELECT TOP 600 DATEADD(DAY, number, CAST('20130122' AS DATE)), number, 2, 3, 4 FROM Master..spt_values WHERE Type = 'P'; GO CREATE PROCEDURE dbo.GetFromT @Param DATE AS SELECT * FROM T WHERE A = @Param GO CREATE PROCEDURE dbo.GetFromT2 @Param DATE AS DECLARE @Param2 DATE = @Param; SELECT * FROM T WHERE A = @Param2 GO 

Esegui le procedure (mostrando il piano di esecuzione effettivo):

 EXECUTE GetFromT '20130122'; EXECUTE GetFromT '20130123'; EXECUTE GetFromT2 '20130122'; EXECUTE GetFromT2 '20130123'; GO EXECUTE SP_RECOMPILE GetFromT; EXECUTE SP_RECOMPILE GetFromT2; GO EXECUTE GetFromT '20130123'; EXECUTE GetFromT '20130122'; EXECUTE GetFromT2 '20130123'; EXECUTE GetFromT2 '20130122'; 

Si vedrà che la prima volta GetFromT viene compilato GetFromT utilizza una scansione della tabella e lo mantiene quando viene eseguito con il parametro ‘20130122’, GetFromT2 utilizza anche una scansione della tabella e conserva il piano per ‘20130122’.

Dopo che le procedure sono state impostate per la ricompilazione e l’esecuzione di nuovo (nota in un ordine diverso), GetFromT utilizza un loopup del segnalibro e mantiene il piano per ‘20130122’, nonostante abbia precedentemente ritenuto che una scansione della tabella sia un piano più appropriato. GetFromT2 non è interessato dall’ordine e ha lo stesso piano di prima del ricompliateion.

Quindi, in breve, dipende dalla distribuzione dei dati, dagli indici, dalla frequenza di ricompilazione e un po ‘di fortuna sul fatto che una procedura trarrà vantaggio dall’uso di variabili locali. Certamente non sempre aiuta.


Spero di aver fatto luce sull’effetto dell’uso dei parametri locali, dei piani di esecuzione e della compilazione delle stored procedure. Se ho fallito completamente, o ho perso un punto chiave, puoi trovare una spiegazione molto più approfondita qui:

http://www.sumrskog.se/query-plan-mysteries.html

Aiuta.

I link sottostanti contengono maggiori dettagli sullo sniffing dei parametri.

http://blogs.msdn.com/b/turgays/archive/2013/09/10/parameter-sniffing-problem-and-workarounds.aspx

http://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options

Quando si esegue un SP con i parametri per la prima volta, Query Optimizer crea il piano di query in base al valore del parametro. Query Optimizer utilizza i dati statistici per quel determinato valore per decidere il miglior piano di query. Ma i problemi di cardinalità possono influenzare questo. Ciò significa che se si esegue lo stesso SP con un valore di parametro diverso che in precedenza ha generato il piano di query potrebbe non essere il piano migliore.

Assegnando i parametri alle variabili locali nascondiamo i valori dei parametri da Query Optimizer. Quindi crea il piano di query per il caso generale.

questo è come usare l’hint “OPTIMIZE FOR UNKNOWN” nell’SP.

Non ci credo. Le moderne architetture di computer hanno un sacco di cache vicino al processore per inserire i valori delle stored procedure. In sostanza, è ansible considerarli come su uno “stack” che viene caricato nella memoria cache locale.

Se si dispone di parametri di output, quindi la copia dei valori di input su una variabile locale potrebbe eliminare un passaggio di riferimento indiretto. Tuttavia, la prima volta che viene eseguito l’indirizzamento indiretto, la memoria di destinazione verrà inserita nella cache locale e probabilmente rimarrà lì.

Quindi, no, non penso che questo sia un’ottimizzazione importante.

Tuttavia, è sempre ansible calcolare diverse varianti di una stored procedure per vedere se ciò potrebbe essere d’aiuto.