Pimp My Base !

« Grmpf, c’est long… Ça charge là ou quoi ? Ça fait bien 5 minutes que j’attends ! Pourquoi ça bloque ? C’est planté ou quoi ?? … Ça freeeeeze !! Mais qui a codé ce truc ??! Ça raaaaaame !!!! »Ça vous rappelle peut-être quelque chose ? Écrire une application qui marche, c’est bien ; mais une application qui dépote, c’est mieux !

Alors, comment faire pour améliorer les performances de nos applications ? Avec des StringBuffer, des if (null != obj), et des combats de for contre foreach???

Peut-être, mais c’est autre chose que nous allons voir ici…

Et oui, les vieilles barbes du milieu vous le diront…

« … Pour arriver à une application rapide et réactive,  il faut avant tout partir sur de bonnes bases (… de données ! :-)) »

(L’auteur de cette citation souhaitant garder l’anonymat, je ne donnerai que ses initiales, « D.B.A. »…)

Oui mais, nous on connait pas les bases de données, et on est nul en SQL ! Nous on est développeur Java ou .Net ! Alors comment on va faire pour passer de la bonne vielle requête SQL des bois taillée à la hache, à la turbo roquette SQL ?

Voici donc quelques astuces « niveau 1 », pour booster logiciels et  applications, en tunant l’accès aux données, pour nous autres développeurs « objet », néophytes en la matière… Alors prépare toi à tuner ta base !!! (Et n’oublie pas, optimiser du SQL, c’est pas de l’objet, mais c’est quand même la classe classe)

Avant propos

Le langage SQL a la particularité d’être « orienté données » et non pas « traitements » (pas complétement délirant pour un langage d’accès aux données me direz vous !) ; ce qui peut être un peu déroutant pour les développeurs applicatifs, plus habitués aux langages impératifs ou objets : une requête SQL définit un ensemble de données à obtenir, sans indiquer la manière de le construire…

C’est à la fois une bonne et une mauvaise nouvelle : d’un côté, pas besoin d’écrire d’algo de recherche, ça fait toujours ça de moins à faire…

… Mais d’un autre, si le développeur n’a pas la main sur le traitement effectué, comment pourrait-on l’optimiser ? Comment même pourrait-on savoir si une requête est performante ?

Théâtre des opérations

Avant toute chose, quelques considérations d’ordre pratique… Blablater c’est bien beau, mais plutôt que de brasser du vent, nous allons dans la suite de cet article nous appuyer sur le support suivant :

  • une application concrète : nous allons reprendre l’exemple des populations de saumons traité dans le tutoriel « So@School – Exercices SQL2 »
  • une base Oracle 10g eXpress Edition1.

Pour les absents de la semaine dernière, un bref rappel !

« Suite à une étude des experts de WWF, il s’est avéré que les populations de saumons de certaines rivières décroissaient fortement en raison du grand nombre de dents des ours habitant la région.Pour palier ce problème et surtout pour gagner les ignobels (https://www.ignobel.com), les chercheurs ont décidé de partager leurs études et leurs statistiques sur l’évolution de ces populations de saumons en utilisant une base de données. »3

Disponible gratuitement sur www.oracle.com

Disponible sur l’intranet so@ et dans toutes les bonnes poissonneries

Source : So@School – Exercices SQL.dot

Connais ton ennemi 4

Avant de partir en guerre, il nous fait définir un plan, une stratégie…

La grande question que tout le monde se pose est : comment un SGBD traduit-il une requête SQL définissant un résultat à obtenir de manière ensembliste, en un algorithme de recherche ?

Pour répondre à cette question, notre arme secrète sera, le plan d’exécution…

Le plan d’exécution d’une requête SQL décrit la façon dont le SGBD (son « optimizer » ou « planner » pour être exact) va interpréter et exécuter la requête, c’est à dire, quel algorithme de recherche va être appliqué.

Chaque SGBD (Oracle, SQLServer, MySQL, Postgresql…) a son propre optimizer, plus ou moins efficace (c’est aussi ce qui fait la puissance d’un SGBD face à un autre !).

De manière générale, on ne peut pas, mais surtout il ne faut pas, obliger un optimizer à appliquer un  algorithme en particulier : trouver le meilleur plan, c’est son boulot5 !

Sans rentrer en profondeur dans les détails, l’optimizer combine pour chaque requête les algorithmes  les plus efficaces, en fonction des  relations entre les tables, des types des données, des fonctions appelées, des volumes des tables, et de divers éléments statistiques allant de la répartition des données, à l’âge du capitaine..

Même si le choix du plan d’exécution est du ressort de l’optimizer, on peut néanmoins l’interroger, sur le plan calculé ! La syntaxe varie d’un SGBD à l’autre ; nous prendrons pour l’exemple la syntaxe d’Oracle :


explain plan for<query>


Cette instruction calcule le plan d’exécution de la requête <query> et l’insère dans la table plan_table. Cette fonctionnalité est souvent intégrée dans les IDE SQL. Elle est par exemple accessible sous TOAD par l’icône de l’ambulance : … Ou encore sous SQL Developer, avec l’icône qui ressemble à… pas grand chose : Cette fonctionnalité intégrée offre l’avantage de fournir également une lecture plus aisée du plan, présentant l’algorithme de calcul des données sous une forme arborescente…

… Si tu ne veux pas avoir « la rage » contre la machine RATM
En réalité, ce n’est pas totalement exact… Mais nous verrons cela plus tard!

Premier assaut : une requête simple

Tout ça c’est bien joli, mais en pratique ça donne quoi ??! Commençons par un exemple simple : la recherche d’un ours à partir de son nom :


select * from ours where nom = 'Grosjojo';

Oracle donne le plan :

Comment l’interpréter ? Les informations données dépendent du SGBD utilisé… Ici, l’optimizer Oracle nous indique l’algorithme suivant :

  • TABLE ACCESS FULL (OURS) :
    Il parcourt toute la table OURS

    • Prédicats de filtre NOM = ‘Grosjojo’ :
      Il filtre les records sur le champ NOM, et ne conserve que les valeurs ‘Grosjojo’

  • COST = 3 :
    Il indique enfin une estimation du temps d’exécution de la requête (3 UT)

Unité de temps arbitraire

Un autre exemple : jointure

On recherche à présent le nom de la rivière où habite l’ours Grosjojo…


select r.nom
from ours o, riviere r
where r.idriviere = o.idriviere
and o.nom = 'Grosjojo';

Le SGBD donne le plan :

L’exécution de cette requête plus complexe nous est alors décrite sous forme arborescente, chaque opération fille permettant l’exécution de l’opération mère :

  • NESTED LOOPS :
    Nous allons parcourir 2 tables pour construire la jointure…

    • TABLE ACCESS FULL (OURS)
      P
      rédicats de filter O.NOM = ‘Grosjojo’ :
      Comme précédemment, parcours de la table OURS, en filtrant sur le record ayant le NOM ‘Grosjojo’
    • TABLE ACCESS BY INDEX ROWID (RIVIERE)
      Accès aux records de  la table RIVIERE, mais cette fois-ci par accès par adresse (ROWID)

      • INDEX UNIQUE SCAN (SYS_COO4167)
        Prédicats d’accès  R.IDRIVIERE = O.IDRIVIERE :
        Pour chaque record de OURS filtrée précédemment, on recherche le record de RIVIERE associé, en utilisant la clef primaire IDRIVIERE. Cette recherche est faite dans l’index SYS_COO4167 (c’est le petit nom interne de l’index de clef primaire de la table RIVIERE). Elle retourne le ROWID du record dans la table RIVIERE utilisé ci-dessus (i.e. son “adresse”)

Un dernier pour la route : utilisation de fonction/opérateur

Nous voulons à présent trier les saumons par taille.


select *
from saumon
order by taille

Le SGBD donne le plan :

Nous voyons ici le coût de l’opération de tri (SORT) induite par le ORDER BY sur les saumons. De la même façon, nous pourrons voir les coûts supplémentaire si nous comptons les lignes (COUNT(colonne)), ou si nous les sommons (SUM(colonne)), etc…

… fumés (oui, celle là, elle était facile)

Retour sur les stratégies de l’optimizer

Ok, nous venons de voir quelques exemples de plans d’exécution, nous indiquant comment l’optimizer décompose une requête en un algorithme de recherche, en combinant des opérations de parcours de données (TABLE ACCESS FULL, TABLE ACCESS BY INDEX ROWID, INDEX UNIQUE SCAN…). Mais dis moi Jamy, quelle différence y a-t-il entre tous ces algorithmes ?

Et bien Fred, c’est très simple, on retrouve la même différence qu’entre une recherche dans une ArrayList, un Array, et un TreeSet, avec des performances significativement différentes. Mais voyons tout cela plus en détail, si tu le veux bien…

FULL TABLE SCAN (FTS)

Le FULL TABLE SCAN (ou TABLE ACCESS FULL) est un parcours séquentiel d’une table (similaire à un Iterator sur une ArrayList). C’est l’algorithme utilisé par défaut pour un SELECT

Il peut cependant coûter cher ! En effet, son temps d’exécution est linéaire (proportionnel au nombre total de lignes de la table parcourue).

Si la table est grosse, un tel algorithme de recherche va être très inefficace pour rechercher un record. Par contre, il reste le plus efficace sur une requête qui doit retourner “beaucoup” de records (beaucoup = 5 à 10% de la table au moins). En effet, quite à devoir lire “toutes” les données de la table, autant le faire une seule fois, dans l’ordre naturelle de lecture…

INDEX SCAN

Un index est une structure de données,  contenant une copie de certaines colonnes (une ou plusieurs) de la table “indexée”, stockées sous forme arborescente (c’est en général un b-tree)

Il permet de récupérer lors d’une recherche :

  • la valeur des colonnes indexées
  • l’adresse (rowId) du record complet dans la table indexée

Une telle structure arborescente offre des temps d’accès logarithmiques à une donnée ; il est donc bien plus intéressant que le FTS pour rechercher un record dans de gros volumes de données… Néanmoins, plus il y aura de records dans le résultat, plus son temps d’exécution va tendre vers celui du FTS… Et même le dépasser à partir d’un plafond critique.

Le 2ème avantage de l”index réside dans le fait qu’il est trié de par sa nature ; il permet donc également d’économiser une opération de tri sur le résultat d’une recherche.

Sans rentrer plus dans le détail aujourd’hui, on pourra constater dans les plans d’exécution, l’existence de plusieurs types d’INDEX SCAN :

  • INDEX UNIQUE SCAN
  • INDEX RANGE SCAN
  • INDEX FULL SCAN
  • INDEX FAST FULL SCAN

ACCESS BY ROWID

C’est un accès au données par adresse (rowId).

Le rowId peut être indiqué directement comme critère de recherche dans une requête, ou être trouvé  par l’intermédiaire d’un index.

L’accès par adresse se fait en temps “constant”.

Debriefing

Ainsi donc, notre ami “plan d’exécution” sera d’une aide précieuse, pour analyser l’algorithme appliqué pour l’exécution d’une requête SQL par le SGBD…

Il nous indiquera les opérations que l’optimizer prévoit d’enchaîner, leur type, et leur coût prévisionnel.  On pourra ainsi avoir une idée des goulots d’étranglement dans le calcul, les opérations les plus coûteuses, qu’il pourrait être intéressant d’optimiser ! En particulier, les FTS, les tris, les jointures, toutes ces opérations coûteuses qu’il serait judicieux, au mieux d’éviter, au pire d’optimiser !

Ok, on sait maintenant où il faut frapper… Reste maintenant à savoir comment ? (… To be continued !!!!)