ECONOMISER LA MACHINE, C'EST EN FAIT POUVOIR LUI EN DEMANDER PLUS

Un h�bergeur Fran�ais bon march�, tr�s populaire chez les informaticiens et proche d'un fournisseur d'acc�s gratuit � Internet, nous l'a cruellement rappel� � la fin de l'ann�e derni�re. Fournissant des h�bergements mutualis�s (plusieurs sites par machines), il s'est retrouv� avec certains sites reposant int�gralement sur des requ�tes SQL lourdes voir inutiles, fausses, et surtout lanc�es � tors et � travers. Certains de ces sites sont devenus un peu connus, ont commenc� � faire de l'audience, et ont finalement faillis faire chavirer l'ensemble de la plate-forme d'h�bergement. Les bases de donn�es, surcharg�es, refusaient souvent de se lancer et de r�pondre aux requ�tes, et renvoyaient r�guli�rement des messages d'erreurs � tous les utilisateurs. Aux heures de pointes, les sites n'�taient parfois m�me plus consultables. Inutile de dire que la cr�dibilit� d'un site internet affichant des erreurs d'acc�s � la base SQL est s�rieusement entam�e, et que de nombreuses personnes ont du reprendre leur d�veloppement. Pour que le site Internet tienne la charge quand il commence � �tre fr�quent�, il vaut mieux qu'il repose sur des fondations solides tant mat�rielles que logicielles. Quant � cet h�bergeur, il a du refondre son architecture mat�rielle, mais a aussi perdu une grande part de sa cr�dibilit� et de sa client�le...

MODELISONS UN CATALOGUE : REVISIONS DE LA TECHNIQUE...

Syst�me d'Information (SI) : Arborescence en Arbre...

Un garagiste expose sur le Web ses mod�les, afin de pr�senter ses nouveaut�es et surtout son stock en temps r�el (disponibilit� de ses mod�les). Il utilise ce que l'on app�le un catalogue, sans caddy ni paiement en ligne (ce sont des voitures...), mais avec navigation par cat�gorie/sous-cat�gorie, affichage de la fiche-produit de la voiture, et possibilit� de recherche par marque de v�hicule. Il limite sa fiche produit au nom, au prix, et � la disponibilit� de la voiture (on simplifie...). Ce qui donne :

Un Produit poss�de 1 ou plusieurs Cr�ateurs (marques, fabricants). Un Cr�ateur fabrique 0 ou plusieurs Produits. Ces Produits appartiennent chacun � une et une seule Cat�gorie. Chaque Cat�gorie contient 0 ou plusieurs Produits. Enfin, une Cat�gorie peut avoir 0 ou plusieurs sous-Cat�gories, chaque Cat�gorie ayant 0 ou une Cat�gorie parente.

D'o� le MCD :



Qui entra�ne le MLDR :

CREATEUR (ID_CREATEUR, NOM_CREATEUR)
FABRIQUE (ID_CREATEUR, ID_PRODUIT)
PRODUIT (ID_PRODUIT, #ID_CATEGORIE, NOM_PRODUIT, PRIX_PRODUIT, DISPONIBLE)
CATEGORIE (ID_CATEGORIE, #ID_PAR_CATEGORIE, NOM_CATEGORIE)


Qui g�n�re le MPD :

POUR SE PROTEGER, IL FAUT BIEN COLMATER LES JOINTURES !!!

Prenons une requ�te d'extraction toute simple, et regardons ce qu'il se passe : On recherche les mod�les disponibles de V�hicules de marques "Peugeot" de type "D�capotable", et leur prix. L'identifiant ID_CREATEUR de "Peugeot" est ici "4", et l'identifiant ID_CATEGORIE de "D�capotable" est "10".

SELECT *
FROM PRODUIT, FABRIQUE
WHERE PRODUIT.ID_CATEGORIE='10'
AND FABRIQUE.ID_CREATEUR='4'
AND PRODUIT.ID_PRODUIT=FABRIQUE.ID_PRODUIT

Regardons maintenant ce que fait le moteur de la base :

1�) Celui interpr�te la requ�te ligne par ligne. Pour commencer, il met dans un tableaux tous les �l�ments de PRODUIT et de FABRIQUE, en faisant ce que l'on appelle un produit cart�sien : c'est � dire que pour chaque enregistrement de la premi�re table rencontr�e, ici PRODUIT, il mettra en face tous les enregistrements de la seconde table rencontr�s un par un, et ce pour chaque ligne. Si PRODUIT contient 250 enregistrements, et FABRIQUE 110, cette table temporaire et interm�diaire comprendra 250*110=27500 lignes.

PRODUIT						FABRIQUE
id_produit	id_categorie	nom_produit	prix_produit	disponible		id_createur	id_produit
1	3	103SP	55 000	Y		4	2
1	3	103SP	55 000	Y		4	11
...	...	...	...	...		8	15
...	...	...	...	...		...	...
1	3	103SP	55 000	Y		33	18
2	10	205 Blue	83 000	Y		4	12
2	10	205 Blue	83 000	Y		4	11
...	...	...	...	...		8	2
...	...	...	...	...		...	...
2	10	205 Blue	83 000	Y		33	18
etc...	etc...	etc...	etc...	etc...		etc...	etc...

2�) Puis, il supprime de cette m�me table temporaire les valeurs de id_cat�gories diff�rentes de '10'.

PRODUIT						FABRIQUE
id_produit	id_categorie	nom_produit	prix_produit	disponible		id_createur	id_produit
2	10	205 Blue	83 000	Y		4	2
2	10	205 Blue	83 000	Y		4	11
...	...	...	...	...		8	15
...	...	...	...	...		...	...
2	10	205 Blue	83 000	Y		33	18
etc...	etc...	etc...	etc...	etc...		etc...	etc...

3�) Ensuite, il supprime de cette m�me table temporaire les valeurs de id_createur diff�rentes de '4'.

PRODUIT						FABRIQUE
id_produit	id_categorie	nom_produit	prix_produit	disponible		id_createur	id_produit
2	10	205 Blue	83 000	Y		4	2
2	10	205 Blue	83 000	Y		4	11
...	...	...	...	...		...	...
etc...	etc...	etc...	etc...	etc...		etc...	etc...

4�) Enfin, il effectue la jointure demand�e, et ne garde que les lignes dont PRODUIT.ID_PRODUIT=FABRIQUE.ID_PRODUIT .

PRODUIT						FABRIQUE
id_produit	id_categorie	nom_produit	prix_produit	disponible		id_createur	id_produit
2	10	205 Blue	83 000	Y		4	2
etc...	etc...	etc...	etc...	etc...		etc...	etc...

5�) Eventuellement, si il le lui avait �t� demand�, c'est � ce stade qu'il aurait interpr�t� les commandes des instructions GROUP BY, puis HAVING et enfin ORDER BY. Toutefois, il est int�ressant de constater que toutes les colonnes sont pr�sentes dans le tableaux, et que l'on a pass� en m�moire � peu pr�s 100 fois l'int�gralit� de la quantit� de donn�es contenues dans ces seules tables.

Imaginez un peu si le garagiste avait eu 100 000 Voitures r�parties dans 250 Cat�gories, avec � peu pr�s 970 marques diff�rentes ?

"F� PAS GACHER", COMME DIRAIT L'AUTRE...

Reprenons la m�me requ�te, mais formul�e un poil diff�remment :

SELECT PRODUIT.NOM_PRODUIT, PRODUIT.PRIX_PRODUIT
FROM PRODUIT, FABRIQUE
WHERE PRODUIT.ID_PRODUIT=FABRIQUE.ID_PRODUIT
AND PRODUIT.ID_CATEGORIE='10'
AND FABRIQUE.ID_CREATEUR='4'

Regardons ce que fais le moteur de la base :

1�) Tout d'abord, il r�cup�re dans un tableaux les �l�ments de PRODUIT demand�s et ceux n�cessaires pour la requ�te, fais pareil pour FABRIQUE, et la jointure �tant sp�cifi�e en premier, il ne prend que les lignes dont PRODUIT.ID_PRODUIT=FABRIQUE.ID_PRODUIT .

PRODUIT					FABRIQUE
id_produit	id_categorie	nom_produit	prix_produit		id_createur	id_produit
2	10	205 Blue	83 000		4	2
3	10	M�gane	83 000		8	3
5	15	4L ME	83 000		4	5
...	...	...	...		...	...
etc...	etc...	etc...	etc...		etc...	etc...

2�) Il enl�ve les lignes dont id_categorie n'est pas �gal � '10'

PRODUIT					FABRIQUE
id_produit	id_categorie	nom_produit	prix_produit		id_createur	id_produit
2	10	205 Blue	83 000		4	2
3	10	M�gane	83 000		8	3
...	...	...	...		...	...
etc...	etc...	etc...	etc...		etc...	etc...

3�) Puis celles o� id_createur n'est pas �gal � '4'

PRODUIT					FABRIQUE
id_produit	id_categorie	nom_produit	prix_produit		id_createur	id_produit
2	10	205 Blue	83 000		4	2
...	...	...	...		...	...
etc...	etc...	etc...	etc...		etc...	etc...

4�) Enfin, il ne garde que les colonnes demand�es dans la requ�te, c'est � dire le nom, et le prix.

205 Blue	83 000
...	...
etc...	etc...

Si j'avais su, par exp�rience ou connaissance du contexte, que la clause FABRIQUE.ID_CREATEUR='4' �tait plus r�ductrice en terme d'�l�ments que la clause PRODUIT.ID_CATEGORIE='10' , je l'aurais alors mise avant celle ci, afin de r�duire le plus possible le nombre d'�l�ments stock�s en m�moire, et donc les op�rations n�cessaires pour les tris et traitements ult�rieurs.

Il est int�ressant de remarquer que pour un obtenir un r�sultat similaire, on a beaucoup moins tir� sur la machine, qui pourra donc accomplir cette requ�te un plus grand nombre de fois simultan�ment, et donc accueillir un plus grand nombre de visiteur sans souffrir...

QUI VEUT ALLER LOIN, MENAGE SA MONTURE

Sans tomber non plus dans l'int�grisme inutile du coupeur de cheveux en 4, il est tout de m�me tr�s clair que la simple formulation de la requ�te SQL est lourde de cons�quence sur les ressources et le temps machine n�cessaire pour sa simple ex�cution. On peut ainsi citer quelques pr�cautions simples qui all�geront simplement la charge reposant sur le serveur :

• Mettre les jointures en premier :
  Si n tables, alors (n-1) jointures.



• Placer les comparaisons les plus restrictives le plus t�t possible :
  cela fera toujours autant de lignes qui ne seront plus en m�moire, et que l'ordinateur n'aura plus � traiter dans le reste de sa requ�te.



• EVITER ABSOLUMENT "SELECT * FROM ..." :
  Ne demandez que les colonnes n�cessaires, c'est toujours �a de moins � garder en tableaux apr�s la requ�te, et donc cela �conomise la m�moire. De plus, si un jour vous d�placez votre code, et que deux colonnes se trouvent invers�es dans la nouvelle base, cela n'aura aucune cons�quence pour votre d�veloppement.



• Comparer des colonnes de m�me type :
  Un CHAR(150) est consid�r� du m�me type qu'un VARCHAR(150), mais diff�rent d'un CHAR(152) ou d'un VARCHAR(148). Cela oblige le moteur de base de donn�e � effectuer des conversions internes.



• Formuler les clauses de comparaison le plus pr�cisement possible :
  En particulier, �viter de mettre des % partout dans les clauses LIKE, c'est tr�s lourd � traiter...



• Mettre les identifiants en INT, et en AUTOINCREMENT :
  L'avantage principal de l'autoincrement est que pour chaque cr�ation d'enregistrement ne comprenant pas d'office son identifiant, le moteur se charge lui-m�me de lui en attribuer un [du type max(id)+1], ce qui �vite des manipulations suppl�mentaires.



• Utilisez des INDEX :
  Mais l'explication, l�, ce sera pour la prochaine fois....