L'esecuzione di codice T-SQL dinamico è una pratica che spesso viene in aiuto al programmatore. Per poterlo fare, con SQL Server 2000, è possibile utilizzare due comandi: EXEC e sp_executesql. In questo articolo vedremo come e perché dovremmo sempre utilizzare sp_executesql in queste situazioni.
Sintassi di sp_executesql
La procedura di sistema sp_executesql esegue un'istruzione o un batch di comandi T-SQL che è possibile riutilizzare o costruire in modo dinamico. La sua sintassi è:
sp_executesql [@stmt =] codice_sql
[
[@params =] N'@parameter_name data_type [,...n]'
{, [@param1 =] 'value1' [,...n] }
]
Vediamo i parametri utilizzati dalla procedura:
- @stmt rappresenta l'istruzione (singola o in batch) di base che vogliamo eseguire e deve essere una stringa in formato Unicode (NCHAR,NVARCHAR,NTEXT) con una lunghezza massima di 4000 caratteri.
- @params rappresenta la lista dei parametri che devono essere allegati all'istruzione T-SQL specificata nel parametro @stmt. Deve essere una stringa in formato Unicode e può essere opzionale.
Se un parametro viene definito all'interno di @params allora esso deve comparire anche in @stmt, inoltre non è possibile effettuare concatenazione di stringhe per costruire l'istruzione T-SQL passata in @stmt, se fosse necessario dobbiamo prima mettere le stringhe in una variabile. Vediamo alcuni esempi per chiarirci le idee:
Use Northwind
GO
DECLARE @SQL NVARCHAR(4000)
SET @SQL = 'SELECT * FROM dbo.Employees '
+ ' WHERE EmployeeID=@EmployeeID'
EXEC sp_executesql
@stmt
= @SQL,
@params = N'@EmployeeID INT',
@EmployeeID = 1
Qui abbiamo eseguito una semplicissima query che recupera le informazioni su uno specifico impiegato.
SET @SQL = 'SELECT * FROM dbo.Employees WHERE LastName LIKE @LastName OR FirstName=@FirstName'
EXEC sp_executesql
@stmt = @SQL,
@LastName = 'C%',
@FirstName = ''
Questa query rappresenta una variante della precedente, difatti vengono passati più parametri, che permettono di impostare una ricerca in base al nome dell'impegato sulla tabella Employees.
In entrambi i casi, dopo la prima esecuzione, per tutte le sucessive l'engine di SQL Server noterà che l'unica variazione nella query è il parametro @EmployeeID (o @LastName ) e riutilizzerà il piano di esecuzione prendendolo dalla cache. Questo velocizza i tempi di risposta e di conseguenza aumenta le prestazioni dell'applicazione.
Riutilizzare gli "Execution Plans"
Aumentare le performance delle query è sicuramente l'obiettivo a cui tendiamo nella progettazione delle applicazioni che coinvolgono un software RDBMS.
Vediamo una semplice query:
SELECT * FROM dbo.Shippers WHERE ShipperID = 3
Quando inviamo a SQL Server questa istruzione (da una pagina ASP ad esempio) il motore relazionale provvederà a costruire un piano di esecuzione ad hoc per la query (sulla base di svariate informazioni e con l'ausilio di complessi algoritmi di calcolo che utilizzano numerosi cicli di CPU).
Il piano di esecuzione appena elaborato per la query verrà completamente ignorato se alla successiva esecuzione della stessa dovessimo cambiare il valore di ShipperID, ad esempio.
SELECT * FROM dbo.Shippers WHERE ShipperID = 99
Usando sp_executesql abbiamo però la possibilità di aggirare l'ostacolo e rendere riutilizzabili i piani di esecuzione di query tra loro simili, dobbiamo parametrizzare le query. Andiamo a ricodificare la query con sp_executesql
DECLARE @Var INT
DECLARE @SQL NVARCHAR(500)
DECLARE @ParmDef NVARCHAR(500)
SET @SQL =
N'SELECT * FROM Northwind.dbo.Shippers WHERE ShipperID = @ShipID'
SET @ParmDef = N'@ShipID int'
SET @Var = 3
EXECUTE sp_executesql @SQL, @ParmDef, @ShipID = @Var
In questo caso la la query si presenta divisa in due parti distinte.
Una parte statica, rappresentata dal corpo della query ed una dinamica, rappresentata dal parametro @ShipID passato con valori differenti ad ogni chiamata.
Così facendo il piano di esecuzione viene costruito considerando la sola parte statica, immutabile ad ogni chiamata, e quindi riutilizzabile ad ogni esecuzione. Tanto lavoro in meno per il processore e tanta velocità di esecuzione per le query.
Quando utilizzare sp_executesql
Ci sono diverse situazioni reali in cui fa comodo usare sp_executesql, in particolar modo quando dobbiamo:
- Gestire paginazioni lato server. Ad esempio, visualizzare in un datagrid il contenuto di una tabella di 100.000 righe!
- Creare query di ricerca complesse a criteri multipli. Ad esempio cercare determinati record su una tabella sulla base di 5-10 criteri di ricerca, il tutto ovviamente all'interno di una sola procedura memorizzata
- Inserire righe in un vista partizionata orizzontalmente.
Questi esempi rappresentano al meglio il campo di utilizzo di sp_executesql, cioè ogni qualvolta si debba coniugare da un lato le performance delle query e dall'altro la necessità di costruire istruzioni T-SQL in modo dinamico.
I vantaggi dell'utilizzo di sp_executesql rispetto a EXEC
Usare sp_executesql è sempre vantaggioso rispetto all'istruzione EXEC, per vari motivi:
- I piani di esecuzione delle query con parametri simili possono essere riutilizzati e ripresi dalla cache.
- Non è necessario formattare i parametri numerici e di tipo DATE.
- Possiamo ricevere variabili valorizzate dal contesto di esecuzione di sp_executesql.
- Viene minimizzato l'utilizzo degli apici nella costruzione della stringa T-SQL da eseguire.
Assegnare valori alle variabili durante l'esecuzione di sp_executesql
Uno dei grandi svantaggi nell'utilizzo di EXEC era sicuramente quello di non poter dialogare in alcun modo con il contesto di esecuzione della stringa T-SQL.
Tutti i risultati che possiamo ottenere tramite una EXEC('istruzione') sono espressi sempre e comunque da un "rowset" e questo implica il non poter scambiare variabili (e valori) tra il contesto chiamante e quello di esecuzione della query.
Un esempio, proviamo a conteggiare i record della tabella Orders del database NorthWind tramite EXEC:
EXEC('SELECT COUNT(*) FROM dbo.Orders')
Il risultato è 813, ovviamente non è possibile in alcun modo recuperare in modo diretto questo valore per assegnarlo eventualmente ad una variabile di output, se non attraverso un "escamotage" simile:
DECLARE @COUNT_RECORD INT
CREATE TABLE #T (C INT)
INSERT INTO #T
EXEC('SELECT COUNT(*) FROM dbo.Orders')
SELECT @COUNT_RECORD=C FROM #T
SELECT @COUNT_RECORD AS CONTEGGIO '-Sempre 813
Questo è il risultato del tracciamento delle statistiche lato server della query:
In questo caso abbiamo dovuto creare una tabella temporanea in cui salvare il risultato e successivamente assegnare il valore alla variabile, il tutto senza alcuna possibilità di riutilizzare il piano di esecuzione della query! Veramente
una soluzione scadente al nostro problema.
Fortunatamente grazie a sp_executesql possiamo trovare una soluzione elegante e efficiente, vediamola:
DECLARE @SQL NVARCHAR(4000), @COUNT_RECORD INT, @TODAY DATETIME
SET @TODAY = DATEADD(yy,-7,GETDATE())
SET @SQL = 'SELECT @COUNT=COUNT(*) FROM dbo.Orders WHERE OrderDate BETWEEN@DataDa AND @DataA'
EXEC sp_executesql
@SQL,
N'@COUNT INT OUT, @DataDa DATETIME, @DataA DATETIMÈ,
@COUNT_RECORD OUT,
@DataDa = '1996-07-04',
@DataA = @TODAY
Sul mio PC il risultato è di 430 ordini, visto che aggiunto un filtro per data, questo è il risultato del tracciamento delle statistiche lato server della query:
Consiglio vivamente, in particolar modo per le applicazioni critiche, di usare sempre e comunque la sp_executesql per eseguire codice dinamico.
Conclusione
In questo articolo abbiamo visto come sia piuttosto semplice eseguire codice T-SQL in modo dinamico, efficiente e performante, con sp_executesql.
Abbiamo inoltre esplorato funzionalità avanzate della procedura che permettono di assegnare valori di ritorno alle variabili passate come output allaprocedura.
