Optimisation SQL Server : attention aux conditions WHERE avec des valeurs à NULL

Voici une recette toute simple pour accélérer l’exécution d’une requête SQL dont la clause WHERE doit pendre en compte la nullité d’un paramètre.

C’est à la suite d’une formation optimisation SQL Server, dans l’avion, que m’est venue l’idée de publier un petit post sur ce sujet.  Je me suis rendu compte que j’avais alors mis en garde les stagiaires (fort sympathiques, j’ai été très bien accueilli, merci) sur l’utilisation de clauses WHERE devant traiter, éventuellement, des paramètres à NULL. Belle mise en garde, une démo valant mieux que des mots, voici du code …

Basons nous sur la table sales.orderheader de la base de données AdvantureWorks2008.
Nous allons rechercher toutes les commandes d’un client en particulier. Pour les besoins de la démo, je ne prends qu’une seule colonne afin de supprimer le Key lookup. La colonne CustomerID est indexée (index noncluster), pas de colonne incluse.

USE AdventureWorks2008;
GO

CREATE PROCEDURE usp_GetOrdersOfCustomer
(
    @CustID INT
)
AS
    — seulement la colonne CustomerID pour éviter un keylookup dans cette démo
    SELECT CustomerID FROM Sales.SalesOrderHeader
    WHERE CustomerID = @CustID
GO

Lorsque l’on exécute la procédure, le plan d’exécution montre un Index Seek sur l’index non cluster, 2 lectures disque, la requête semble optimisée.

image

D’un point de vue fonctionnel, le besoin se fait peut être sentir d’appeler cette même procédure stockée sans paramètres, de temps en temps. Et ainsi, renvoyer l’ensemble des commandes, tous clients confondus. Réfléchissez bien ,il y a bien une requête semblable quelque part au fond d’un tiroir …

la solution classique consiste à modifier la procédure de la manière suivante :

ALTER PROCEDURE usp_GetOrdersOfCustomer
(
    @CustID INT
)
AS
    — seulement la colonne CustomerID pour éviter un keylookup dans cette démo
    SELECT CustomerID FROM Sales.SalesOrderHeader
    WHERE  @CustID IS NULL
        OR CustomerID = @CustID
GO

L’appel de la procédure avec le paramètre NULL renvoie l’ensemble des enregistrement, soit 31465 lignes.

L’appel avec le même paramètre que précédemment revoie toujours 8 lignes, mais cette fois ci, il y a eu 45 lectures et un index scan au lieu de l’index seek.

image

Le fait de vouloir tester la nullabilité du paramètre dans la requête a modifié le plan d’exécution et ralenti l’exécution de la requête, provoquant beaucoup plus d’IO disque que nécessaire.

L’option FORCEPLAN et le table Hint FORCESEEK causent une erreur au niveau de l’optimiseur de requête.

Il ne reste alors qu’a tester les paramètre, comme je le recommande ici, avant d’exécuter la procédure.

ALTER PROCEDURE usp_GetOrdersOfCustomer
(
    @CustID INT
)
AS
    — seulement la colonne CustomerID pour éviter un keylookup dans cette démo
    IF @CustID IS NULL
        SELECT CustomerID FROM Sales.SalesOrderHeader
    ELSE
        SELECT CustomerID FROM Sales.SalesOrderHeader
        WHERE  CustomerID = @CustID
GO

Le plan d’exécution prend bien en compte le test et montre bien les deux alternatives sur ‘l’exécution de la procédure :

image

Au niveau des IO disque, on est revenu sur 2 lectures avec un index seek. Certes, cela rend moins maintenable le code de la procédure stockée, mais l’exécution en est vraiment plus rapide, logiquement ….

Bonne optimisation …

A propos Christophe

Consultant SQL Server Formateur certifié Microsoft MVP SQL Server MCM SQL Server 2008
Cet article a été publié dans SQL Server. Ajoutez ce permalien à vos favoris.

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s