On lit souvent qu’un index a des effets néfastes sur les performances des requêtes en Update. J’ai réalisé un micro-benchmark sur une de nos tables internes afin de déterminer cet impact de manière un peu plus précise.
La table utilisée est une table de production de 8 555 648 lignes, pesant 550 Mo et ayant 8 colonnes. Une de ces colonnes est un champ Email unique et une autre est la date d’intégration dans la base au format datetime.
La requête que j’utilise est :
Update R_Temp.dbo.Email
set Date_In='04/09/2008'
where Email in ('test@gmail.com')
and Date_In is null
Voici le plan d’exécution estimé sur la table sans index :
Comme il n’y a pas d’index, SQLServer doit procéder à un Table Scan, c’est-à-dire parcourir entièrement la table afin de trouver les lignes correspondant à la requête. Voici les coûts de ce Scan :
Estimated I/O Cost : 52,1454
Estimated CPU Cost : 4,70565
Estimated Operator Cost : 56,8511
Estimated Subtree Cost : 56,8511
J’ai ensuite créé un Index non ordonné (nonclustered) sur le champ Email, cet index occupant 307 Mo sur le disque.
On peut immédiatement constater que l’utilisation d’un index permet d’améliorer considérablement les performances, le coût de l’Update passant de 60,6541 à 0,0165721. Un autre point important, caractéristique d’un index non ordonné, est la partie RID Lookup s’ajoutant à l’Index Seek. En effet, une fois l’index parcouru, SQLServer doit ensuite suivre le pointeur vers la table et lire la valeur correspondante. Pour reprendre l’image classique du livre et de sa table des matières, cela correspond à d’abord trouver le numéro de page du chapitre nous intéressant puis à se rendre à cette page et enfin lire le texte.
Voici le coût de l’Index Seek (le RID Lookup a le même coût) :
Estimated I/O Cost : 0,003125
Estimated CPU Cost : 0,0001581
Estimated Operator Cost : 0,0032831
Estimated Subtree Cost : 0,0032831
L’utilisation de cet index permet donc de réduire les coûts CPU, les accès disques et de manière plus globale le temps d’exécution de la requête.
Après avoir supprimé cet index, j’ai créé un index ordonné (clustered) sur le champ Email et occupant 2,6 Mo d’espace disque. L’image classique de l’index ordonné est l’annuaire : les noms des personnes sont dans l’ordre alphabétique et le numéro de téléphone est indiqué à côté. Plus techniquement, les feuilles du B-arbre d’un index ordonné contiennent directement les données et non pas un pointeur vers les données situées dans la table.
On constate que le coût estimé est légèrement inférieur à celui d’un index non ordonné mais le coût de l’Index Seek est identique et dans la pratique la requête s’exécute aussi rapidement avec les deux types d’index. On remarque également une étape Clustered Index Update à la place du Table Update dans le cas d’un index non ordonné : on l’a vu, un index non ordonné ne fait que pointer vers la valeur située dans la table et un Update ne l’influence donc pas au contraire d’un index ordonné.
A l’utilisation, un index accélère énormément des Updates de ce type, diminuant la durée de la requête de 2 s à 15 ms sur mon serveur. Il faut cependant bien garder à l’esprit que les performances peuvent rapidement se dégrader lorsque la fragmentation des index, le nombre d’Updates/Inserts et le nombre d’index sur les champs mis à jour augmente. Comme toujours, une planification et une phase de test exhaustive est indispensable avant la mise en production de nouveaux index.
