Partager via

Optimisation de la table NewOrg

La table HumanResources.NewOrd que vous avez créée dans la tâche Remplissage d'une table avec des données hiérarchiques existantes contient toutes les informations relatives aux employés et représente la structure hiérarchique à l'aide d'un type de données hierarchyid. Cette tâche ajoute de nouveaux index pour prendre en charge les recherches sur la colonne hierarchyid.

Index cluster

La colonne hierarchyid (OrgNode) est la clé primaire de la table NewOrg. Lorsque la table a été créée, elle contenait un index cluster nommé PK_NewOrg_OrgNode pour appliquer l'unicité de la colonne OrgNode. Cet index cluster prend également en charge une recherche à profondeur prioritaire de la table.

Index non cluster

Cette étape crée deux index non cluster pour prendre en charge des recherches typiques.

Pour indexer la table NewOrg en vue d'effectuer recherches efficaces

  1. Pour faciliter les requêtes au même niveau de la hiérarchie, utilisez la méthode GetLevel pour créer une colonne calculée qui contient le niveau dans la hiérarchie. Créez ensuite un index composite sur le niveau et Hierarchyid. Exécutez le code suivant pour créer la colonne calculée et l'index à largeur prioritaire :

    ALTER TABLE HumanResources.NewOrg 
ADD H_level AS OrgNode.GetLevel() ;
CREATE UNIQUE INDEX EmpBFInd 
   ON HumanResources.NewOrg(H_level, OrgNode) ;
GO

  2. Créez un index unique sur la colonne EmployeeID. Il s'agit de la recherche singleton classique d'un seul employé par numéro EmployeeID. Exécutez le code suivant pour créer un index sur EmployeeID :

    CREATE UNIQUE INDEX EmpIDs_unq ON HumanResources.NewOrg(EmployeeID) ;
GO

  3. Exécutez le code suivant pour récupérer des données de la table dans l'ordre de chacun des trois index :

    SELECT OrgNode.ToString() AS LogicalNode,
OrgNode, H_Level, EmployeeID, LoginID, Title
FROM HumanResources.NewOrg 
ORDER BY OrgNode;

SELECT OrgNode.ToString() AS LogicalNode,
OrgNode, H_Level, EmployeeID, LoginID, Title
FROM HumanResources.NewOrg 
ORDER BY H_Level, OrgNode;

SELECT OrgNode.ToString() AS LogicalNode,
OrgNode, H_Level, EmployeeID, LoginID, Title
FROM HumanResources.NewOrg 
ORDER BY EmployeeID;
GO

  4. Comparez les jeux de résultats pour voir comment l'ordre est stocké dans chaque type d'index. Seules les quatre premières lignes de chaque de sortie suivent.

    Voici l'ensemble des résultats.

    Index à profondeur prioritaire : les enregistrements d'employés sont stockés à proximité de leur responsable.

    LogicalNode OrgNode    H_Level EmployeeID LoginID                  Title
/           0x         0       109        adventure-works\ken0     Chief Executive Officer
/1/         0x58       1        12        adventure-works\terri0   Vice President of Engineering
/1/1/       0x5AC0     2         3        adventure-works\roberto0 Engineering Manager
/1/1/1/     0x5AD6     3         4        adventure-works\rob0     Senior Tool Designer

    Index à largeur prioritaire : les niveaux de gestion sont stockés ensemble.

    LogicalNode OrgNode    H_Level EmployeeID LoginID                  Title
/           0x         0       109        adventure-works\ken0     Chief Executive Officer
/1/         0x58       1        12        adventure-works\terri0   Vice President of Engineering
/2/         0x68       1         6        adventure-works\david0   Marketing Manager
/3/         0x78       1        42        adventure-works\jean0    Information Services Manager
...

    Index affichant EmployeeID en priorité : les lignes sont stockées dans l'ordre des EmployeeID.

    LogicalNode OrgNode    H_Level EmployeeID LoginID                  Title
/6/4/13/12/ 0x961B7640 4       1          adventure-works\guy1     Production Technician - WC60
/2/5/       0x6C60     2       2          adventure-works\kevin0   Marketing Assistant
/1/1/       0x5AC0     2       3          adventure-works\roberto0 Engineering Manager
/1/1/1/     0x5AD6     3       4          adventure-works\rob0     Senior Tool Designer

[!REMARQUE]

Pour les diagrammes qui affichent la différence entre un index à profondeur prioritaire et un index à largeur prioritaire, consultez Utilisation des types de données hierarchyid (moteur de base de données).

Pour supprimer les colonnes inutiles

  1. La colonne ManagerID représente la relation employé/responsable, qui est maintenant représentée par la colonne OrgNode. Si d'autres applications n'ont pas besoin de la colonne ManagerID, envisagez sa suppression à l'aide de l'instruction suivante :

    ALTER TABLE HumanResources.NewOrg DROP COLUMN ManagerID ;
GO

  2. La colonne EmployeeID est également redondante. La colonne OrgNode identifie chaque employé de façon unique. Si d'autres applications n'ont pas besoin de la colonne EmployeeID, envisagez la suppression de l'index puis de la colonne en utilisant le code suivant :

    DROP INDEX EmpIDs_unq ON HumanResources.NewOrg ;
ALTER TABLE HumanResources.NewOrg DROP COLUMN EmployeeID ;
GO

Pour remplacer la table d'origine par la nouvelle table

  1. Si votre table d'origine contenait des index ou contraintes supplémentaires, ajoutez-les à la table NewOrg.

  2. Remplacez l'ancienne table EmployeeDemo par la nouvelle table. Exécutez le code suivant pour supprimer l'ancienne table, puis renommez la nouvelle table avec l'ancien nom :

    DROP TABLE HumanResources.EmployeeDemo ;
GO
sp_rename 'HumanResources.NewOrg', EmployeeDemo ;
GO

  3. Exécutez le code suivant pour examiner la table finale :

    SELECT * FROM HumanResources.EmployeeDemo ;
  • Last updated on