274
Chapitre 8. Optimisation du code SQL
exécutée ne fait plus partie de la procédure stockée. Son évaluation et son exécution
dynamique sont prises en charge par le moteur relationnel comme les autres requêtes
ad hoc.
Sécurité
Une autre contrainte vient de la sécurité : le SQL dynamique n’obéit plus aux règles de chaînage de propriétaires. En d’autres termes, les privilèges doivent être vérifiés sur les objets
contenus dans le SQL dynamique, et l’utilisateur exécutant la procédure doit donc être autorisé sur ces objets. Ceci pour éviter les risques d’injection SQL, c’est-à-dire d’envoi de code
malicieux. Vous pouvez créer votre procédure avec la clause EXECUTE AS pour l’exécuter dans
un contexte d’utilisateur ayant des privilèges sur ces objets. Soyez néanmoins très prudents
sur les risques d’injections (voyez par exemple cet article, pour vous prévenir contre elles :
http://fr.wikipedia.org/wiki/Injection_SQL).
À quoi bon faire une procédure stockée si c’est pour la faire agir comme un code
client ? Mais dans le cas de figure du formulaire de recherche, cet inconvénient se
révèle plutôt un avantage. La non-réutilisation du plan d’exécution permet d’éviter
l’écueil dans lequel nous tombons dans le cas de l’exemple de requête statique utilisant le bricolage (LastName = @LastName OR @LastName IS NULL). Nous avons créé
deux procédures stockées dont vous trouverez le source sur le site d’accompagnement du livre. Elles sont nommées Person.SearchContactsStatique et Person.SearchContactsDynamique, et elles implémentent la même requête selon les deux
solutions proposées. Essayons deux appels :
EXEC Person.SearchContactsStatique @LastName = 'Adams'
GO
EXEC Person.SearchContactsDynamique @LastName = 'Adams'
GO
EXEC Person.SearchContactsStatique
@LastName = 'Adams', @MiddleName = 'S'
GO
EXEC Person.SearchContactsDynamique
@LastName = 'Adams', @MiddleName = 'S'
GO
Sur la figure 8.15, nous voyons la différence de performance entre les deux solutions, lors du deuxième appel. Elle est très parlante. La solution dynamique gagne
haut la main. Nous voyons aussi un CacheHit : le code ad hoc de la requête dynamique s’est tout de même inséré dans le cache de plans.
Sur les figures 8.16 et 8.17, nous voyons respectivement le plan de la première
procédure – qui n’a pas changé, et le plan de la seconde – qui s’est adapté.
Chapitre 8. Optimisation du code SQL
exécutée ne fait plus partie de la procédure stockée. Son évaluation et son exécution
dynamique sont prises en charge par le moteur relationnel comme les autres requêtes
ad hoc.
Sécurité
Une autre contrainte vient de la sécurité : le SQL dynamique n’obéit plus aux règles de chaînage de propriétaires. En d’autres termes, les privilèges doivent être vérifiés sur les objets
contenus dans le SQL dynamique, et l’utilisateur exécutant la procédure doit donc être autorisé sur ces objets. Ceci pour éviter les risques d’injection SQL, c’est-à-dire d’envoi de code
malicieux. Vous pouvez créer votre procédure avec la clause EXECUTE AS pour l’exécuter dans
un contexte d’utilisateur ayant des privilèges sur ces objets. Soyez néanmoins très prudents
sur les risques d’injections (voyez par exemple cet article, pour vous prévenir contre elles :
http://fr.wikipedia.org/wiki/Injection_SQL).
À quoi bon faire une procédure stockée si c’est pour la faire agir comme un code
client ? Mais dans le cas de figure du formulaire de recherche, cet inconvénient se
révèle plutôt un avantage. La non-réutilisation du plan d’exécution permet d’éviter
l’écueil dans lequel nous tombons dans le cas de l’exemple de requête statique utilisant le bricolage (LastName = @LastName OR @LastName IS NULL). Nous avons créé
deux procédures stockées dont vous trouverez le source sur le site d’accompagnement du livre. Elles sont nommées Person.SearchContactsStatique et Person.SearchContactsDynamique, et elles implémentent la même requête selon les deux
solutions proposées. Essayons deux appels :
EXEC Person.SearchContactsStatique @LastName = 'Adams'
GO
EXEC Person.SearchContactsDynamique @LastName = 'Adams'
GO
EXEC Person.SearchContactsStatique
@LastName = 'Adams', @MiddleName = 'S'
GO
EXEC Person.SearchContactsDynamique
@LastName = 'Adams', @MiddleName = 'S'
GO
Sur la figure 8.15, nous voyons la différence de performance entre les deux solutions, lors du deuxième appel. Elle est très parlante. La solution dynamique gagne
haut la main. Nous voyons aussi un CacheHit : le code ad hoc de la requête dynamique s’est tout de même inséré dans le cache de plans.
Sur les figures 8.16 et 8.17, nous voyons respectivement le plan de la première
procédure – qui n’a pas changé, et le plan de la seconde – qui s’est adapté.