Discussion :

[kheraud]

Bonjour,

Je dispose d'une table contenant des enregistrements qui possèdent une hiérarchie entre eux :

T_ITEM

ID_ITEM NOM_ITEM ID_DEPEND
1_______gente________3
2_______voiture_______2
3_______roue_________2
4_______volant________2

ID_DEPEND précise de quel sur-item l'item concerné est le fils.

Je souhaite récupérer par une requête adaptée le résultat sous forme hiérarchique sans faire 15 requêtes :

2_______voiture_______2
3_______roue_________2
1_______gente________3
4_______volant________2
Le cas pratique concerne des resultsets allant jusqu'à 250 lignes. D'autre part, la profondeur de hiérarchie ne dépasse pas 3.

Je pensais jouer cela avec des ORDER BY mais cela ne paraît pas vraiment faisable.
> Je suppose que ce type de problème (hierarchisation des résultats) est commun et qu'il y a du réutilisable.
---> Connaitriez vous des mots-clés SQL me permettant de faire ce genre de traitement ?
---> Dois-je faire cette récursivité derrière (en PHP en l'occurence) ? Auriez-vous des algorythmes optimisés dans ce but ?

Merci par avance pour votre aide !

K.

[SQLpro]

Pour ordonner une arborescence de niveau n il faut :

• si votre SGBDR supporte les requêtes récursive, une seule requête récursive
• dans le cas contraire, n requêtes en jointures externes sauf à implémenter le mode intervallaire

Lisez les articles que j'ai écrit à ce sujet, et notamment :
http://sqlpro.developpez.com/cours/arborescence/

A +

[kheraud]

La méthode intervallaire est vraiment pas mal.

Un simple ORDER BY NFM_BG permet d'avoir tout dans l'ordre.

Je pars là dessus. J'aurais probablement des questions sur des détails d'optimisation. J'ouvrirai un autre fil.

Merci pour le coup de main et pour l'article très clair.

K.

[SQLpro]

En complément, j'ai réalisé des entrées de mon blog à ce sujet :
http://blog.developpez.com/sqlpro/p7...dure-de-derec/
http://blog.developpez.com/sqlpro/p7...dure-de-depla/
http://blog.developpez.com/sqlpro/p8...allaire-proce/

A +

[kheraud]

Bonjour !

Nuit aidant ou doutes, j'aurais quelques précisions à apporter:

Parmi les fonctionnalités qui seront couramment utilisées, certaines me semblent gourmandes en requêtes :
-----> Transformer une feuille en branche voir en branche de branche (par déplacement d'une feuille ou d'une branche)
-----> Changer le père d'une branche
-----> Changer le père d'une feuille

Je précise que le "tronc" de ma table comprendra USER * ~[100-1000] enregistrements. Sachant que je souhaite avoir beaucoup d'utilisateurs , on peut facilement (j'espère) atteindre le million d'enregistrements.

Les méthodes proposées pour supprimer ou insérer des branches/feuilles nécessitent de faire une modification de deux valeurs dans TOUS les enregistrements de la table (dans le pire des cas). Statistiquement, la modification est sur la moitié des enregistrements.

Je me demande dans quelle mesure, il serait plus optimisé d'avoir des enregistrements donnant chacun l'ID de leur père (une modification entrainerait alors une modifications sur 1 enregistrement de la table) et un traitement de la hiérarchie assuré derrière par le serveur d'application au cas par cas pour chaque user.
A priori cela me semble plus adapté car l'itération sera sur [100-1000] enregistrements à hiérarchiser plutôt que ~1M à mettre à jour.

Je ne doute pas que tu sois plus expérimenté dans le domaine que moi. Quel est ton avis sur la question ?

Merci pour ta précieuse aide !

K.

[SQLpro]

D'abord, ôtez de votre vocabulaire le mot enregistrement qui n'a rien à voir avec les SGBDR. Nous ne sommes pas dans le Cobol.
A lire : http://sqlpro.developpez.com/cours/sqlaz/erreurs/#L2

Les modifications induites par le déplacement des sous arbres concernent en effet de nombreuses lignes, mais portent sur des données de taille fixe. L'effort à faire est donc extrêmement faible (8 octets par ligne).

Le nombre de modifications pour déplacement d'arbre est à considérer. En effet, si ce nombre est bien plus élevé que les lectures alors vous avez raison de préférez le modèle par adjacence. Mais c'est généralement rarement le cas car on fait plus de lecture que de mises à jour, sauf cas particulier.

Ensuite vous me parlez de 100 000 Lignes par arbre. Ceci ne veut rien dire, car une base de données travaille par volume de données (mesurées en pages) et non en nombre de ligne. Si vos lignes font 100 octets en moyenne, la mise à jour de 100 000 lignes sous SQL Server consiste à récrire 1250 pages, ce qui est important, mais pas énorme.
Le problème sera donc un problème de qualité de du modèle et d'indexation (en particulier étudier le FILL FACTOR adéquat et réindex régulièrement). Mais tout cela fait partie d'un job ordinaire de DBA.

Enfin il existe d'autres possibilités :
1) de n'utiliser le mode intervallaire qu'en dehors de la table fonctionnelle (donc une table avec Clef + BG, BD et niveau, Clef faisant référence à la clef de la table fonctionnelle), le tout mis à jour par trigger. Dans ce cas, la longueur des lignes est de 16 octets et la mise à jour de 100000 ligne ne fait plus que 200 pages ce qui est faible
2) d'utiliser des bornes décimales, qui n'oblige pas à recalculer toutes les bornes à chaque UPDATE puisqu'on intercale les valeurs. Les algorithmes étant plus délicat à écrire et surtout il faut penser à prévoir un recalibrage de l'arbre régulièrement à l'aide d'une procédure planifiée

Sachez que j'ai mis en œuvre des arbres intervallaires contenant des millions de lignes sans que cela soit un problème.

A +

[kheraud]

De par votre réponse je me rend compte des km qui nous séparent.

Je vais donc mettre en place la méthode basique nécessitant un calcul après requêtes du serveur d'application. Une optimisation pourra venir par la suite si l'utilisation de l'application le justifie.

Merci encore pour votre aide