Une problématique récurrente au niveau SQL Server consiste à réaliser des requêtes de recherche multicritères avec des critères facultatifs. Le but est bien évidemment que chacune des combinaisons fournisse le résultat demandé avec des performances optimales.

Pour détailler le raisonnement, nous allons nous appuyer sur le base AdventureWorksDW2008R2, que l’on peut trouver gratuitement ici.

Prenons par exemple la table suivante :

Considérons par exemple que nous avons un écran de recherche qui permet de filtrer les enregistrement de cette table suivant les critères suivants : CurrencyKey, DueDateKey, EmployeeKey, OrderDateKey, ProductKey, PromotionKey, ResellerKey et ShipDateKey.

Cherchons à construire une procédure stockée qui prendrait en paramètres d’entrée 8 valeurs correspondant à ces critères, et qui retournerait l’ensemble des enregistrements correspondant. Ajoutons comme difficulté supplémentaire le fait que chaque critère de sélection est facultatif, c’est-à-dire que le filtre associé ne doit pas être pris en considération si la valeur fournie à la procédure stockée vaut Null.

On obtient une procédure ayant l’allure suivante :

create procedure Recherche1 
	@CurrencyKey int=Null,
	@DueDateKey int=Null,
	@EmployeeKey int=Null,
	@OrderDateKey int=Null,
	@ProductKey int=Null,
	@PromotionKey int=Null,
	@ResellerKey int=Null,
	@ShipDateKey int=Null
AS
BEGIN
	SELECT * FROM dbo.FactResellerSales
	WHERE (@CurrencyKey is null or CurrencyKey=@CurrencyKey)
		AND (@DueDateKey is null or DueDateKey=@DueDateKey)
		AND (@EmployeeKey is null or EmployeeKey=@EmployeeKey)
		AND (@OrderDateKey is null or OrderDateKey=@OrderDateKey)
		AND (@ProductKey is null or ProductKey=@ProductKey)
		AND (@PromotionKey is null or PromotionKey=@PromotionKey)
		AND (@ResellerKey is null or ResellerKey=@ResellerKey)
		AND (@ShipDateKey is null or ShipDateKey=@ShipDateKey)
END

Cherchons maintenant à tester un peu notre procédure stockée, et surtout à observer ses performances.

Premier test, aucun critère fourni :

exec Recherche1 -- Liste tous les enregistrements

Jusqu’ici, tout va bien, il est normal que l’on utilise un scan de l’index clsutered, puisque l’on cherche à récupérer toutes les colonnes, pour tous les enregistrements de la table.

Maintenant, choisissons un filtre sur la date de commande.

exec Recherche1 @OrderDateKey=20080331 -- Filtre sur 4 enregistrements

On s’aperçoit que le plan d’exécution n’a pas changé, et que le moteur passe toujours par un scan complet de l’index clustered, c’est-à-dire par un parcours complet de la table.

En fait, aucune directive particulière n’ayant été donnée, le moteur conserve le plan d’exécution qu’il avait déterminé lors de la première requête, et l’applique de nouveau. Pour s’en convaincre, choisissons de repasser ces deux requêtes, mais dans un sens puis dans l’autre, et en ayant pris soin de vider le cache des plans d’exécution entre temps…

dbcc freeproccache
exec Recherche1 -- Liste tous les enregistrements
exec Recherche1 @OrderDateKey=20080331 -- Filtre sur 4 enregistrements

dbcc freeproccache
exec Recherche1 @OrderDateKey=20080331 -- Filtre sur 4 enregistrements
exec Recherche1 -- Liste tous les enregistrements

Au delà du fait que le plan d’exécution est conservé, on remarque ici surtout que le système se fourvoie complètement quand au coût relatif des requêtes. Ainsi, pour les exécutions passant par l’index non clustered (il s’agit de l’index IX_FactResellerSales_OrderDateKey), les coûts prévus sont identiques pour les deux requêtes, alors qu’en réalité les durées ne sont pas du tout les mêmes…

set statistics io on
set statistics time on
dbcc freeproccache
exec Recherche1 @OrderDateKey=20080331 -- Filtre sur 4 enregistrements
exec Recherche1 -- Liste tous les enregistrements

Plusieurs solutions sont possibles pour corriger ce problème de performances.

Tout d’abord, il est possible d’ajouter un paramètre lors de la définition de la requêtes pour indiquer au moteur de re-déterminer systématiquement le plan d’exécution correspondant le mieux aux paramètres d’entrée fournis.

create procedure Recherche2
	@CurrencyKey int=Null,
	@DueDateKey int=Null,
	@EmployeeKey int=Null,
	@OrderDateKey int=Null,
	@ProductKey int=Null,
	@PromotionKey int=Null,
	@ResellerKey int=Null,
	@ShipDateKey int=Null
WITH RECOMPILE -- Déterminer un nouveau plan d'exécution à chaque fois.
AS
BEGIN
	SELECT * FROM dbo.FactResellerSales
	WHERE (@CurrencyKey is null or CurrencyKey=@CurrencyKey)
		AND (@DueDateKey is null or DueDateKey=@DueDateKey)
		AND (@EmployeeKey is null or EmployeeKey=@EmployeeKey)
		AND (@OrderDateKey is null or OrderDateKey=@OrderDateKey)
		AND (@ProductKey is null or ProductKey=@ProductKey)
		AND (@PromotionKey is null or PromotionKey=@PromotionKey)
		AND (@ResellerKey is null or ResellerKey=@ResellerKey)
		AND (@ShipDateKey is null or ShipDateKey=@ShipDateKey)
END

On constate bien ici que les plans d’exécution sont adaptés.

exec Recherche2 -- Liste tous les enregistrements
exec Recherche2 @OrderDateKey=20080331 -- Filtre sur 4 enregistrements

Le problème de cette solution est le surcoût du calcul systématique du plan d’exécution, notamment lorsque les mêmes combinaisons de paramètres d’entrée reviennent souvent.

Nous nous attacherons ici à détailler une solution qui permet d’adapter la requête exécutée pour chaque combinaison de paramètres d’entrée. Dans notre cas, elle joue presque à jeu égal avec la recompilation systématique. Néanmoins, dans la pratique, elle la devance largement lorsque la requête contient des jointures qui ne sont à réaliser que pour certains paramètres ou certaines combinaisons de paramètres. Voici donc la notion de requête dynamique.

Nous allons donc définir une requête en fonction des critères d’entrée de la procédure, et nous n’aurons alors plus qu’à exécuter cette requête minimaliste. Cette requête sera construite étape par étape, en y ajoutant les conditions seulement si nécessaire.

create procedure Recherche3
	@CurrencyKey int=Null,
	@DueDateKey int=Null,
	@EmployeeKey int=Null,
	@OrderDateKey int=Null,
	@ProductKey int=Null,
	@PromotionKey int=Null,
	@ResellerKey int=Null,
	@ShipDateKey int=Null
WITH RECOMPILE -- Déterminer un nouveau plan d'exécution à chaque fois.
AS
BEGIN
	declare @Requete nvarchar(max)

	select @Requete=N'SELECT * FROM dbo.FactResellerSales
	WHERE 1=1'

	if (@CurrencyKey is not null)
		select @Requete=@Requete+ N' AND CurrencyKey=@CurrencyKey'
	if (@DueDateKey is not null)
		select @Requete=@Requete+ N' AND DueDateKey=@DueDateKey'
	if (@EmployeeKey is not null)
		select @Requete=@Requete+ N' AND EmployeeKey=@EmployeeKey'
	if (@OrderDateKey is not null)
		select @Requete=@Requete+ N' AND OrderDateKey=@OrderDateKey'
	if (@ProductKey is not null)
		select @Requete=@Requete+ N' AND ProductKey=@ProductKey'
	if (@PromotionKey is not null)
		select @Requete=@Requete+ N' AND PromotionKey=@PromotionKey'
	if (@ResellerKey is not null)
		select @Requete=@Requete+ N' AND ResellerKey=@ResellerKey'
	if (@ShipDateKey is not null)
		select @Requete=@Requete+ N' AND ShipDateKey=@ShipDateKey'

	exec sp_executesql @Requete,
					N'@CurrencyKey int,
						@DueDateKey int,
						@EmployeeKey int,
						@OrderDateKey int,
						@ProductKey int,
						@PromotionKey int,
						@ResellerKey int,
						@ShipDateKey int',
					@CurrencyKey=@CurrencyKey,
						@DueDateKey=@DueDateKey,
						@EmployeeKey=@EmployeeKey,
						@OrderDateKey=@OrderDateKey,
						@ProductKey=@ProductKey,
						@PromotionKey=@PromotionKey,
						@ResellerKey=@ResellerKey,
						@ShipDateKey=@ShipDateKey

END

Et cette fois-ci, on consta bien sur les plan d’exécution que les requêtes sont adaptées aux critères de recherche. (au passage, le 1=1 permet à moindre coût de ne pas s’embêter avec des tests supplémentaires pour savoir quel paramètre sera le premier à être utilisé…)

exec Recherche3 -- Liste tous les enregistrements
exec Recherche3 @OrderDateKey=20080331 -- Filtre sur 4 enregistrements

Le fait de construire de la sorte la requête est nommé « SQL dynamique ». Dans notre exemple simple, la requête n’implique pas de jointures, mais si cela avait été le cas, on aurait de la même manière pu choisir de positionner ou pas ces jointures suivant leur nécessité ou pas en fonction des paramètres fournis, et cette possibilité n’est pas offerte avec la solution de recompilation systématique évoquée plus haut qui, elle, conserve dans tous les cas la même requête…

Nous verrons dans un prochain article comment optimiser sous SQL Server 2012 les performances de requêtes de ce style lorsque plusieurs paramètres sont fournis à la procédure stockée…