309
9.3 Cache des requêtes ad hoc
(option par défaut à la création d’une clé primaire). Le plan d’exécution ne changera
pas, quelle que soit la valeur passée en paramètre. SQL Server ne cache donc qu’un
plan qu’il considère « safe », c’est-à-dire qui va pouvoir répondre à pratiquement
tous les cas de valeur du paramètre. De plus, il n’essaie même pas de paramétrer des
requêtes qui comportent des constructions particulières, pour certaines très courantes, comme des jointures ou des sous-requêtes 1 . Vraiment pas de quoi concurrencer
une procédure stockée...
Notons que la réutilisation du cache de requêtes paramétrées est plus souple avec
la syntaxe, car elle ne base plus sa recherche sur le même mécanisme de comparaison
de hachage. Une requête paramétrée sera alors « rationalisée « pour correspondre à
la même requête comportant des espaces ou des retours chariot différents. Elle reste
toutefois sensible à la casse.
Pour généraliser le paramétrage, vous disposez éventuellement d’une option de
base de données qui force SQL Server à paramétrer toutes les requêtes :
ALTER DATABASE AdventureWorks SET PARAMETERIZATION FORCED
-- pour revenir à l'état par défaut :
ALTER DATABASE AdventureWorks SET PARAMETERIZATION SIMPLE
En passant en paramétrage forcé (Forced Parameterization), toutes les valeurs littérales rencontrées dans une requête sont converties en paramètres. La requête précédente sera alors stockée dans le cache avec la syntaxe suivante :
(@0 varchar(8000))select * from Person.Contact where LastName = @0
Apparemment utile, cette option n’est pas à activer à la légère, car le paramétrage implique un effort plus important de SQL Server, aussi bien à la génération du
plan d’exécution qu’à la recherche de correspondances dans le cache. Elle peut aussi
provoquer la conservation et la réutilisation de plans non optimaux, puisque les
limites du paramétrage simple servent justement à générer des plans différents lorsque c’est nécessaire. Elle n’est donc pas la panacée. Rien ne remplace la simplicité
d’une procédure stockée.
Vous noterez au passage que le paramètre généré est de type varchar(8000), alors
que la colonne recherchée est un nvarchar(50). Le type de la colonne n’est pas
vérifié par SQL Server, qui applique le type de la constante, et la taille maximum
du type de données. Cette opération est appelée bucketization. Elle est forcée par
le serveur dans ce cas, mais vous avez la possibilité de paramétrer votre requête
dans votre code client (par exemple en utilisant la collection Parameters de l’objet SQLCommand en ADO.NET avec des types de données explicites), et ainsi d’optimiser le type de données du paramètre, pour une commande que vous allez
réutiliser plusieurs fois.
1. Vous en trouvez une liste non exhaustive dans cette entrée de blog : http://blogs.msdn.com/
sqlprogrammability/archive/2007/01/11/4-0-query-parameterization.aspx
9.3 Cache des requêtes ad hoc
(option par défaut à la création d’une clé primaire). Le plan d’exécution ne changera
pas, quelle que soit la valeur passée en paramètre. SQL Server ne cache donc qu’un
plan qu’il considère « safe », c’est-à-dire qui va pouvoir répondre à pratiquement
tous les cas de valeur du paramètre. De plus, il n’essaie même pas de paramétrer des
requêtes qui comportent des constructions particulières, pour certaines très courantes, comme des jointures ou des sous-requêtes 1 . Vraiment pas de quoi concurrencer
une procédure stockée...
Notons que la réutilisation du cache de requêtes paramétrées est plus souple avec
la syntaxe, car elle ne base plus sa recherche sur le même mécanisme de comparaison
de hachage. Une requête paramétrée sera alors « rationalisée « pour correspondre à
la même requête comportant des espaces ou des retours chariot différents. Elle reste
toutefois sensible à la casse.
Pour généraliser le paramétrage, vous disposez éventuellement d’une option de
base de données qui force SQL Server à paramétrer toutes les requêtes :
ALTER DATABASE AdventureWorks SET PARAMETERIZATION FORCED
-- pour revenir à l'état par défaut :
ALTER DATABASE AdventureWorks SET PARAMETERIZATION SIMPLE
En passant en paramétrage forcé (Forced Parameterization), toutes les valeurs littérales rencontrées dans une requête sont converties en paramètres. La requête précédente sera alors stockée dans le cache avec la syntaxe suivante :
(@0 varchar(8000))select * from Person.Contact where LastName = @0
Apparemment utile, cette option n’est pas à activer à la légère, car le paramétrage implique un effort plus important de SQL Server, aussi bien à la génération du
plan d’exécution qu’à la recherche de correspondances dans le cache. Elle peut aussi
provoquer la conservation et la réutilisation de plans non optimaux, puisque les
limites du paramétrage simple servent justement à générer des plans différents lorsque c’est nécessaire. Elle n’est donc pas la panacée. Rien ne remplace la simplicité
d’une procédure stockée.
Vous noterez au passage que le paramètre généré est de type varchar(8000), alors
que la colonne recherchée est un nvarchar(50). Le type de la colonne n’est pas
vérifié par SQL Server, qui applique le type de la constante, et la taille maximum
du type de données. Cette opération est appelée bucketization. Elle est forcée par
le serveur dans ce cas, mais vous avez la possibilité de paramétrer votre requête
dans votre code client (par exemple en utilisant la collection Parameters de l’objet SQLCommand en ADO.NET avec des types de données explicites), et ainsi d’optimiser le type de données du paramètre, pour une commande que vous allez
réutiliser plusieurs fois.
1. Vous en trouvez une liste non exhaustive dans cette entrée de blog : http://blogs.msdn.com/
sqlprogrammability/archive/2007/01/11/4-0-query-parameterization.aspx