Réutilisation des paramètres et des plans d'exécution

L'utilisation de paramètres, notamment de marqueurs de paramètres dans les applications ADO, OLE DB et ODBC, peut favoriser la réutilisation des plans d'exécution.

Remarque relative à la sécurité
L'utilisation de paramètres ou de marqueurs de paramètres pour la conservation des valeurs entrées par les utilisateurs finaux est plus fiable que la concaténation des valeurs dans une chaîne qui sera exécutée à l'aide de la méthode API d'accès aux données, à savoir l'instruction EXECUTE, ou de la procédure stockée sp_executesql.

La seule différence entre les deux instructions SELECT suivantes porte sur les valeurs comparées dans la clause WHERE :

SELECT * 
FROM AdventureWorks2008R2.Production.Product 
WHERE ProductSubcategoryID = 1;

SELECT * 
FROM AdventureWorks2008R2.Production.Product 
WHERE ProductSubcategoryID = 4;

La seule différence entre les plans d'exécution de ces requêtes est la valeur stockée pour la comparaison avec la colonne ProductSubcategoryID. Bien que SQL Server soit conçu pour toujours reconnaître que les instructions génèrent essentiellement le même plan et réutilisent les plans, il peut arriver que SQL Server ne le détecte pas dans les instructions SQL complexes.

La séparation des constantes de l'instruction SQL à l'aide de paramètres permet au moteur relationnel de reconnaître plus facilement les plans en double. Vous pouvez utiliser les paramètres des manières suivantes :

• Dans Transact-SQL, utilisez sp_executesql :

  DECLARE @MyIntParm INT
  SET @MyIntParm = 1
  EXEC sp_executesql
    N'SELECT * 
    FROM AdventureWorks2008R2.Production.Product 
    WHERE ProductSubcategoryID = @Parm',
    N'@Parm INT',
    @MyIntParm
  

  Cette méthode est recommandée pour les scripts Transact-SQL, les procédures stockées ou les déclencheurs SQL qui génèrent dynamiquement des instructions SQL.

• ADO, OLE DB et ODBC utilisent des marqueurs de paramètres. Les marqueurs de paramètres sont des points d'interrogation (?) qui remplacent une constante dans une instruction SQL et qui sont liés à une variable de programme. Dans une application ODBC, vous pourriez par exemple procéder comme suit :

  • Utilisez SQLBindParameter pour lier une variable de type entier au premier marqueur de paramètres dans une instruction SQL.

  • Placez la valeur de type entier dans la variable.

  • Exécutez l'instruction en spécifiant le marqueur de paramètres (?) :

    SQLExecDirect(hstmt, 
      "SELECT * 
      FROM AdventureWorks2008R2.Production.Product 
      WHERE ProductSubcategoryID = ?",
      SQL_NTS);
    

    Le fournisseur OLE DB Native Client SQL Server et le pilote ODBC Native Client SQL Server fournis avec SQL Server utilisent sp_executesql pour envoyer des instructions à SQL Server lorsque les marqueurs de paramètres sont utilisés dans les applications.

• Pour concevoir des procédures stockées qui utilisent les paramètres par définition.

Si vous ne construisez pas explicitement des paramètres dans la conception de vos applications, vous pouvez toujours vous fier à l'optimiseur de requête SQL Server qui paramètre automatiquement certaines requêtes à l'aide du comportement par défaut de Paramétrage simple. Vous pouvez également forcer l'optimiseur de requête à paramétrer l'ensemble des requêtes de la base de données en attribuant la valeur FORCED à l'option PARAMETERIZATION de l'instruction ALTER DATABASE. Pour plus d'informations, consultez Paramétrage forcé.

En cas d'activation du paramétrage forcé, il est toujours possible d'utiliser le paramétrage simple. Par exemple, la requête suivante ne peut être paramétrée conformément aux règles de paramétrage forcé :

SELECT * FROM Person.Address
WHERE AddressID = 1 + 2;

Elle peut toutefois être paramétrée conformément aux règles de paramétrage simple. En cas de tentative infructueuse de paramétrage forcé, le paramétrage simple est activé.