Utiliser SQL pour optimiser

      Il peut sembler naturel d’utiliser SQL pour travailler sur les données stockées dans un SGBDR. Pourtant, en pratique, ce n’est pas aussi simple et certains obstacles peuvent se présenter.
      La complexité grandissante d’un traitement amène parfois le développeur à tenter de découper une requête SQL, voire de réaliser certaines tâches avec un autre langage de programmation.
      Abandonner SQL n’est pas toujours la bonne approche, ce langage est très souvent l’outil à privilégier pour obtenir les meilleurs performances avec un SGBDR.

Débusquer le Minotaure tapi dans un programme labyrinthe

       Une collègue m’a posé une question concernant l’optimisation d’une fonctionnalité en cours de développement. C’est une démarche intéressante, il est préférable de ne pas avoir à intervenir alors qu’il est déjà trop tard et que la lenteur est constatée en production.
      En extrapolant à partir des premiers résultats obtenus en test, la durée du traitement en production aurait été d’une centaines d’heures. Le traitement devant pouvoir s’exécuter quotidiennement, la situation aurait été inacceptable.
      Le traitement consiste à produire depuis la base de données d’une application transactionnelle un fichier au format défini et imposé, destiné à alimenter une application décisionnelle.
      Le programme est développé avec un AGL produisant au final du C. Avec les explications de ma collègue, j’ai pu déterminer que le principe général du programme était le suivant :

01 REQUETE_A donne CURSEUR_A (SELECT) 02 BOUCLE SUR CURSEUR_A (FETCH) 03 AFFECTATION DE VARIABLES EN UTILISANT CURSEUR_A 04 REQUETE_B UTILISANT UNE DES VARIABLES AFFECTEE EN 03 DONNE CURSEUR_B (SELECT) 05 BOUCLE SUR CURSEUR_B (FETCH) 06 AFFECTATION DE VARIABLES EN UTILISANT CURSEUR_B 07 INSERTION DANS UNE TABLE DE TRAVAIL ou MISE A JOUR DANS UNE TABLE DE TRAVAIL (INSERT / UPDATE) 08 FIN BOUCLE SUR CURSEUR_B 09 FIN BOUCLE SUR CURSEUR_A 10 REQUETE_C sur TABLE DE TRAVAIL donne CURSEUR_C (SELECT) 11 BOUCLE SUR CURSEUR_C (FETCH) 12 ECRITURE DEPUIS CURSEUR_C DANS FICHIER_RESULTAT 13 FIN BOUCLE SUR CURSEUR_C

      Informations sur les différentes étapes :

      Le programme était assez long à lire et les requêtes REQUETE_A et REQUETE_B comprenaient déjà plusieurs dizaines de lignes. Il ne faut cependant pas se laisser impressionner par la complexité apparente, déterminer ce qui doit être produit et évaluer pour chaque tâche si elle peut être faite en SQL ou s’il faut passer par un outil tiers.

Un fil d’Ariane : SQL

      Que pouvons-nous faire en SQL au lieu de le faire au niveau du programme en C ?
      La requête REQUETE_B consistant à utiliser des variables obtenues depuis la requête REQUETE_A était en fait une JOINTURE effectuée au niveau du programme. REQUETE_A et REQUETE_B peuvent donc être remplacées par une requête REQUETE_AB effectuant la jointure SQL des résultats entre REQUETE_A et REQUETE_B.
      L’arrondi effectué par le programme C peut être réalisé en SQL
      Pivoter les résultats (regrouper les données de plusieurs lignes et les afficher dans différentes colonnes sur une seule ligne) peut être réalisé en SQL, voir cet article. La solution retenue est celle basée sur CASE car le programme doit être compatible avec Oracle et PostgreSQL, la table de travail devient inutile
      Le regroupement qui était effectué par la requête REQUETE_C est ajouté à REQUETE_AB qui devient REQUETE_ABC (GROUP BY)
      Cela donne au final UNE requête et le nouveau principe du programme est le suivant :

01 REQUETE_ABC DONNE CURSEUR ABC (SELECT) 02 BOUCLE SUR CURSEUR ABC (FETCH) 03 ECRITURE DANS LE FICHIER RESULTAT 04 FIN BOUCLE SUR CURSEUR ABC
      Petite optimisation supplémentaire par rapport au programme initial : le nombre de lignes ramenées à chaque FETCH n’est plus réalisé ligne à ligne. Il est ajusté de manière à ne pas perdre de temps dû à la latence (SETBUFFERSIZE avec le langage utilisé). Temps total de traitement : moins de 2 minutes.

Conclusion

      Optimiser a posteriori en observant ce qui se passe sur la base est possible mais mieux vaut prévenir que guérir. Dans l’exemple que j’ai donné, la requête B serait bien sûr apparue dans une analyse effectuée avec le traqueur mais même cet outil n’aurait pas immédiatement donné la logique complète du traitement. .
      Le réflexe du DBA peut être de tenter immédiatement d’optimiser la requête, d’ajouter un index etc. Ce n’est pas la meilleure méthode et il est souvent préférable de comprendre globalement ce que doit faire un traitement avant de se lancer dans une optimisation. La principale optimisation consistait en fait ici à réaliser la jointure et le pivot en SQL et non au niveau du programme C.
      Les optimisations les plus importantes sont accomplies pendant la phase de développement. C’est donc le développeur qui peut les réaliser en utilisant le bon outil pour la bonne tâche. Et pour traiter des données stockées dans un SGBDR, difficile de faire mieux que SQL.

Mise à jour : 07/06/2017