279
8.3 Optimisation du code SQL
En SQL Server 2008, vous disposez du type de données HierarchyID, qui est un
type complexe implémenté en classe .NET. Il permet de stocker une information de
hiérarchie, et de retrouver ces informations à travers de méthodes. Malgré le fait que
ce soit un type .NET, il est plus rapide que l’utilisation de CTE. De plus, on peut
indexer ses propriétés si nécessaire, en passant par une colonne calculée. Pour vous
donner une idée de l’utilisation de ce type, voici un exemple de requête, qui fait à
peu près la même chose que la CTE précédente :
SELECT o1.EmployeeName, o1.EmployeeID.GetLevel() as level,
o2.EmployeeName as boss
FROM HumanResources.Organization o1
JOIN HumanResources.Organization o2
ON o2.EmployeeID = o1.EmployeeID.GetAncestor(1)
WHERE hierarchyid::GetRoot().IsDescendantOf(o1.EmployeeId)= 1 1
Vous trouvez des exemples d’utilisation dans les BOL 2008, sous l’entrée
« Working with hierarchyid Data ».
Une dernière méthode, très efficace, implique une structuration de la table spécifique, en représentation intervallaire. Vous en trouvez la description dans cet article
de Frédéric Brouard : http://sqlpro.developpez.com/cours/arborescence/.
Mises à jour
Un UPDATE s’effectue lui aussi ligne par ligne, même si l’instruction est ensembliste.
Comme vous pouvez affecter une valeur à une variable dans un UPDATE, et mélanger
cette affectation avec une mise à jour de colonne, vous pouvez générer une forme
détournée de boucle, qui vous évitera un curseur. Exemple très simple :
CREATE TABLE dbo.testloop (
id int NOT NULL IDENTITY (1,1) PRIMARY KEY CLUSTERED,
nombre int NULL,
groupe int NOT NULL
)
GO
INSERT INTO dbo.testloop (groupe)
SELECT TOP (10) 1 FROM sys.system_objects UNION ALL
SELECT TOP (20) 2 FROM sys.system_objects UNION ALL
SELECT TOP (15) 3 FROM sys.system_objects
GO
DECLARE @i int
SET @i = 0
UPDATE dbo.testloop
SET @i = @i + 1,
nombre = @i
GO
1. La méthode IsDescendantOf s’appelait IsDescendant en version beta (CTP) 6. Elle a été
renommée en IsDescendantOf.
8.3 Optimisation du code SQL
En SQL Server 2008, vous disposez du type de données HierarchyID, qui est un
type complexe implémenté en classe .NET. Il permet de stocker une information de
hiérarchie, et de retrouver ces informations à travers de méthodes. Malgré le fait que
ce soit un type .NET, il est plus rapide que l’utilisation de CTE. De plus, on peut
indexer ses propriétés si nécessaire, en passant par une colonne calculée. Pour vous
donner une idée de l’utilisation de ce type, voici un exemple de requête, qui fait à
peu près la même chose que la CTE précédente :
SELECT o1.EmployeeName, o1.EmployeeID.GetLevel() as level,
o2.EmployeeName as boss
FROM HumanResources.Organization o1
JOIN HumanResources.Organization o2
ON o2.EmployeeID = o1.EmployeeID.GetAncestor(1)
WHERE hierarchyid::GetRoot().IsDescendantOf(o1.EmployeeId)= 1 1
Vous trouvez des exemples d’utilisation dans les BOL 2008, sous l’entrée
« Working with hierarchyid Data ».
Une dernière méthode, très efficace, implique une structuration de la table spécifique, en représentation intervallaire. Vous en trouvez la description dans cet article
de Frédéric Brouard : http://sqlpro.developpez.com/cours/arborescence/.
Mises à jour
Un UPDATE s’effectue lui aussi ligne par ligne, même si l’instruction est ensembliste.
Comme vous pouvez affecter une valeur à une variable dans un UPDATE, et mélanger
cette affectation avec une mise à jour de colonne, vous pouvez générer une forme
détournée de boucle, qui vous évitera un curseur. Exemple très simple :
CREATE TABLE dbo.testloop (
id int NOT NULL IDENTITY (1,1) PRIMARY KEY CLUSTERED,
nombre int NULL,
groupe int NOT NULL
)
GO
INSERT INTO dbo.testloop (groupe)
SELECT TOP (10) 1 FROM sys.system_objects UNION ALL
SELECT TOP (20) 2 FROM sys.system_objects UNION ALL
SELECT TOP (15) 3 FROM sys.system_objects
GO
DECLARE @i int
SET @i = 0
UPDATE dbo.testloop
SET @i = @i + 1,
nombre = @i
GO
1. La méthode IsDescendantOf s’appelait IsDescendant en version beta (CTP) 6. Elle a été
renommée en IsDescendantOf.