Gestion des modifications de données

Cet exemple de gestion des hiérarchies avec SQL Server utilise un déclencheur sur insertion pour mettre à jour les colonnes lvl et hierarchy lorsqu'un utilisateur de la base de données ajoute un employé à la table Employees. La solution doit désormais inclure un déclencheur sur mise à jour modifiant les

informations lvl
et hiérarchie lorsqu’un employé change de poste dans l’entreprise. Le listing
3 présente un déclencheur sur mise à jour permettant de mettre à jour les colonnes
lvl et hierarchy.

De nombreux environnements informatiques ne permettent pas les mises à jour de
la clé primaire. Aussi, le déclencheur commence-t-il par vérifier si la colonne
empid a été modifiée. Si c’est le cas, le déclencheur annule l’opération de mise
à jour et s’arrête. Ensuite, le déclencheur vérifie la colonne mgrid de l’employé
pour voir si l’employé dépend désormais d’un autre manager. Le changement de statut
de l’employé peut modifier son salaire, mais le déclencheur ne doit modifier les
colonnes lvl et hierarchy que si l’employé change de supérieur hiérarchique. Après
toutes les vérifications de cohérence, le déclencheur passe à l’essence même de
son algorithme : l’instruction de mise à jour.
Supposons qu’Andrew dépende désormais de Janet et non plus de Nancy.

UPDATE employees
SET mgrid = 3
WHERE empid = 2

La nouvelle position d’Andrew affecte également tous ses employés à tous les niveaux
(dans le cas présent, Steven et Michael). Les niveaux de Steven et Michael dans
la hiérarchie changent en même temps que la chaîne hiérarchique, comme cela est
indiqué dans le tableau 1.
On peut généraliser les effets du changement de poste d’un employé sur son enregistrement
et sur toutes les personnes sous ses ordres, et concevoir une formule définissant
les nouvelles valeurs lvl et hiérarchie. Par exemple,

· Supposons que old_emp_lvl = le niveau de l’employé avant la mise à jour
· Supposons que new_mgr_lvl = le niveau du nouveau manager de l’employé
· Supposons que new_mgr_hier = la hiérarchie du nouveau manager de l’employé
· Supposons que mov_empid = l’ID employé de l’employé
· Supposons que right_hier = la partie droite de la hiérarchie de chaque employé
affecté, commençant par son ID employé

Ainsi, la formule définissant les nouvelles valeurs lvl et hiérarchie se présente
sous la forme suivante :

lvl = lvl – old_emp_lvl + { new_mgr_lvl + 1 | 0 } hierarchy = { new_mgr_hier |
‘.’ } + mov_empid + ‘.’ + right_hier

En s’inspirant de la formule de la colonne lvl, on peut calculer le nouveau niveau
de Michael de la manière suivante :

Niveau actuel de Michael – Ancien niveau d’Andrew + niveau de Janet + 1 = 2 –
1 + 1 + 1 = 3

Notez que si l’employé est promu au plus haut niveau de la hiérarchie, on n’a
pas besoin de récupérer le niveau de son manager car cet employé n’a pas de supérieur
hiérarchique. Par conséquent, son niveau est 0.

D’après la formule de calcul de la hiérarchie, la nouvelle hiérarchie de Michael
est :

Hiérarchie de Janet + ID employé d’Andrew + ‘.’ + partie droite de la hiérarchie
de Michael commençant par son ID employé = ‘.1.3.’ + ‘2’ + ‘.’ + ‘6.’ = ‘.1.3.2.6.’

Encore une fois, si l’employé est promu au plus haut niveau de la hiérarchie,
on n’a pas besoin de récupérer le niveau de son manager, car cet employé n’a pas
de supérieur hiérarchique.
L’instruction de mise à jour du déclencheur met à jour la table Employees. Mais,
pour obtenir les informations concernant l’employé et le manager, nécessaires
pour filtrer les lignes devant être mises à jour, puis effectuer la mise à jour,
l’instruction doit assembler deux tables. La figure 3 illustre ce qui se passe
de manière logique dans le déclencheur sur mise à jour.
Dans un premier temps, l’instruction de mise à jour fait une jointure entre la
table Employees et la table insérée contenant uniquement la ligne mise à jour
d’Andrew. Le lien n’est pas évident car il est basé sur l’opérateur LIKE et non
sur l’égalité :

FROM Employees AS E JOIN INSERTED AS I
ON E.hierarchy LIKE I.hierarchy + ‘%’

La jointure sélectionne les lignes de la table Employees selon qu’elles commencent
par la même hiérarchie que la table insérée (l’ancienne hiérarchie d’Andrew).
Le résultat de cette jointure est constitué de la ligne d’Andrew et des lignes
de ses subordonnés à tous les niveaux. L’instruction de mise à jour effectue ensuite
une jointure externe gauche des résultats de l’étape 1 et de la table Employees
(qui effectue une jointure entre les lignes d’Andrew et de ses subalternes et
la ligne du manager d’Andrew). Le déclencheur sur mise à jour utilise ici le lien
externe gauche pour la même raison que le déclencheur sur insertion précédemment
: pour éviter qu’une ligne soit perdue si on met l’ID du responsable de l’employé
à NULL.

Mise en oeuvre pratique
Maintenant que nous avons vu comment fonctionnait cette approche de la création
et de la maintenance de données hiérarchiques, examinons sa mise en oeuvre
dans la pratique, et notamment comment les index peuvent améliorer les performances
des requêtes et comment contrôler l’ordre dans lequel les requêtes hiérarchiques
renvoient les données.

Des index pour de meilleures performances
Dans l’exemple de l’article principal, toute les lignes de la table tiennent
dans une page unique, et donc les index n’apporteraient pas de bénéfices
importants en termes de performances. Mais dans la réalité, les tables seraient
bien plus importantes, et les index seraient susceptibles d’apporter des
améliorations de performances considérables à certaines requêtes.
Un index non-clusterisé sur la colonne hierarchy est l’un des index les
plus importants pour améliorer les performances de requêtes ne renvoyant
que quelques lignes :

CREATE UNIQUE NONCLUSTERED INDEX idx_nc_hierarchy ON Employees(hierarchy)

Notez que l’on ne peut placer un index que sur une colonne de 900 octets
ou moins. Cette restriction limite le nombre maximal de niveaux hiérarchiques
que la solution pourra prendre en compte. Mais on peut toujours supporter
plus de 100 niveaux, ce qui est largement suffisant pour la plupart des
structures hiérarchiques.
Un autre problème avec les index non-clusterisés sur la colonne hierarchy
est que le processeur de requêtes n’utilise pas toujours l’index. Imaginons
par exemple que nous ayons de nombreuses lignes dans la table Employees
et que l’on passe la requête suivante, qui ne devrait en principe ne renvoyer
que quelques lignes :

SELECT *
FROM Employees
WHERE hierarchy LIKE (SELECT hierarchy
FROM Employees
WHERE empid = 7) + ‘%’
ORDER BY hierarchy

Le processeur de requêtes va probablement décider de ne pas utiliser l’index
sur la colonne hierarchy. Lorsqu’on utilise l’opérateur LIKE pour filtrer
les lignes, le processeur de requêtes utilise en général l’index sur hierarchy
quand on compare une certaine colonne à une constante sous la forme ‘constant%’.
On pourrait donc reformuler cette requête comme ceci :

SELECT *
FROM Employees
WHERE hierarchy LIKE ‘.1.3.7.%’
ORDER BY hierarchy

Mais on ne veut toujours pas contrôler la valeur de hierarchy manuellement
à chaque fois que l’on a besoin d’interroger la table Employees. Une solution
consiste à encapsuler la requête au sein d’une procédure cataloguée. Il
suffit de construire l’instruction SQL à l’intérieur d’une variable de type
caractère, puis d’exécuter cette instruction dynamiquement :

DECLARE @cmd AS varchar(8000)
SET @cmd =
‘SELECT * FROM Employees WHERE hierarchy LIKE »’ +
(SELECT hierarchy + ‘%’
FROM Employees
WHERE empid = 7) +
»’ORDER BY hierarchy’

EXECUTE(@cmd)

Cette technique a le même effet que d’exécuter la requête avec une constante,
et le processeur de requêtes va certainement utiliser l’index sur la colonne
hierarchy pour améliorer les performances de la requête.
Pourquoi ne pas tout simplement écrire une procédure cataloguée acceptant
l’ID de l’employé en paramètre, définissant une variable, et plaçant la
valeur de hierarchy dans la variable en fonction de l’ID d’employé considérée
? On pourrait utiliser la variable plus tard dans la requête. Le problème
de cette technique, c’est que l’optimiseur de requêtes optimise un batch
ou une procédure cataloguée, et donc y compris la déclaration de la variable
et son alimentation et la requête qui utilise cette variable, le tout dans
une seule unité. Du coup, la valeur de la variable est inconnue au moment
où l’optimiseur de requêtes optimise la requête.
Umachandar Jayachandran, SQL Server MVP, m’a suggéré la solution de jointure
suivante, qui utilise souvent l’index sur hierarchy:

SELECT *
FROM Employees AS E JOIN (SELECT hierarchy
FROM Employees
WHERE empid = 7) as M
ON E.hierarchy LIKE M.Hierarchy + ‘%’

Mais le meilleur conseil que je puisse vous donner pour tirer les meilleures
performances de vos requêtes est, comme toujours d’ailleurs, de les tester,
retester, retester, et tester encore un peu.

Les données hiérarchiques dans l’ordre
Notez que dans ces résultats de requêtes hiérarchiques, l’ordre des données
d’un même niveau hiérarchique peut parfois être étonnant. Comme la valeur
de la colonne hierarchy est une chaîne de caractères, si on a deux enfants
avec des ID d’employé de 100 et de 20, par exemple, l’employé ayant l’ID
à 20 apparaîtra après celui qui a son ID à 100. En général, l’ordre entre
les enfants d’un même niveau n’est pas important, du moment que la solution
maintient la hiérarchie. Mais si cet ordre devient important, on peut modifier
la colonne hierarchy pour qu’elle contienne la chaîne des ID d’employés
au format de longueur fixe, dans lequel la longueur est le nombre maximal
de caractères possibles. On pourrait par exemple représenter l’ID d’employé
« 20 » sous la forme ‘0000020’, et celui de « 100 » sous la forme ‘0000100’.
Cette technique utilise néanmoins beaucoup d’espace disque, et donc, si
l’on ne se soucie pas de l’ordre d’arrivée dans un même niveau hiérarchique,
il vaut mieux conserver le format original de la colonne hierarchy.