SQL Server ed ALM su database


Il blog di Alessandro Alpi
Archivio Posts
Anno 2018

Anno 2017

Anno 2016

Anno 2015

Anno 2014

Anno 2013

Anno 2012

Anno 2011

Anno 2010

Anno 2009

Anno 2008

Anno 2007

Anno 2006

SQL Server programming - Variabili/costanti e Statistiche

Questo post vuole essere un reminder di un comportamento che il Query Processor di Sql Server ha regolarmente quando si parla di variabili locali e costanti (literal).
Come sappiamo, SQL Server colleziona un elenco di statistiche che poi serviranno al motore per stabilire quale piano di esecuzione (il migliore secondo la sua analisi interna) applicare nell'esecuzione di ogni query.
Queste statistiche inoltre vengono aggiornate di default (a meno che non si cambi il comportamento standard) qualora SQL Server lo ritenga opportuno.
Grazie a tale procedimento, ogni esecuzione di lettura o scrittura sui nostri database è ottimizzata in base ai contatori salvati.

Può succedere comunque che il motore non mantenga perfettamente allineate le statistiche, ragion per cui, una delle best practices consigliate è quella di effettuare UPDATE STATISTICS a periodicità che le nostre applicazioni richiedono (basandosi ad esempio sulle quantità di update che l'applicazione stessa effettua, se è alto, un update statistics fullscan è consigliato, magari anche ogni notte).

C'è una particolare pratica, da seguire il meno possibile, che è quella di utilizzare variabili locali nelle condizioni di WHERE, con scopo di ridurre l'utilizzo dei literal. Ad esempio:

1 - Utilizzo delle variabili locali (direttamente da documentazione - Northwind)

declare @StartOrderDate datetime

set @StartOrderDate = '20040731'

select * from Sales.SalesOrderHeader h,Sales.SalesOrderDetail d 

WHERE h.SalesOrderID = d.SalesOrderId AND h.OrderDate >= @StartOrderDate


1 - Utilizzo delle costanti (direttamente da documentazione - Northwind)

SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d

WHERE h.SalesOrderID = d.SalesOrderId AND h.OrderDate >= '20040731'


Ognuno dei precedenti approcci avrà un piano di esecuzione differente. Nel primo dei casi il numero delle righe della SalesOrderHeader sarà il 30% dell'intera tabella, mentre nel secondo una percentuale intorno all'1% che è un valore vicino all'effettivo numero di righe che soddisfano la condizione. Come mai due query equivalenti hanno però due analisi differenti da parte del motore? La differenza sta proprio nell'interpretazione del literal rispetto ad una variabile (non un parametro, una variabile locale). La documentazione indica:

"Even when local variables are used in a query, an estimate that is better than a guess is used in the case of equality predicates. Selectivity for conditions of the form "@local_variable = column_name" is estimated using the average value frequency from the histogram for column_name. So, for example, if the column column_name contains all unique values, then a selectivity estimate of 1/(number of unique values in column) will be used, which is accurate."

Quindi, utilizzando local variables è possibile che la stima della query non sia quella sperata e quindi il piano stesso non dia le prestazioni desiderate.
Se però si vuole reinterpretare ad ogni esecuzione il valore della variabile per calcolare il piano effettivo è sempre possibile utilizzare l'opzione RECOMPILE:

SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d

WHERE h.SalesOrderID = d.SalesOrderId AND h.OrderDate >= @StartOrderDate

OPTION(RECOMPILE)

Questa opzione forza l'ottimizzazione della query ad ogni esecuzione, perdendo quindi tempo nella ricreazione dei piani di esecuzione, ma dando l'effettivo costo della query stessa.

Stiamo parlando di query, ma lo stesso si presenta anche a livello di stored procedure.
E' quindi buona norma evitare l'utilizzo di local variables anche in questo contesto, per le medesime ragioni, considerando poi che con le stored procedure i piani sono salvati e persistiti in cache by design.
L'opzione di recompile c'è anche nello statement di CREATE PROCEDURE o ALTER PROCEDURE.

Stay Tuned! 


Categoria: Transact-SQL
sabato, 30 apr 2011 Ore. 16.31
Statistiche
  • Views Home Page: 485.424
  • Views Posts: 874.945
  • Views Gallerie: 500.843
  • n° Posts: 484
  • n° Commenti: 273



















Copyright © 2002-2007 - Blogs 2.0
dotNetHell.it | Home Page Blogs
ASP.NET 2.0 Windows 2003