• Articolo

Utilizzo di sp_executesql

Per eseguire una stringa, è consigliabile utilizzare la stored procedure sp_executesql anziché l'istruzione EXECUTE. Poiché tale stored procedure supporta la sostituzione di parametri, sp_executesql è più versatile dell'istruzione EXECUTE. Poiché inoltre sp_executesql genera piani di esecuzione che è più probabile vengano riutilizzati in SQL Server, sp_executesql è più efficiente dell'istruzione EXECUTE.

Batch autonomi

Una stringa eseguita tramite sp_executesql o l'istruzione EXECUTE viene eseguita come batch autonomo. Tramite SQL Server, l'istruzione o le istruzioni Transact-SQL incluse nella stringa vengono compilate in un piano di esecuzione distinto da quello del batch contenente sp_executesql o l'istruzione EXECUTE. Per i batch autonomi vengono rispettate le regole seguenti:

  • Le istruzioni Transact-SQL della stringa eseguita tramite sp_executesql o EXECUTE vengono compilate in un piano di esecuzione solo quando si esegue sp_executesql o l'istruzione EXECUTE. Le stringhe vengono analizzate o verificate per rilevare eventuali errori solo quando vengono eseguite. I nomi cui viene fatto riferimento nelle stringhe vengono risolti solo quando le stringhe vengono eseguite.

  • Le istruzioni Transact-SQL della stringa eseguita non possono accedere alle variabili dichiarate nel batch contenente la stored procedure sp_executesql o l'istruzione EXECUTE. Il batch contenente sp_executesql o l'istruzione EXECUTE non può accedere alle variabili o ai cursori locali definiti nella stringa eseguita.

  • Se la stringa eseguita contiene un'istruzione USE che modifica il contesto di database, tale modifica risulta valida solo fino al completamento dell'esecuzione di sp_executesql o dell'istruzione EXECUTE.

Tali regole vengono illustrate nell'esecuzione dei due batch seguenti:

/*Show not having access to variables from the calling batch. */
DECLARE @CharVariable CHAR(3);
SET @CharVariable = 'abc';
/* sp_executesql fails because @CharVariable has gone out of scope. */
EXECUTE sp_executesql N'PRINT @CharVariable';
GO

/* Show database context resetting after sp_executesql finishes. */
USE master;
GO
EXECUTE sp_executesql N'USE AdventureWorks;'
GO
/* This statement fails because the database context
   has now returned to master. */
SELECT * FROM Sales.Store;
GO

Sostituzione dei valori dei parametri

sp_executesql, diversamente dall'istruzione EXECUTE, consente di sostituire i valori di tutti i parametri specificati nella stringa Transact-SQL. Le stringhe Transact-SQL generate da sp_executesql risultano pertanto più simili tra loro rispetto a quelle generate dall'istruzione EXECUTE. SQL Server Query Optimizer associa con maggior probabilità le istruzioni Transact-SQL di sp_executesql ai piani di esecuzione delle istruzioni eseguite in precedenza, eliminando in tal modo l'overhead associato alla compilazione di un nuovo piano di esecuzione.

Se si utilizza l'istruzione EXECUTE, è necessario convertire tutti i valori dei parametri in formato carattere o Unicode e inserirli quindi nella stringa Transact-SQL:

Se l'istruzione viene eseguita più volte, per ogni esecuzione è necessario creare una stringa Transact-SQL completamente nuova, anche se l'unica differenza consiste nei valori dei parametri. Questa operazione comporta la generazione di overhead aggiuntivo nei modi seguenti:

  • La capacità di SQL Server Query Optimizer di associare la nuova stringa Transact-SQL a un piano di esecuzione esistente viene ostacolata dalle continue variazioni dei valori dei parametri nel testo della stringa, soprattutto nel caso di istruzioni Transact-SQL molto complesse.

  • L'intera stringa deve essere ricreata per ogni esecuzione.

  • Ai valori dei parametri diversi da valori di tipo carattere o Unicode è necessario assegnare ogni volta un formato carattere o Unicode.

La stored procedure sp_executesql supporta l'impostazione dei valori dei parametri separatamente dalla stringa Transact-SQL:

DECLARE @IntVariable INT;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);

/* Build the SQL string one time. */
SET @SQLString =
     N'SELECT * FROM AdventureWorks.Sales.Store WHERE SalesPersonID = @SalesID';
/* Specify the parameter format one time. */
SET @ParmDefinition = N'@SalesID int';

/* Execute the string with the first parameter value. */
SET @IntVariable = 275;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 276;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @SalesID = @IntVariable;

sp_executesql presenta i vantaggi aggiuntivi seguenti:

  • Poiché il testo effettivo dell'istruzione Transact-SQL non cambia tra le due esecuzioni, Query Optimizer associa l'istruzione Transact-SQL della seconda esecuzione al piano di esecuzione generato per la prima istruzione. Di conseguenza, la seconda istruzione non deve essere compilata da SQL Server.

  • La stringa Transact-SQL viene creata una sola volta.

  • Il parametro integer viene specificato in formato nativo. Non è pertanto necessario eseguire la conversione in Unicode.

    [!NOTA]

    Per consentire a SQL Server di riutilizzare il piano di esecuzione, è necessario che i nomi degli oggetti inclusi nella stringa dell'istruzione siano completi.

Riutilizzo di piani di esecuzione

Nelle versioni precedenti di SQL Server l'unico modo per poter riutilizzare i piani di esecuzione consiste nel definire le istruzioni Transact-SQL come stored procedure e nell'eseguirle quindi nell'applicazione. Questo metodo, tuttavia, comporta l'esecuzione di numerose operazioni di amministrazione. L'utilizzo di sp_executesql favorisce la riduzione di questo overhead pur consentendo il riutilizzo dei piani di esecuzione in SQL Server. sp_executesql può essere utilizzata al posto delle stored procedure durante l'esecuzione ripetuta di un'istruzione Transact-SQL, nel caso in cui l'unica variazione consiste nei valori dei parametri forniti all'istruzione Transact-SQL. Poiché le istruzioni Transact-SQL restano inalterate e cambiano solo i valori dei parametri, è probabile che SQL Server Query Optimizer possa riutilizzare il piano di esecuzione creato per la prima esecuzione.

Nell'esempio seguente viene creata ed eseguita un'istruzione DBCC CHECKDB per tutti i database di un server, ad eccezione dei quattro database di sistema.

USE master;
GO
SET NOCOUNT ON;
GO
DECLARE AllDatabases CURSOR FOR
SELECT name FROM sys.databases WHERE database_id > 4
OPEN AllDatabases

DECLARE @DBNameVar NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)

FETCH NEXT FROM AllDatabases INTO @DBNameVar
WHILE (@@FETCH_STATUS = 0)
BEGIN
   PRINT N'CHECKING DATABASE ' + @DBNameVar
   SET @Statement = N'USE ' + @DBNameVar + CHAR(13)
      + N'DBCC CHECKDB (' + @DBNameVar + N')' + N'WITH PHYSICAL_ONLY'
   EXEC sp_executesql @Statement
   PRINT CHAR(13) + CHAR(13)
   FETCH NEXT FROM AllDatabases INTO @DBNameVar
END

CLOSE AllDatabases
DEALLOCATE AllDatabases
GO
SET NOCOUNT OFF;
GO

Il driver ODBC SQL Server utilizza sp_executesql per implementare SQLExecDirect quando l'istruzione Transact-SQL eseguita contiene indicatori di parametri associati. In questo modo è possibile estendere i vantaggi offerti da sp_executesql a tutte le applicazioni che utilizzano ODBC o API definite su ODBC, ad esempio RDO. Le applicazioni ODBC esistenti e trasferite in SQL Server acquisiscono automaticamente tutti i vantaggi in termini di prestazioni senza alcuna modifica a livello di programmazione. L'unica eccezione è che la stored procedure sp_executesql non viene utilizzata con parametri data-at-execution. Per ulteriori informazioni, vedere Utilizzo dei parametri delle istruzioni.

Il provider ODBC Native Client di SQL Server utilizza inoltre sp_executesql per implementare l'esecuzione diretta delle istruzioni con parametri associati. Le applicazioni che utilizzano OLE DB o ADO acquisiscono automaticamente i vantaggi di sp_executesql senza alcuna modifica a livello di programmazione.