En théorie, quelle que soit la façon d'écrire une requête, le SGBDR doit être capable de trouver le moyen le plus efficace de traiter la demande, grâce à l'optimiseur. Mais parce qu'il y a loin de la théorie à la pratique, différentes écritures et différents styles de résolution, alliés à la qualité de l'indexation peuvent donner des temps d'exécution très variés.
C'est pourquoi la maîtrise de la conception de requêtes est un des points clefs de la performance d'une base de données bien conçue. Voyons quels en sont les principes basiques.
Dans un cours que je donne aux Arts & Métiers, je montre comment une bonne indexation alliée à la qualité de l'écriture des requêtes, peut faire varier dans une proportion de plus de 300 le temps d'exécution d'une requête. Je commence l'exercice par une demande simple : écrire une requête SQL permettant de répondre à une question basique, mais en prenant soin d'exprimer différentes solutions, même les plus bizarres.
Outre la solution simpliste, certains y arrivent par une union, d'autres avec des sous requêtes, d'autres encore avec des jeux de CASE... L'exécution brute de toutes ces solutions, donne un coût d'exécution allant du simple au triple. La pose d'un premier index trivial ne donne rien, par manque de sélectivité. Après élimination de cette première tentative, la pose d'un nouvel index montre que les requêtes les plus coûteuses au départ deviennent les plus rapides, alors que celles qui étaient les moins coûteuses à l'origine n'ont rien gagné. Un nouvel essai d'indexation remet toutes les requêtes au même rang qu'au départ, chacune gagnant dans la même proportion et plus que l'indexation précédente.
Enfin, la dernière tentative d'indexation étant la bonne, toutes les requêtes bénéficient d'un gain important, mais certaines bien plus que d'autres. Le clou est enfoncé lorsque l'on choisit de dénormaliser avec une vue. Là, le gain devient gigantesque. Il est voisin de 13 000. Mais il ne prend pas en compte l'effort supplémentaire à faire pour les mises à jour (INSERT, UPDATE, DELETE...).
Cet exercice nous apprend trois choses :
• différentes écritures d'une même requête ne donneront pas forcément les mêmes performances, bien que dans l’absolu, ce devrait être le cas [1]
• rien ne sert de poser un index s'il ne sert pas la requête
• une même requête écrite de différentes manières ne bénéficiera pas des mêmes gains lorsque l’on pose un index J’ajouterai que tout ceci évolue en fonction de la volumétrie des données et des données mêmes !
Tant est si bien qu’il est difficile de trouver de prime abord ce qu’est l’écriture d’une bonne requête. Affirmons cependant qu’une bonne requête est une requête qui sait tirer partie du moteur de requête pour le forcer à calculer un plan de requête dont les étapes sont les plus courtes à traiter.
[1] En fait, nous croyons souvent et naïvement que certaines écritures de requêtes sont identiques. Mais nous oublions souvent l’influence du marqueur NULL, dont le comportement particulier dans différents prédicats oblige le moteur SQL à des constructions parfois fort différentes.
Optimisation des bases de données MS SQL Server : les requêtes
Mais au fait, qu’est-ce qu’un plan de requêtes ? Quelle est son importance ? Osons le dire immédiatement. Ce qui nous intéresse n’est pas tant le plan de requêtes que ce qu’il y a derrière : la consommation des ressources. Et là, il faut bien dire que l’habillage graphique de Microsoft est plus un miroir aux alouettes qu’un outil de premier ordre…
En effet, beaucoup de développeurs sont aveuglés par les jolies icônes et ne réalisent pas que ce qui compte n’est pas tant le dessin de ces dernières mais plus le dessein qui se trame derrière ! Ainsi, la plupart des développeurs sont persuadés qu’entre deux plans de requêtes, le meilleur est celui qui a le moins d’étapes… Grave erreur !
Commençons donc par définir ce qu’est un plan de requêtes : le plan de requêtes est la décomposition en étapes élémentaires du traitement de données nécessaire à la résolution de la requête. Les plans de requêtes se présentent sous la forme d’arbres dont les feuilles sont différents points de départs indépendants et la racine le point final de collecte des lignes à afficher. En premier lieu, ce sont les feuilles de l’arbre « plan de requêtes » auxquelles il convient de porter toute son attention. Comme ces dernières sont des points de départ, et qu’un point de départ consiste à lire des données, plus on lira vite les données et mieux ce sera ! En second lieu, il faut s’intéresser aux algorithmes de jointures.
Comme il faut bien en faire, plus ces algorithmes seront efficaces en fonction des données à mettre en relation et mieux cela sera. Enfin il faut se préoccuper des autres étapes : groupements, tris et autres algorithmes sont-ils bien nécessaires ou pas ? Mais à y voir de plus près, la première mesure de l’efficacité d’un plan par rapport à un autre est en fait la consommation des données. Moins le moteur lira de pages de données pour un même traitement, plus la requête sera efficace et rapide.
En second lieu, et si deux requêtes arrivent ex æquo en consommation de pages, il faudra préciser la mesure par la consommation de CPU. Ces deux mesures sont simples et faciles à mettre en oeuvre dans l’analyseur de requêtes2. Finalement, la grande question est : comment le moteur SQL calcule-t-il le plan de requêtes ? C’est là, nous allons voir, du grand Art… Confronté à une requête qu’on lui lance, le moteur relationnel tente de savoir s’il n’a pas déjà rencontré cette forme de requête, aux paramètres près. Pour cela il transforme chaque donnée de la requête en paramètre. Puis il compare la chaîne de caractères ainsi obtenue avec les autres éléments qu’il conserve en mémoire. S’il trouve une correspondance absolue alors il peut se resservir du plan de requêtes précédemment établi.
Mais, même si le plan a déjà été établi, et a donc été repris depuis le cache des procédures, il est possible que ce dernier évolue. En effet, des changements de dernière minute dans le plan de requêtes, peuvent intervenir dans différentes conditions : valeur très déséquilibrée de paramètres, volumétrie de données ayant subi un grand changement depuis la dernière utilisation du plan, obsolescence des statistiques disponibles sur les index… Il n’y a pas longtemps, un internaute postait dans un forum SQL Server sa stupéfaction de voir sa requête passer de quelques minutes à plusieurs heures et ne trouvait aucune explication… Les statistiques de ses index ayant été calculées il y a fort longtemps, SQL Server avait considéré – sans doute à tort – qu’on ne pouvait plus s’y fier, et décidé de modifier le plan afin que les accès aux données lisent séquentiellement toutes les données…
Téléchargez cette ressource
Guide inmac wstore pour l’équipement IT de l’entreprise
Découvrez toutes nos actualités à travers des interviews, avis, conseils d'experts, témoignages clients, ainsi que les dernières tendances et solutions IT autour de nos 4 univers produits : Poste de travail, Affichage et Collaboration, Impression et Infrastructure.
