novembre
2010
Un article de zinzineti
Le premier contrôle à effectuer en cas de lenteur d’une requête est l’analyse des indexes. Cette analyse oblige à se poser les questions suivantes :
• 1. est ce que les bons indexes sont créés ?
• 2. si oui est ce que la requête profite bien des l’index crées ?
La réponse à ces questions nécessite une bonne connaissance de comment fonctionne l’optimiseur du SGBD. Créer les bons indexes ne veut pas dire créer des indexes sur toutes les colonnes membres de la clause WHERE… Non, Non et Non ça ne marche pas comme ça. La réponse à la question 2. nécessite une bonne connaissance de ce que les Anglo-saxons appelent Search ARGument(SARG) ou argument de recherche. Dans ce billet nous allons examiner les précautions à prendre pour rendre la clause WHERE SARGable, c’est à dire optimisée.
De façon générale, une clause WHERE SARGable est sous la forme :
• Colonne opérateur <constante ou variable>
• <constante ou variable> opérateur Colonne
avec comme opérateur l’une des valeurs suivantes :
=, >, <, >=, <=, IN, BETWEEN et parfois LIKE (dans les cas des concordances des préfixes, tels que LIKE 'etie%')
Est donc Non-SARG un WHERE contenant comme opérateur l’une des valeurs suivantes :
NOT, <>, NOT EXISTS, NOT IN, NOT LIKE (LIKE '%nne' ou LIKE '%tie%')
Remarque :
===========
/!\ Le comportement de l’opérateur LIKE varie en fonction de la version SQL SERVER et de l’édition
Pour la démo nous allons utiliser le jeu de données suivant :
–Création de la table de test T_TEST
–============================================================
SET NOCOUNT ON
IF OBJECT_ID('dbo.T_TEST') IS NOT NULL
DROP TABLE dbo.T_TEST
CREATE TABLE dbo.T_TEST
(
id int identity(1,1),
val varchar(10),
creation_date datetime
)
–Chargement d’un million de lignes dans la table : Cette opération a duré environ 9 minutes sur ma machine (SQL2K8 Edition Entreprise (version Evaluation) – OS: WXP SP3 – 2CPU T5600 @1.83GHZ – Mémoire = 2Go )
–============================================================
SET @counter = 1;
WHILE @counter <= 1000000
BEGIN
INSERT INTO T_TEST(val,creation_date) VALUES(convert(varchar(10),(left(convert(bigint,RAND()*10000000),6))),getdate());
SET @counter = @counter + 1
END;
– Ce qu’il faut savoir concernant le plan d’exécution des requêtes
–============================================================
• « Table Scan » indique la lecture complète de la table donc pas d’index utilisé• « Index Scan » indique que la table est analysée à travers l’index, cela ne veut pas dire que l’index est utilisé pour accéder directement aux lignes individuelles. Autrement dit la requête ne profite pas de l’indexe, c’est un opérateur similaire à « table scan ».
• « Index Seek » indique que l’optimiseur utilise l’index à bon escient, utilisation correcte de l’index par le moteur
–> Conseil N° 1 : Éviter l’usage des opérateurs Non-SARG dans la clause WHERE
–============================================================
–> Démo : Conseil N° 1
— ===============================================================================
CREATE CLUSTERED INDEX IX_VAL ON dbo.T_TEST(val)
--Soit la requête ci-dessous avec l'opérateur NOT IN
SELECT val
FROM T_TEST
WHERE val NOT IN ('456','789','980')
–Résultat : le plan d’exécution montre un Index Scan => la requête ne profite pas de l’indexe
 |
–> Conseil N° 2 : Éviter l’usage des opérations arithmétiques sur les colonnes dans la clause WHERE
–============================================================
–> Démo : Conseil N° 2
— ===============================================================================
DROP INDEX IX_VAL ON dbo.T_TEST
--Créons un index cluster sur la colonne ID
CREATE CLUSTERED INDEX IX_ID ON dbo.T_TEST(id)
--Soit la requête ci-dessous avec une opération de multiplication sur la colonne id
SELECT id
FROM T_TEST
WHERE id * 100 = 10000
–Résultat : le plan d’exécution montre un Index Scan => la requête ne profite pas de l’indexe
 |
–> Conseil N° 3 : Éviter l’usage des fonctions sur les colonnes dans la clause WHERE
–============================================================
–> Démo : Conseil N° 3
— ===============================================================================
--Soit la requête ci-dessous avec une fonction convert appliquée sur la colonne id
<code>SELECT id
FROM T_TEST
WHERE convert(varchar(10),id) = '123456'
–Résultat : le plan d’exécution montre un Index Scan => la requête ne profite pas de l’indexe
 |
Alors que la même requête sans la fonction convert sur la colonne id utilise bien l’indexe créé => présence de l’Index Seek
 |
Autre exemple avec les fonctions horodatages
— Supprimons l’index placé sur la colonne ID de la table T_TEST
DROP INDEX IX_ID ON dbo.T_TEST
–Créons un index clustered sur la colonne « creation_date »
CREATE CLUSTERED INDEX IX_creationdate ON dbo.T_TEST(creation_date)
–Vérifions le bon usage de l’indexe crée
FROM T_TEST
WHERE creation_date = getdate()
–Résultat : Présence d’Index Seek => Bonne utilisation de l’index crée
 |
Appliquons maintenant des fonctions sur la colonne date
FROM T_TEST
WHERE year(creation_date) = 2010
–Résultat : le plan d’exécution montre un Index Scan => la requête ne profite pas de l’indexe
 |
On peut bien sûr rendre SARGable la requête précédente en l’écrivant autrement.
FROM T_TEST
WHERE creation_date >= '2010-01-01' AND creation_date <='2010-12-12'
–Résultat : Présence d’Index Seek => Bonne utilisation de l’index crée
 |
–> Conseil N° 4 : Éviter d’avoir WHERE Table1.colonne1 opérateur Table1.colonne2 (colonne1 et colonne2 appartiennent à la même table)
–============================================================
–> Démo : Conseil N° 4
— ===============================================================================
--Soit la requête ci-dessous
SELECT id
FROM T_TEST
WHERE id = val
–Résultat : le plan d’exécution montre un Index Scan => la requête ne profite pas de l’indexe
–Remarque : dans notre cas la colonnes id (int) et val (varchar) ne sont pas typées pareil. Et pourtant la requête s’exécute sans erreur. Ceci parce que l’optimiseur a fait une conversion implicite. Il faut a tout prix éviter ce genre de chose c’est à dire respecter les types de données car ceci a d’impact sur la performance de la requête.
Pour notre Démo, si les colonnes id et val sont du même type on a également un Index Scan.
 |
–> Conseil N° 5 : Éviter d’avoir dans la clause WHERE Non-SARG OR SARG
–============================================================
–> Démo : Conseil N° 5
— ===============================================================================
--Soit la requête ci-dessous
SELECT id
FROM T_TEST
WHERE id = 123456 OR convert(varchar,id) ='456'
–Résultat : le plan d’exécution montre un Index Scan => la requête ne profite pas de l’indexe
 |
Par contre un WHERE Non-SARG AND SARG peut être SARGable
Voici un exemple
FROM T_TEST
WHERE id = 123456
AND SUBSTRING(convert(varchar,id),1,3) ='123'
 |
–> Conseil N° 6 : N’oublier pas les exceptions suivantes :
–============================================================
Fonction ISNULL
===============================
— Supprimons tous les index précédents [pour être sûr qu’il y a rien qui traine ;-)]
DROP INDEX IX_ID ON dbo.T_TEST
DROP INDEX IX_creationdate ON dbo.T_TEST
–Créons un index sur la colonne id
CREATE CLUSTERED INDEX IX_ID ON dbo.T_TEST(id)
–Exécutons la requête
FROM T_TEST
WHERE ISNULL(id,123456) = 123456
–Résultat : Présence d’Index Seek => Bonne utilisation de l’index crée malgré l’application de la fonction ISNULL à la colonne id !
 |
Fonction IS NULL
===============================
–Supprimons l’index IX_ID
DROP INDEX IX_ID ON dbo.T_TEST
–Créons l’index sur la colonne val
CREATE CLUSTERED INDEX IX_VAL ON dbo.T_TEST(val)
— Exécutons la requête
FROM T_TEST
WHERE val IS NULL
–Résultat : Présence d’Index Seek => Bonne utilisation de l’index crée !
 |
Fonction IS NOT NULL
===============================
Comme précédemment on a il y a utilisation de l’index pour la requête
FROM T_TEST
WHERE val IS NOT NULL
 |
–> L’opérateur « IN » est-il SARGable ?
–============================================================
–Supprimons tous les indexes créés précédement
DROP INDEX IX_ID ON dbo.T_TEST
DROP INDEX IX_creationdate ON dbo.T_TEST
— Créons à nouveau l’indexe sur la colonne val
CREATE CLUSTERED INDEX IX_VAL ON dbo.T_TEST(val)
— Testons la requête suivante :
FROM T_TEST
WHERE val IN ('123','456','789')
–Résultat : Présence d’Index Seek => Bonne utilisation de l’index crée !
 |
On voit donc dans cet exemple que l’opérateur IN est SARGable dans les conditions de notre test (SQL 2008 version Entreprise)
–> L’opérateur « OR » est-il SARGable ?
–============================================================
–Gardons toujours l’index IX_VAL précédemment crée puis examinons la requête suivante
FROM T_TEST
WHERE val = '123' OR val = '456' OR val ='789'
–Résultat : Présence d’Index Seek => Bonne utilisation de l’index crée !
 |
On voit donc dans cet exemple que l’opérateur OR est SARGable dans les conditions de notre test (SQL 2008 version Entreprise)
===========================================================================================================================
Il y a encore beaucoup de choses à dire sur l’optimisation des requêtes, là c’est juste une petite analyse sous le prisme de la clause WHERE. On peut également analyser les clauses JOIN ,HAVING , condition de trie ORDER BY et les fonctions d’aggrégations.
===========================================================================================================================
Vos commentaires sont les bienvenus 