SQL Server vu par Christian Robert

SQL Server : Vues indexées y compris sur Edition Express ou l'utilisation du NOEXPAND

Les vues indexées sont supportées dans toutes les éditions de SQL Server, et celèrent depuis SQL Server 2000, version dans laquelle elles ont fait leur apparition. Ceci est valable y compris sur SQL Server Express. La particularité de l’édition Enterprise en matière de vues indexées est la manière de les supporter.

Petit rappel sur ce qu’est une vue indexée. La vue indexée a pour effet de matérialiser les données ; le résultat de la requête (contenu dans la vue) est stocké dans un index unique de type CLUSTERED (ordonné). Ce résultat est maintenu à jour en fonction des modifications effectuées sur les tables de base de la vue. À tout moment le contenu de l’index de la vue est le reflet exact du résultat de la requête.

La vue indexée est très efficace sur des requêtes utilisant beaucoup de jointures et/ou réalisant des agrégations sur les données. En contrepartie, elle est très couteuse sur les tables sources de la vue, si celles-ci sont fréquemment mise à jour. En effet la mise à jour de la vue se fait de manière synchrone. Tant que tous les index de toutes les vues indexées qui référencent la table ne sont pas à jour, la requête de modification de la table de base ne sera pas terminée.

Le très gros avantage de l'utilisation des vues indexées sous SQL Server Edition Enterprise (Et Datacenter et Developer), c'est que leur utilisation est totalement automatique. Vous n'avez rien d'autre à faire que créer la vue indexée. L'optimiseur de requêtes utilisera la vue indexée dès qu’il trouvera qu'une requête à une définition proche de celle de la vue. Ceci est très pratique si vous ne pouvez pas toucher au code des requêtes. Attention tout de même, l’optimiseur de requêtes ne considérera l’usage d’une vue indexée que si la requête est considérée comme couteuse.

Voici un exemple où la vue indexée se révèle intéressante. Cet exemple est réalisé sur la base de données AdventureWorks. 

CREATE VIEW v1
     WITH SCHEMABINDING
AS
     SELECT PRD.Name, CST.AccountNumber,
     SUM(SOD.OrderQty) AS SommeQte, COUNT_BIG(*) AS Compte
     FROM Sales.SalesOrderHeader AS SOH
         JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID
         JOIN Production.Product AS PRD ON SOD.ProductID = PRD.ProductID
         JOIN Sales.Customer AS CST ON SOD.CustomerID = CST.CustomerID AND SOH.CustomerID = CST.CustomerID
     GROUP BY PRD.Name, CST.AccountNumber
GO 

Dans l'exemple je cumule les jointures et les agrégats qui sont le domaine de prédilection des vues indexées.

Notez les éléments surlignés dans le code, ceux-ci sont obligatoires dans le cadre de la création d’une vue indexée.
Pour que cette vue devienne une vue indexée, j'ai juste à ajouter un index unique de type CLUSTERED.

CREATE UNIQUE CLUSTERED INDEX IX_v1 ON v1(Name, AccountNumber)

Les données sont maintenant matérialisées et stockées dans l’index.

Sur SQL Server édition Enterprise, vous n'avez rien de plus à faire, dès qu'une requête se rapproche suffisamment de la définition de la vue le moteur va automatiquement utiliser les données de la vue indexée.

-- Dans l'édition Entreprise la vue indexée est utilisée automatiquement
SELECT PRD.Name, CST.AccountNumber, SUM(SOD.OrderQty)
FROM Sales.SalesOrderHeader AS SOH
     JOIN Sales.SalesOrderDetail AS SOD ON SOH.SalesOrderID = SOD.SalesOrderID
     JOIN Production.Product AS PRD ON SOD.ProductID = PRD.ProductID
     JOIN Sales.Customer AS CST ON SOD.CustomerID = CST.CustomerID AND SOH.CustomerID = CST.CustomerID
GROUP BY PRD.Name, CST.AccountNumber

Par contre sur les autres éditions il faut requêter directement la vue et forcer l'utilisation de l'index par l'utilisation du Hint NOEXPAND.

-- Execute la requête de la vue
-- On remplace le nom de la vue par sa définition
-- Comme on le fait pour une vue normale
SELECT * FROM dbo.v1 

-- Renvoie les données de la vue indexée
-- Dans ce cas on demande explicitement à lire les données
-- de l'index créée précédement
SELECT * FROM dbo.v1 WITH(NOEXPAND) 

À titre de comparaison, la 1re requête qui traite environ 20 Mo de données, met 13 secondes à s'exécuter (dont 8 secondes par le CPU). La deuxième (qui utilise donc les données matérialisées) traite seulement 6 Mo de données en 4 secondes (dont 1 seconde par le CPU). Le gain est très net et il est même possible d’avoir des ratios plus importants.

Petit conseil, avant de vous lancer dans la création de vues indexées, lisez l'aide en ligne à ce sujet. Il y a en effet énormément de restrictions quant aux requêtes qu'il est possible d'utiliser dans une vue indexée.

Et tous ces tests ont été réalisés avec SQL Server Express :o)
Comme quoi ça marche bien !

Bons tests et bons développements...