10 commandements positifs ou règles impératives à suivre pour que votre SGBDR soit performant, efficace et précis ! valable pour tous les SGBDR, avec des exemples pour MS SQL Server.

COMMANDEMENT N°1

Une base de données relationnelle a été conçue pour gérer des relations. Pas pour faire de la gestion de fichiers.

Autrement dit évitez les tables de 200 colonnes… Gérez un modèle relationnel. Le nombre des jointures dans une requête a bien moins d’importance que le respect du modèle relationnel. Contrairement à une idée reçu, plus votre base sera éparse dans de multiples tables ayant peu de colonnes, et plus rapide en seront les requêtes… !

En effet une base de données travaille essentiellement en mémore et le moins possible sur disque. Autrement dit la notion de fichier, donc d’écriture, de lecture et pire d’enregistrement (ce qui suppose qu’une tête magnétique induise un flux sur une surface réceptive) na pas de réalité dans la logique d’un SGBDR.

Décortiquons son fonctionnement : Une nouvelle base est créée avec des tables. Tout ceci se fait en mémoire. Pas besoin d’écrire sur un disque. Une requête d’insertion de ligne arrive. Les données sont mise dans la table (unité logique) qui est située en mémoire. Une requête de modification arrive. Les modification des données sont effectuées sur les données en mémoire. Des lignes sont supprimées à l’aide de DELETE. Ces lignes de tables sont effacées de la mémoire. A quel moment avez-vous vu des manipulations de fichiers ? Aucune ! En effet l’écriture dans les fichiers de la base n’est que le résultat dicté par la physique des bases de données du fait qu’il est impératif de préserver les données en cas de coupure de la courant, ce qui déchargerait la mémoire. En effet, dans les notions d’acidités des transactions (A.C.I.D : Atomicité, Cohérence, Isolation, Durabilité) le D indique que les données doivent être préservées dans un support inaltérable. En vertu de ce principe, le SGBDR décide d’écrire des fichiers, car c’est la seule chose bien maîtrisée aujourd’hui pour le stockage de masse (il y avait autrefois les cartes perforées [entre nous, c’est encore le support le plus fiable !], et bientôt il y aura les carte flash…). Mais la question fondamentale est… Quand fait-il ces écritures ? Hé bien, quand cela lui chante. Et comme un bon SGBDR se doit d’être optimisé, il implémente tout un tas de techniques pour les rendre les moins perturbantes possible. D’abord les écriture sur disque se font par sessions, par exemple toutes les minutes ou « on iddle ». Ensuite il regroupe les pages à écrire en fonction de leur contiguïté géographique par rapport à la surface du disque. Enfin, il structure ses fichiers en les plaçant aux meilleurs endroits du disque. Je vous laisse deviner ou !
Je vous voit venir… Si le serveur plante entre deux sessions qu’arrive t-il aux données qui ont été mise à jour ? C’est là que le journal de transaction arrive à la rescousse. Toute transaction est d’abord inscrite dans le fichier (unique) du JT avant d’être exécutée. Tant est si bien que si le serveur plante, le jeu consistera à lire le journal des transactions afin de savoir parmi les transactions dernièrement réalisées, quelles sont celles dont les données n’ont pas encore fait l’objet d’écritures dans les fichiers de données.

Maintenant que vous savez qu’un SGBDR travaille exclusivement en RAM, voyons ce que cela implique sur des jointures ou des tables monolithiques. Je vais parler de tables monolithiques, tels qu’étaient les anciens fichiers avec 200 colonnes, comme les tables que l’on voit régulièrement du genre T_PERSONNE avec les colonnes ID, NOM, PRENOM, ADRESSE_DOMICILE, ADRESSE_FACTURATION, ADRESSE_LIVRAISON, MAIL1, TEL_FIXE, TEL_FAX, TEL_GSM…. par opposition à des tables normalisées comme T_PERSONNE (ID, NOM, PRENOM), T_TELEPHONE (ID, NUMERO, TYPE), T_ADRESSE (ID, ADRESSE)…
Imaginons qu’il faille trouver le nom de la personne relatif au n° de téléphone N. Si vous avez une table des téléphones, vous n’aurez donc à monter en mémoire qu’une toute petite table de 3 colonnes. Si elle est indexées sur le n° de téléphone, vous ne montrez en mémoire que quelques pages de cette toute petite table. Vous en tirerez l’ID que vous devrez alors et ensuite rechercher dans la table des personnes, dont c’est la clef primaire… Bref une deuxième tables et quelques pages. Faites le calcul, avec 100 millions de données, la recherche de l’un et de l’autre ne demande environ que 3 pages par table. Dans ces conditions, chaque jointure représente donc 6 lectures de pages au plus. Bref avec 30 jointures vous lirez 200 pages à tout casser !
En maintenant la version « gore » ! Tout dans la même table… Ou dois-je chercher le n° de téléphone ? Dans les fixes, les portables, les fax ??? Bref il me faut faire une requête SQL avec un OR… Donc impossible d’utiliser l’indexation ! Donc je doit lire toutes les pages de la table, mais comme les lignes sont immenses alors que la plupart des colonnes ne me servent aucunement dans cette requête, je dois me farcir un nombre de pages délirant pour ne récupérer qu’une infime partie des données.

Or la RAM n’est pas extensible à l’infini… Que va t-il se passer si la table dépasse la capacité de la mémoire vive ? C’est simple, il va falloir faire appel au disque pour aller charger les pages manquante en RAM depuis les fichiers de données. pages nouvelles qui vont devoir pousser dehors les pages anciennes… Quelle importance me direz-vous ? Simple : une légère différence de temps d’accès. D’un côté 9 nano seconde pour la RAM, de l’autre 10 ms pour le disque. En bref un rapport de 1 à 1 million en faveur de la RAM ! En réalité un peu moins vu qu’entre les deux il y a le processeur et donc des bus avec des cadences différentes. En fait au moins 1000 !

Comprenez que plus les tables seront petite en nombre de colonnes, conformément aux principes de normalisation (et oui, les fameuses formes normales…) et plus dense sera l’information traitée en mémoire, car largement économisé. C’est bien évidemment le contraire avec des tables monolithiques… Or entre 200 pages lues en mémoire du fait des 30 jointures et un nombre incalculables de pages lues depuis un disque, je vous laisse deviner quelle sera la base la plus performante (et de très loin…) !

En conclusion, pour ne pas avoir voulu faire des tables normalisées et donc gérer des relations, certains ont réinventé le fonctionnement des fichiers COBOL ! Brillant non ?

COMMANDEMENT N°2

Une clef purement informatique est souvent préférable à une clef purement sémantique.

Certains pensent qu’une clef significative est obligatoire pour toute table. Si la chose est concevable à l’aide de l’immatriculation pour un véhicule, du matricule pour un salarié ou d’un numéro de sécu pour un patient, elle n’en reste pas moins dangereuse et contre performante.

Dangereuse parce qu’il faut que la clef soit connue pour pouvoir saisir l’information. Tôt ou tard vous vous heurterez au problème : la voiture a été commandée et pour l’assurer il faut la rentrer dans le SI… problème l’immatriculation n’est pas encore faites ! Vous voulez employer Paul, le frère jumeau de Marc. Problème, votre SI a bien prévu que vous utiliseriez le numéro de sécu, mais n’a pas prévu le cas des jumeaux et la fameuse clef lunaire !

De plus l’un des dangers absolus d’une clef significative est son évolution. En effet les changements de type ou de valeur de certaines informations sont légion. Si cela touche à la clef, la modification peut s’avérer catastrophique. En effet, que devez vous faire des informations archivées ? Que se passera t-il dans le cas d’une restauration partielle si la structure de la clef a été modifiée entre temps ? Quel est le coût du report du changement de valeur d’une seule clef si elle est référencée par plusieurs milliers de lignes filles ?

Mais la pire des choses réside encore dans la contre performance qu’une telle clef entraîne dans son exploitation.

Récemment, dans un de mes audits, une des clefs d’une table était formée de cinq colonnes : char(50), varchar(128), varchar(128), varchar(32), varchar(128) … Soit 466 octets de données pour la clef. Si l’on considère qu’un processeur fonctionne sur 32 bits, soit 4 octets, cela veut dire que pour lire une telle clef, il faut dans le pire des cas 117 passes. Ajoutons à cela les problématiques de collation et l’on s’aventure dans les 200 passes pour lire la clef.

En revanche en prenant un entier de 32 bits, il faut… une seule passe pour lire la clef !

Une telle clef composée nécessite donc plus de 100 fois de travail qu’une simple clef numérotée.

Assenons un dernier coup… Avec un CHAR de 4 identique en cout de stockage à un INTEGER (ne prenons pas un VARCHAR de 4 c’est idiot, il lui faut 6 octets au pire car il doit en sus stocker la longueur réelle de la donnée), combien de valeurs différentes de clefs puis-je stocker ? 4 x 8 = 32 => 2^32 – 1… Soit 4 294 967 295 ? Eh bien pas du tout… En effet la table des caractères compte déjà 32 caractères non imprimables (ceux de 0 à 1F en hexadécimal, comme par exemple la sonnette 7 !). Donc en théorie votre clavier vous permet donc (256 – 32) ^ 4 = 2 517 630 976 C’est déjà 2 fois moins de clefs possible. Mais allez-vous franchement demander à vos clients de saisir comme code : # »{,|@ùÈ ??? Donc si l’on enlève tous les caractères de ponctuation et les lettres accentuées, il vous reste exactement 62 caractères en comptant majuscule et minuscule. Et comme le risque est grand de se tromper dans la saisie d’un code comme dEfH0iI, vous en viendrez a ne plus devoir faire de différence entre majuscules et minuscules ce qui vous réduit les possibilités aux 26 lettres de l’alphabet et aux 10 chiffres soit 36 combinaisons, c’est à dire au plus 36 ^ 4 valeurs de clefs possible soit 1 679 616 combinaisons. Quelle excellent rendement que d’avoir perdu 99,92 % de clefs potentielles. Que dirait-on d’un moteur dont le rendement est de 0,01 % ?

Ne parlons pas non plus des sur-lectures engendrées au cas où la clef composite est fréquemment mise à jour. Dans un tel cas il n’est pas rare que la lecture de la clef engendre une quantité faramineuse de lectures de page dues au fait du déplacement de la donnée qui ne peut être stockée à l’emplacement de l’ancienne trop étroit.

Enfin, les aficionados de la clef significative ne savent sans doute pas que MS SQL Server construit par défaut un index cluster pour toute clef primaire. Si un tel index représente le sommet de l’optimum pour ce qui est d’une clef dont les valeurs sont en constante croissance dans la vie de la base données, un tel index représente un véritable calvaire pour le processeur et le moteur de stockage si la valeur de la clef d’une nouvelle ligne à insérer est aléatoire. En effet dans un tel cas il faut « déplacer » en moyenne la moitié des lignes de la table à chaque insertion, modification… Je vous laisse imaginer le travail ! Au moins ces personnes-là, si elles se reconnaissent devrait immédiatement chasser le cluster pour le remplacer par un index en heap. Ce sera déjà le remaniement de toutes les lignes de gagner…

Donc résumons-nous : plus la clef et petite, stable dans le temps et en continuelle croissance (série mathématique), meilleure elle sera. Conclusion : choisissez (ou plutôt imposez-vous) une clef de type entier auto incrémentée. Ce qui n’empêche pas (et au contraire on devrait s’y obliger) de doter les clefs sémantiques de contrainte d’unicité en vertu du fait que ce sont des clef subrogées, c’est à dire d’anciennes clefs candidates.

Mais au fait pour un processeur 64 bits… quelle serait la meilleure clef ?

COMMANDEMENT N°3

Évitez impérativement « SELECT * »

Moins les données circulent, plus les temps d’exécution sont écourtés. Mieux vaut donc mettre dans la liste des colonnes uniquement celles nécessaires, pour ne pas renvoyer systématiquement tout.
Moins le serveur à d’effort à faire pour retrouver les informations dans les tables systèmes (nom et ype des colonnes par exemple, mais aussi privilèges…) plus la préparation de la requête est écourtée.
Même si vous voulez renvoyer actuellement toutes les colonnes pour les afficher toutes, que ferez-vous si la table est remaniée ultérieurement notamment en ajoutant une colonne ? Le risque de voir votre code client ne plus fonctionner est assez important.

Mais il arrive parfois, et même de plus en plus, que le développeur de bonne foi, soit confronté à des SELECT * qu’il n’a pourtant pas demandé. Ce sont en général de merveilleux outils de développement, dont le choix a été fait « à la va-vite » et plus sur leur aspect que sur leurs qualités, qui en sont la cause. A l’heure du développement Web rapide, ce genre de chose est de plus en plus courant…

Le pire en ce domaine est l’utilisation d’Access en frontal de MS SQL Server. Ceux qui se sont contenté d’utiliser le RAD d’Access sans tenir compte de ce que faisait le moteur JET derrière ont dû avoir de très mauvaises surprises lors des montées en charge. Il y a quelques temps j’ai dû rendre un « arrêt de mort » sur un logiciel développé par un grand éditeur. Celui-ci avait été racheté à une petite boîte qui avait réalisé cela « à la va vite » avec Access et eût l’idée ingénieuse de porter cela en SQL Server à l’aide de 3 clics. Le résultat était particulièrement catastrophique. En effet Access a une conception curieuse de l’accès aux données : pour afficher une seule information, il fait un SELECT * sur la table et filtre sur le client. Imaginez le temps d’attente lorsque la table passe de 300 lignes à quelques centaines de milliers…

Seule exception à cette règle, les sous requêtes corrélées encapsulées par le prédicat EXISTS. Préférez lorsque cela est possible le SELECT *. SQL Server remplacera cette étoile par une constante quelconque la mieux calibrée pour pulser les performances. Mais sachez que cela n’est pas toujours possible… Tiens … Dans quel cas ? Je vous y laisse réfléchir.

COMMANDEMENT N°4

Évitez tout curseur

Horreur ! Malheur ! Les curseurs ne sont autre que le moyen de rendre itératif ce qui est nativement ensembliste. Or un SGBDR est fait pour traiter naturellement de manière ensembliste les données. Il est optimisé pour cela et le code ensembliste (donc à base de requêtes) sera toujours plus rapide.

Depuis la version SQL:1999 implémentant la récursivité des requêtes, SQL est un langage complet au sens mathématique du terme. C’est à dire capable de résoudre tout problème au sens de la machine de Turing. En d’autre mots, une seule requête suffit à répondre à tout traitement des données, quelque soit la complexité demandée !

Mais pourquoi un curseur est-il à ce point mauvais ?
Un curseur permet de parcourir un jeu de résultat provenant d’une requête. Dans cette manÅ“uvre on se retrouve comme au bon vieux temps de papa et des dinosaures informatiques qu’étaient les IBM 360 et autres monstres antédiluviens capables seulement de lire ligne à ligne les données d’un fichier COBOL ! Non, ne rêvez pas, le curseur a bien été fait pour cela à l’origine : encapsuler des résultats de requête dans un traitement COBOL. Vous en voulez la preuve ?

   1. ***    

   2.    

   3.    

   4.    

   5. => déclaration des variables locales COBOL pour récupérer les valeurs de la ligne pointée par le curseur    

   6.    

   7. EXEC SQL BEGIN DECLARE SECTION END-EXEC.    

   8.    

   9. 01 PARTNUMBER PIC X(16).    

  10.    

  11. 01 PARTNAME PIC X(30).    

  12.    

  13. 01 PARTNAMEIND SQLIND.    

  14.    

  15. 01 SALESPRICE PIC S9(8)V99 COMP-3.    

  16.    

  17. 01 SALESPRICEIND SQLIND.    

  18.    

  19. EXEC SQL END DECLARE SECTION END-EXEC.    

  20.    

  21. 01 OK PIC S9(9) COMP VALUE 0.    

  22.    

  23. 01 NOTFOUND PIC S9(9) COMP VALUE 100.    

  24.    

  25. ...    

  26.    

  27. PROCEDURE DIVISION.    

  28.    

  29. => déclaration du curseur par appel SGBDR depuis Cobol !    

  30.    

  31. EXEC SQL DECLARE PRICECURSOR    

  32.    

  33. CURSOR FOR    

  34.    

  35. SELECT PARTNUMBER, PARTNAME, SALESPRICE    

  36.    

  37. FROM PURCHDB.PARTS    

  38.    

  39. WHERE SALESPRICE = :SALESPRICE    

  40.    

  41. FOR UPDATE OF SALESPRICE    

  42.    

  43. END-EXEC.    

  44.    

  45. => ouverture du curseur par appel SGBDR depuis Cobol !    

  46.    

  47. EXEC SQL OPEN PRICECURSOR END-EXEC.    

  48.    

  49. IF SQLCODE = OK    

  50.    

  51. PERFORM LABEL-DISPLAY-ROW THRU LABEL-DISPLAY-ROW-EXIT    

  52.    

  53. UNTIL SQLCODE = NOTFOUND    

  54.    

  55. ELSE    

  56.    

  57. IF SQLCODE = NOTFOUND    

  58.    

  59. DISPLAY "No rows have the salesprice specified!"    

  60.    

  61. ELSE    

  62.    

  63. PERFORM SQL-STATUS-CHECK.    

  64.    

  65.    

  66.    

  67. LABEL-DISPLAY-ROW.    

  68.    

  69. => alimente la première ligne du curseur par appel du SGBDR depuis Cobol !    

  70.    

  71. EXEC SQL FETCH PRICECURSOR    

  72.    

  73. INTO :PARTNUMBER,    

  74.    

  75. :PARTNAME PARTNAMEIND,    

  76.    

  77. :SALESPRICE SALESPRICEIND    

  78.    

  79. END-EXEC.    

  80.    

  81. IF RESPONSE = '/'    

  82.    

  83. GO TO DISPLAY-ROW-EXIT    

  84.    

  85. ELSE    

  86.    

  87. IF RESPONSE = 'D'    

  88.    

  89. => mise à jour de la table à travers le curseur par appel du SGBDR depuis Cobol !    

  90.    

  91. EXEC SQL DELETE FROM PURCHDB.PARTS    

  92.    

  93. WHERE CURRENT OF PRICECURSOR    

  94.    

  95. END-EXEC    

  96.    

  97. ELSE    

  98.    

  99. IF RESPONSE = 'U'    

 100.    

 101. EXEC SQL UPDATE PURCHDB.PARTS    

 102.    

 103. SET SALESPRICE = :SALESPRICE    

 104.    

 105. WHERE CURRENT OF PRICECURSOR    

 106.    

 107. END-EXEC    

 108.    

 109. LABEL-DISPLAY-ROW-EXIT.    

 110.    

 111. => fermeture du curseur par appel du SGBDR depuis Cobol !    

 112.    

 113. EXEC SQL CLOSE PRICECURSOR END-EXEC.    

 114.    

 115. 

 116. ***

=> déclaration des variables locales COBOL pour récupérer les valeurs de la ligne pointée par le curseur  

 

EXEC SQL BEGIN DECLARE SECTION END-EXEC.  

01 PARTNUMBER PIC X(16).  

01 PARTNAME PIC X(30).  

01 PARTNAMEIND SQLIND.  

01 SALESPRICE PIC S9(8)V99 COMP-3.  

01 SALESPRICEIND SQLIND.  

EXEC SQL END DECLARE SECTION END-EXEC.  

01 OK PIC S9(9) COMP VALUE 0.  

01 NOTFOUND PIC S9(9) COMP VALUE 100.  

 

...  

 

PROCEDURE DIVISION.  

 

=> déclaration du curseur par appel SGBDR depuis Cobol !  

 

EXEC SQL DECLARE PRICECURSOR  

CURSOR FOR  

SELECT PARTNUMBER, PARTNAME, SALESPRICE  

FROM PURCHDB.PARTS  

WHERE SALESPRICE = :SALESPRICE  

FOR UPDATE OF SALESPRICE  

END-EXEC.  

 

=> ouverture du curseur par appel SGBDR depuis Cobol !  

 

EXEC SQL OPEN PRICECURSOR END-EXEC.  

IF SQLCODE = OK  

PERFORM LABEL-DISPLAY-ROW THRU LABEL-DISPLAY-ROW-EXIT  

UNTIL SQLCODE = NOTFOUND  

ELSE  

IF SQLCODE = NOTFOUND  

DISPLAY "No rows have the salesprice specified!"  

ELSE  

PERFORM SQL-STATUS-CHECK.  

LABEL-DISPLAY-ROW.  

 

=> alimente la première ligne du curseur par appel du SGBDR depuis Cobol !  

 

EXEC SQL FETCH PRICECURSOR  

INTO :PARTNUMBER,  

:PARTNAME PARTNAMEIND,  

:SALESPRICE SALESPRICEIND  

END-EXEC.  

IF RESPONSE = '/'  

GO TO DISPLAY-ROW-EXIT  

ELSE  

IF RESPONSE = 'D'  

 

=> mise à jour de la table à travers le curseur par appel du SGBDR depuis Cobol !  

 

EXEC SQL DELETE FROM PURCHDB.PARTS  

WHERE CURRENT OF PRICECURSOR  

END-EXEC  

ELSE  

IF RESPONSE = 'U'  

EXEC SQL UPDATE PURCHDB.PARTS  

SET SALESPRICE = :SALESPRICE  

WHERE CURRENT OF PRICECURSOR  

END-EXEC  

LABEL-DISPLAY-ROW-EXIT.  

 

=> fermeture du curseur par appel du SGBDR depuis Cobol !  

 

EXEC SQL CLOSE PRICECURSOR END-EXEC.

Certes ceux qui n’ont pas connu Cobol auront du mal à comprendre ce code abscons…

Bref à chaque fois que vous voudrez faire un curseur, pensez que vous faites du Cobol. Si cela vous répugne, tant mieux. Plus vous éviterez les curseurs, mieux vous vous porterez.

Mais pourquoi ?

Tout simplement parce que les SGBDR savent travailler de manière très performante sur des données manipulées de manière ensembliste (requêtes SQL). Pour ce faire, ils disposent d’un optimiseur de requête qui calcule à chaque fois que nécessaire, la meilleure manière parmi les combinaisons les plus réalistes, de traiter votre demande. Faut-il commencer par la table X ou la table Y ? Le filtre bidule sera-t-il plus sélectif appliqué en premier que la condition chose ? L’accès aux données peut-il être parallélisé pour lire les données de machin en même temps que les données de truc ? C’est lui qui décide en fonction des structures de données, des index, des statistiques (max, min, dispersion…), des types, et même du hardware !…. Mais dès que le code est itératif, plus aucune optimisation n’est possible. C’est vous qui décidez dans quel sens lire, manier, traiter, manipuler, tripatouiller, estrapasser, … les données. Avez-vous les moyens de choisir la meilleure manière de concevoir votre traitement, et cela en le modifiant au gré de l’accroissement du volume de données ou de la montée en charge des utilisateurs ? Avez-vous les moyens de réinventer la roue ? Non bien sûr. Alors passez-vous de curseurs.

C’est impossible me direz-vous ! Que nenni. Rares sont les bases de données bien conçues où le recours au curseur est le seul moyen de traiter le problème. Même moi, je me suis fait piégé. ! C’est vous dire !!!

Dans mes audits, je constate que 80% des curseurs peuvent être remplacés par des requêtes. Quand au 20% restant, ils sont souvent dû à un mauvais modèle, ou tout au moins à un modèle qui ne répond pas pleinement à la problématique de traitement. Certes il existe des astuces pour passer du curseur à la requête. Mais le truc le plus simple consiste à maîtriser l’écriture des requêtes SQL. Une requête, même de 3 pages en SQL, remplacera toujours avantageusement en terme de performances, un curseur écrit en 10 lignes. Oui, c’est dur, Non, vous n’avez pas le temps, je sais…

Et comme nous l’avons dit, la norme SQL:1999 apportant la récursivité, SQL est devenu un langage complet, c’est à dire que tout traitement, aussi complexe soit-il peut s’écrire en une seule requête SQL ! Je met donc au défi quiconque de me donner un traitement qui ne puisse être remplacé par quelques requêtes (mon tarif moyen n’est que de 111,48 € en 2008). Mais peut être faudra-t-il revoir votre modèle de données…

Au fait, pourquoi ne pas supprimer les curseurs ? Impossible. Mais sauriez vous pourquoi ???

COMMANDEMENT N°5

Moins ça dure, plus c’est bon…

Une transaction nécessite obligatoirement un accès souvent exclusif aux données. Pour ce faire des verrous sont nécessaires. Les verrous induisent des temps d’attentes pour les utilisateurs concurrents. Cela génère de la contention… Comme le bon vieil embouteillage le vendredi soir sur le périphérique !

Faisons la comparaison avec votre entreprise… Comme dans toute entreprise, il y a des sanitaires. Ces sanitaires sont séparés pour éviter le mélange des sexes, mais cela n’est qu’un détail dans notre cas. Je suis désolé de cette comparaison, mais c’est la seule que j’ai trouvée dans la vie courante où l’on trouve encore un verrou. Au moins, j’espère qu’elle vous fera rire ! Si vous voulez utiliser les toilettes, il vous faut vous barricader à l’aide d’un verrou dans une pièce exigu afin de faire vos besoins. Moins vous y restez, plus de personnes pourront faire leurs besoins, cela se comprend, c’est mathématique. Imaginons maintenant une autre solution. On doit vous amener le pot à votre bureau. Bien entendu, vous refusez que tout le monde vous voit pousser votre étron et exigez que l’on apporte une cabine. Cela existe : un simple rideau monté sur des tringles métalliques posé sur quatre tiges. Passons sur les détails et incommodités envers vos voisins. Constatez simplement que l’indisponibilité du pot comme du rideau est bien plus longue parce qu’il faut transbahuter tout le toutim. Bref vous perdez plus de temps à acheminer le pot à votre emplacement que le temps qu’il vous faut pour vous soulager. De plus un nouveau risque apparaît : le porteur peut se casser la figure en chemin… Non, pas de détails SVP !!!

Vous avez compris, l’avantage d’utiliser un WC fixe et centralisé c’est qu’il permet de débiter plus de nécessiteux que la solution itinérante qui de plus isole mal.

Transposé au monde des bases de données, cela revient à dire qu’une transaction pilotée côté serveur sera bien plus performante et assurera une meilleure montée en charge que sur le poste client. Elle sera naturellement plus courte.

Attendez la suite… A votre bureau vous pouvez continuer à travailler ce qui n’est pas le cas lorsque vous êtes dans la situation sus mentionnée. Alors qu’à peine vous avez commencé votre travail de forçage, votre meilleur client vous appelle. Vous en êtes entre la poire et le fromage et devez discuter des détails techniques du nouveau projet… Alors on vous passe le combiné aux toilettes… Combien de temps allez-vous mobiliser ainsi tout ce beau matériel en pure perte ?

Oui, parce que lorsque l’on fait des transactions sur le poste client, il arrive qu’en plus on programme une demande utilisateur. Bien entendu, juste au moment où il devrait répondre oui ou non, l’utilisateur est accaparé par Martine la déléguée du personnel auquel on vient d’apprendre que Joseph va être licencié… Il s’en suit une discussion à la cafétéria pendant que le serveur attend une éternité. Et comme chacun sait, une éternité, c’est long, surtout vers la fin ! (C’est pas de moi, c’est d’un certain Alien, Woody de son prénom)

Vous l’aurez compris, une solution saine (si je puis m’exprimer ainsi) est de faire des transactions les plus courtes possible, donc sur le serveur et par conséquent jamais sur le poste client. Et bien entendu d’éviter toute interaction (en principe impossible si effectuée sur le serveur).

COMMANDEMENT N°6

Utilisez du SQL dynamique pour des requêtes SQL simple

Aussi curieux que cela puisse paraître MS SQL Server est plus rapide pour des petites requêtes lancées de manière dynamique que pour ces mêmes requêtes statifiées, paramétrées ou encore encapsulées dans des procédures ou des fonctions. Point besoin donc de PREPARE. Construisez votre requête comme une chaîne de caractères et exécutez-la. Mais attention, assurez-vous que ce sont des petites requêtes (quelques tables tout au plus) et écrivez les toujours de la même façon : respectez la casse et la présentation. Nous verrons dans le principe suivant que ceci à son importance !

COMMANDEMENT N°7

Paramétrer la bonne collation…

Tous mes audits, et même les audits effectués chez de grands éditeurs de logiciels, montrent que peu de techniciens maîtrisent les problématiques de collations et leurs conséquences.

Tout d’abord, nous devons définir ce qu’est une collation. J’ai écrit de nombreux papiers sur le sujet, mais une foi encore, je préfère m’expliquer directement…

Une collation sert à gérer la manière dont les chaînes de caractères constituant les données de la base, vont se comporter face aux opérateurs de comparaison et à l’ordonnancement des données (tri) :

• Un mot en majuscule est-il équivalent à ce même mot en minuscule ?
• Un mot accentué est-il égal à sa version dénuée d’accents ?
• Un mot encodé en ASCII est-il comparable à ce même mot codé en UNICODE ?
• Un mot utilisant des ligatures (par exemple e dans l’o comme dans cÅ“ur) peut-il être considéré comme identique à son écriture déligaturée ?

A ces quatre questions, ainsi qu’au choix de l’algorithme de tri, la réponse est apportée par la technique de gestion des collations…
SQL Server permet l’utilisation de plusieurs centaines de collations. Leur nom permet de connaître immédiatement leurs caractéristiques.
La première partie du nom de la collation est la langue et pilote l’algorithme de tri. La seconde partie est composée d’un ou plusieurs éléments dont voici la liste :

• BIN collation binaire : la comparaison s’effectue par octets
• CI « Case Insensitive » : la collation est insensible à la casse (le prédicat ‘TOTO’ = ‘toto’ est évalué à vrai).
• CS « Case Sensitive » : la collation est sensible à la casse (le prédicat ‘TOTO’ = ‘toto’ est évalué à faux).
• AI « Accent Insensitive » : la collation est insensible aux caractères diacritiques (accents, cédille, ligatures… le prédicat ‘pâté’ = ‘pate’ est évalué à vrai).
• AS « Accent Sensitive » : la collation est sensible aux caractères diacritiques (le prédicat ‘pâté’ = ‘pate’ est évalué à faux).
• WS « Wide Sensitive » : la collation est sensible à la largeur d’encodage (le prédicat N’toto’ = ‘toto’ est évalué à faux, N’toto’ étant codé en unicode avec 2 octets par caractère tandis que ‘toto’ est codé en ASCII à raison d’un octet par caractère)
• KS « Kana Sensitive » : la collation permet de distinguer les syllabes du katakana et du hiragana (dialectes japonais)

Même s’il parait très amusant de pouvoir confondre majuscules et minuscules, accents et lettres nues… c’est généralement une très mauvaise idée que de le permettre au niveau du serveur. Or par défaut MS SQL Server à la très mauvaise idée de calquer le paramétrage de la collation lors de l’installation du serveur, sur les caractéristiques des pages de codes de Windows. Il s’ensuit que sur un OS français la collation d’installation du serveur est French_CI_AS.

Pourquoi ceci n’est pas bon ? D’abord pour des raisons fonctionnelles : un mot de passe nécessite un respect fort de la collation afin d’être moins facile à « hacker ». Une référence alphanumérique (référence constructeur par exemple) se doit d’être unique compte tenu de la casse. Ensuite pour des raisons de performances : tout le monde sent bien que pour faire confusion entre lettres accentuées ou non, majuscules ou minuscules, il faut un certain travail derrière et comme tout travail, un temps de traitement qui n’est jamais négligeable pour celui qui veut de la performance. Enfin parce que certains effets pervers plus profonds peuvent se produire. De toute façon, vous pouvez toujours prévoir une collation différente de celle installée au niveau du serveur en ce qui concerne la base de données, comme en ce qui concerne la colonne d’une table, mais attention : tout ceci n’est valable que lors de la CREATION de l’objet. Aucune manœuvre d’aucune sorte n’est capable de changer la collation à un niveau quelconque (serveur, base ou colonne) sans migrer les données. De plus, c’est la collation du serveur sur laquelle se repose tous les objets. Autrement dit, la collation supplémentaire que vous affecterez à une base ou une colonne viendra en quelque sorte se superposer à celle du serveur.

Par conséquent, le choix de la collation du serveur est primordial en terme de fonctionnalité et de performances. Or pourquoi choisir une collation « passoire » comme French_CI_AS qui ne correspondra en réalité qu’à peu de cas concrets dans la base ?

Parlons maintenant de l’effet pervers d’une collation lâche (insensible à la casse ou aux accents…)… Lorsque vous envoyez une requête avec une telle collation, SQL Server se fiche de la façon dont vous avez créé les noms identifiant les tables, les colonnes… A contrario, l’emploi d’une collation serrée (respect de la casse et des accents) oblige d’écrire des requêtes dont le nom des objets est stricto scriptus celui qui a été introduit au moment de la création.

Or SQL Server gère un cache de procédure qui, quelle que soit la collation du serveur est sensible à tout du fait de l’algorithme de hachage utilisé pour repérer si la requête a déjà été exécutée ! Autrement dit pour MS SQL Server les phrases :

Sont quatre procédures qui génèrent quatre plans d’exécution distincts et s’exécutent dans quatre environnements différents. Autrement dit, pour une simple histoire de collation, vous perdez tout le bénéfice de la mise en cache et de la notion de réutilisabilité ! En sus chacune des procédure mise en cache vieillit plus vite et donc dégage plus vite du cache en cas de pression mémoire…

Pour vous en convaincre et si votre serveur a été installé dans une collation lâche, faîtes :

Lisez simplement la table et vous constaterez la présence de deux lignes pour nos deux requêtes identiques.

Au fait, j’espère que vous ne testez jamais ce genre de code sur le serveur de production… Dans cet exemple, avec une telle manœuvre, vous perdriez tout le bénéfice de toutes les mises en cache de toutes les procédures et requêtes.

Pour finir, une question : Comment passer d’une collation à une autre pour les données d’une table qui existe déjà ?

COMMANDEMENT N°8

N’utilisez JAMAIS le SELECT MAX(…) + 1 pour générer une clef

Grande est la tentation de la facilité pour générer un compteur que d’utiliser le SELECT MAX(MaColonne) + 1 afin de calculer le numéro suivant : ce mode de calcul est une hérésie absolue et ne peut que conduire un jour ou l’autre au mieux à un télescopage de clefs au pire à un blocage total. En effet tant que la nouvelle ligne pourvue de cette nouvelle clef n’est pas encore insérée dans la base, n’importe quel autre processus peut effectuer le même calcul et se retrouver à générer la même valeur. Et ce n’est pas en utilisant une transaction pour effectuer ce calcul que vous assurerez l’absolue unicité de la valeur. Il faudrait pour cela que la transaction incorpore à la fois le calcul deu MAX + 1 et l’ordre d’insertion avec la nouvelle clef. Non, le problème est bien plus complexe qu’il n’y parait. J’ai aussi consacré un article à ce sujet (l’auto incrémentation des clefs des tables). Je vous invite à le lire pour en comprendre la perversité et le moyen d’y remédier. En gros, disons qu’un tel compteur doit être généré en dehors de la table qui l’utilise et doit se faire au sein d’une transaction dont le niveau d’isolation doit être le plus élevé, c’est-à-dire SERIALIZABLE.

Mais il y a plus simple : utiliser un des mécanismes d’auto incrément dont votre SGBDR est pourvu et qui de plus est normalisé (IDENTITY ou SEQUENCE). Au fait, lequel est-ce pour votre SGBDR favori ?

COMMANDEMENT N°9

Utilisez le moins possible d’objets temporaires

Chaque objet temporaire créé au sein d’un SGBDR est instancié, c’est à dire physiquement écrit au moins dans un journal de transaction, mais le plus souvent dans les données de la base ou d’une base particulière (par exemple tempdb dans MS SQL Server). Cela signifie que l’objet est réellement instancié : une structure de données est crée dans la base et les données y sont écrites.

Par conséquent, c’est lent !

Tout objet temporel créé par un utilisateur possède une durée de vie liée à la connexion : tant que dure la connexion, l’objet persiste. Lorsque la connexion cesse ou que l’utilisateur détruit explicitement l’objet, celui-ci disparaît.

Par conséquent, c’est lourd.

Le pire est généralement constitué par des automates (services par exemple) qui sont connectés en permanence à la base : s’ils créent des objets temporaires régulièrement, cela peut conduire à une accumulation qui, au mieux rend la base tempdb lente parce qu’énorme, au pire sature le disque et rend le serveur inexploitable !

Or il existe de nombreux moyens d’éviter les tables temporaires, à commencer par des requêtes plus imbriquées ou encore en utilisant des fonctions table.

En théorie SQL langage complet ne nécessite pas de tables temporaires, en particulier si votre SGBDR prend en charge les CTE….

Et pour vous en convaincre, je vous renvoie à cet autre article : http://blog.developpez.com/sqlpro/p6217/langage-sql-norme/tables-temporaires-et-persistance/

COMMANDEMENT N°10

Utilisez des index, mais à bon escient

Celui que le Canard Enchaîné appelait « le grand balamouchi » avait coutume de dire : « trop d’impôts tue l’impôt ». Ce qui ne l’a d’ailleurs pas empêché de faire comme tout ses prédécesseurs : inventer de nouvelles formes de fiscalité et prélèvements pour assurer à l’état un train de vie toujours en croissance ! Et bien, cette formule fait parfaitement l’affaire en matière d’indexation des bases de données.

Donnons une quantification statistique : avec 3 à 5 index par table, une base OLTP est dans la bonne moyenne. Au delà, il faut se pencher sur les raisons qui ont obligé à une telle indexation (elle peut être juste….). En dessous, c’est peut être un peu faible. Cette moyenne peut ensuite évoluer en fonction de la stabilité des données (data warehouse) et du volume des données (V.L.D.B.).

Posez-vous aussi la question de l’adéquation du type d’index avec les données à indexer : par exemple SQL Server créé des index CLUSTERED par défaut sur toute clef primaire d’une table. Si un tel type d’index est parfait pour une clef monotone et stable, autrement dit un auto incrément, cela peut s’avérer un désastre pour tout autre type de données et en particulier pour les colonnes alphanumériques de taille variable… Dans ce cas spécifiez au moment de la création de la contrainte de clef primaire, un type NONCLUSTERED pour l’index sous jacent à la clef de la table.

Conclusion

Il y aurait bien d’autres choses à dire sur ce qu’il faut faire et ne pas faire avec un SGBDR pour rendre vos bases performantes et agréables à développer. Par exemple qu’une règle de nommage bien pensée est un outil puissant permettant d’introduire de la généricité, donc une forme de traitement proche de l’objet. Mais si vous vous conformez déjà à ces quelques prescriptions vos projets seront à terme plus simples à maintenir et plus faciles à piloter lors des montées en charge. Car un SGBDR est un outil bien sympathique : il permet de réduire les coûts de développement de manière drastique, et se trouve aussi à l’aise dans l’embarqué ou le mono utilisateur, que dans la plateforme à haute volumétrie. Mais gare à celui qui fera l’impasse qualitative en pensant que son application n’aura jamais au grand jamais une forte volumétrie à traiter : le jour où un tel challenge lui sera proposé il sera piégé par sa propre insouciance !

-------- 

Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ 

Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server 

www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation 

* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *