296
Chapitre 9. Optimisation des procédures stockées
Forçage de la recompilation
Que faire pour résoudre le problème des appels de procédures stockées avec des paramètres s’appliquant à des colonnes dont la distribution est très variable ? Une solution est de provoquer manuellement la recompilation. Cela peut être fait de
plusieurs façons. L’option WITH RECOMPILE peut être indiquée dans le corps de la
procédure stockée ou indépendamment à chaque appel :
ALTER PROCEDURE Person.GetContactByLastName
@LastNameStart nvarchar(50)
WITH RECOMPILE
AS BEGIN …
-- ou :
EXEC Person.GetContactByLastName 'Ackerman' WITH RECOMPILE
La première méthode, qui force la recompilation à chaque appel, est utile pour
une procédure où les valeurs de paramètres sont très différentes chaque fois, la
seconde pour forcer la recompilation dans des cas particuliers. Si votre procédure
n’est appelée qu’avec des paramètres atypiques, préférez la première solution. La
seconde est d’une utilité particulière : un EXEC … WITH RECOMPILE génère un plan
d’exécution qui ne sera pas mis en cache, et qui ne remplacera donc pas le plan
caché existant. Cette méthode sera donc précieuse si vous avez besoin d’appeler
ponctuellement une procédure avec un paramètre atypique.
La recompilation est une opération coûteuse, c’est pourquoi il vaut mieux limiter
l’usage du WITH RECOMPILE à des procédures de petite taille, qui en ont vraiment
besoin, c’est-à-dire où le coût de la recompilation est moindre que celui de l’exécution avec un plan inefficace.
Pour les procédures qui comportent de multiples instructions, vous pouvez sélectionner une recompilation instruction par instruction, à l’aide de l’indicateur de
requête OPTION (RECOMPILE). Par exemple :
CREATE PROCEDURE dbo.GetContactsParameter
@LastName nvarchar(50) = NULL
AS BEGIN
SET NOCOUNT ON
SELECT FirstName, LastName
FROM Person.Contact
WHERE LastName LIKE @LastName
OPTION (RECOMPILE);
END
Une meilleure solution est de forcer, toujours par indicateur de requête, sur
quelle valeur l’optimiseur doit se baser :
SELECT FirstName, LastName
FROM Person.Contact
WHERE LastName LIKE @LastName
OPTION OPTIMIZE FOR (@LastName = '%'));
Chapitre 9. Optimisation des procédures stockées
Forçage de la recompilation
Que faire pour résoudre le problème des appels de procédures stockées avec des paramètres s’appliquant à des colonnes dont la distribution est très variable ? Une solution est de provoquer manuellement la recompilation. Cela peut être fait de
plusieurs façons. L’option WITH RECOMPILE peut être indiquée dans le corps de la
procédure stockée ou indépendamment à chaque appel :
ALTER PROCEDURE Person.GetContactByLastName
@LastNameStart nvarchar(50)
WITH RECOMPILE
AS BEGIN …
-- ou :
EXEC Person.GetContactByLastName 'Ackerman' WITH RECOMPILE
La première méthode, qui force la recompilation à chaque appel, est utile pour
une procédure où les valeurs de paramètres sont très différentes chaque fois, la
seconde pour forcer la recompilation dans des cas particuliers. Si votre procédure
n’est appelée qu’avec des paramètres atypiques, préférez la première solution. La
seconde est d’une utilité particulière : un EXEC … WITH RECOMPILE génère un plan
d’exécution qui ne sera pas mis en cache, et qui ne remplacera donc pas le plan
caché existant. Cette méthode sera donc précieuse si vous avez besoin d’appeler
ponctuellement une procédure avec un paramètre atypique.
La recompilation est une opération coûteuse, c’est pourquoi il vaut mieux limiter
l’usage du WITH RECOMPILE à des procédures de petite taille, qui en ont vraiment
besoin, c’est-à-dire où le coût de la recompilation est moindre que celui de l’exécution avec un plan inefficace.
Pour les procédures qui comportent de multiples instructions, vous pouvez sélectionner une recompilation instruction par instruction, à l’aide de l’indicateur de
requête OPTION (RECOMPILE). Par exemple :
CREATE PROCEDURE dbo.GetContactsParameter
@LastName nvarchar(50) = NULL
AS BEGIN
SET NOCOUNT ON
SELECT FirstName, LastName
FROM Person.Contact
WHERE LastName LIKE @LastName
OPTION (RECOMPILE);
END
Une meilleure solution est de forcer, toujours par indicateur de requête, sur
quelle valeur l’optimiseur doit se baser :
SELECT FirstName, LastName
FROM Person.Contact
WHERE LastName LIKE @LastName
OPTION OPTIMIZE FOR (@LastName = '%'));