Partager via

  • Article

Conception des vues indexées

Les vues sont également appelées tables virtuelles, car l'ensemble de résultats qu'elles retournent se présente globalement sous la même forme qu'une table composée de colonnes et de lignes et elles peuvent être référencées tout comme les tables dans les instructions SQL. L'ensemble de résultats d'une vue standard n'est pas définitivement stocké dans la base de données. Chaque fois qu'une requête fait référence à une vue standard, SQL Server remplace la définition de la vue dans la requête en interne jusqu'à ce qu'une requête modifiée soit formée, ne faisant référence qu'à des tables de base. Ensuite, elle exécute aussi la requête résultante. Pour plus d'informations, consultez Résolution de vues.

Dans le cas d'une vue standard, le temps système requis pour l'élaboration dynamique de l'ensemble de résultats de chaque requête qui fait référence à une vue peut être conséquent si cette dernière implique un traitement complexe de nombreuses lignes, comme lors de l'agrégation d'importantes quantités de données ou de la jointure de nombreuses lignes. Si une telle vue est fréquemment référencée dans les requêtes, vous pouvez améliorer les performances en créant un index cluster unique sur cette vue. Lorsqu'un index cluster unique est créé sur une vue, l'ensemble de résultats est stocké dans la base de données au même titre qu'une table contenant un index cluster.

Un autre avantage lié à la création d'un index sur une vue réside dans le fait que l'optimiseur utilise d'abord cet index dans les requêtes qui ne nomment pas directement la vue dans la clause FROM. Les requêtes existantes n'ont pas besoin d'être réécrites pour tirer parti de l'amélioration des performances obtenue lors de l'extraction des données à partir de la vue indexée. Pour plus d'informations, consultez Résolution d'index sur les vues.

Ainsi, les modifications apportées aux données dans les tables de base sont dynamiquement répercutées sur les données stockées dans la vue indexée. Grâce à la contrainte d'unicité de l'index cluster de la vue, SQL Server trouve plus efficacement les lignes de l'index affectées par une modification de données quelconque.

L'optimiseur de requête peut mieux tirer profit des vues indexées lors du traitement des requêtes que les versions antérieures, lorsque la définition de la requête et celle de la vue contiennent toutes les deux les éléments correspondants suivants :

  • Expressions scalaires. Par exemple, l'optimiseur de requête peut associer la requête suivante avec une expression scalaire de son prédicat :

    SELECT ColA, ColB FROM TableT WHERE ColC * (ColD + 10) > 50

    à un index créé sur cette vue :

    CREATE VIEW V1 WITH SCHEMABINDING AS
SELECT ColA, ColB, ColC * (ColD + 10) AS ExpCol
FROM dbo.TableT

    Les expressions scalaires qui incluent des fonctions définies par l'utilisateur peuvent aussi être associées de manière similaire.

  • Fonctions d'agrégation scalaires. Par exemple, la requête suivante qui contient une fonction d'agrégation scalaire dans sa liste SELECT :

    SELECT COUNT_BIG (*) FROM dbo.TableT

    peut être associée à un index créé sur cette vue :

    CREATE VIEW V2 WITH SCHEMABINDING AS
SELECT COUNT_BIG (*) AS Cnt 
FROM dbo.TableT

L'optimiseur de requête considérera également ceci lors du choix d'un plan de requêtes :

  • Si un intervalle de valeurs définies dans un prédicat de requête tombe dans un intervalle défini dans une vue indexée. Prenons l'exemple d'un index créé sur la vue suivante :

    CREATE VIEW V1 WITH SCHEMABINDING AS
SELECT ColA, ColB, ColC FROM dbo.TableT
WHERE ColA > 1 and ColA < 10

    Puis la requête suivante :

    SELECT ColB, ColC FROM dbo.TableT
WHERE ColA > 3 and ColA < 7

    L'optimiseur de requête associerait cette requête à la vue V1, parce que l'intervalle entre 3 et 7 défini dans la requête tombe dans l'intervalle entre 1 et 10 défini dans la vue indexée.

  • La valeur d'une expression définie dans une requête équivaut à ce qui est défini dans une vue indexée. SQL Server tente d'associer des expressions en s'appuyant sur leurs références de colonnes, littéraux, opérateurs logiques AND, OR, NOT, BETWEEN et IN et opérateurs de comparaison =, <>, >, <, >= et <=. Les opérateurs arithmétiques tels que + et % et les paramètres ne sont pas pris en considération.

    Par exemple, l'optimiseur de requête associerait la requête suivante :

    SELECT ColA, ColB from dbo.TableT
WHERE ColA < ColB

    à un index créé sur cette vue :

    CREATE VIEW V1 WITH SCHEMABINDING AS
SELECT ColA, ColB FROM dbo.TableT
WHERE ColB > ColA

Souvenez-vous que dans le cas utilisant tous les index, SQL Server choisit d'utiliser une vue indexée de son plan de requête uniquement si l'optimiseur de requête détermine si cela est utile.

Les vues indexées peuvent être créées dans n'importe quelle version de SQL Server 2008. Dans SQL Server 2008 Enterprise, l'optimiseur de requête prend automatiquement en considération la vue indexée. Pour utiliser une vue indexée dans toutes les autres versions, il faut utiliser l'indicateur de table NOEXPAND.

Conseils pour créer une vue indexée

Les vues indexées sont particulièrement efficaces lorsque les données sous-jacentes sont rarement mises à jour. Le coût de maintenance d'une vue indexée peut être plus élevé que celui de la maintenance d'un index de table. Si les données sous-jacentes sont fréquemment mises à jour, le coût de maintenance des données d'une vue indexée annule les gains de performance liés à l'utilisation de la vue. Si les données sous-jacentes sont mises à jour périodiquement dans des traitements, mais traitées principalement comme étant en lecture seule entre les mises à jour, pensez à supprimer toute vue indexée avant la mise à jour, puis à les recréer par la suite. Cela peut améliorer les performances des mises à jour.

Les vues indexées améliorent les performances des types de requêtes suivants :

  • Jointures et agrégations traitant de nombreuses lignes.

  • Opérations de jointure et d'agrégation fréquemment réalisées par de nombreuses requêtes.

    Par exemple, dans une base de données de traitement des transactions en ligne (OLTP) recensant des stocks, de nombreuses requêtes sont susceptibles de rejoindre les tables ProductMaster, ProductVendor et VendorMaster. Même si chaque requête réalisant cette jointure n'est pas amenée à traiter beaucoup de lignes, le traitement des jointures global de centaines de milliers de requêtes de ce type peut être représentatif. Étant donné que ces relations ne sont pas susceptibles d'être mises à jour fréquemment, les performances globales du système peuvent être améliorées en définissant une vue indexée qui stocke les résultats conjoints.

  • Charges de travail d'aide à la prise de décision.

    Les systèmes d'analyse se caractérisent par le stockage de données de synthèse agrégées peu fréquemment mises à jour. L'agrégation supplémentaire des données et la jointure de nombreuses lignes caractérisent également de nombreuses requêtes d'aide à la prise de décision. De plus, les systèmes d'aide à la prise de décision contiennent parfois des tables volumineuses, avec de nombreuses colonnes ou des colonnes qui sont trop grandes, ou les deux. Les requêtes qui font référence à un sous-ensemble étroit de ces colonnes peuvent tirer profit d'une vue indexée ne comprenant que les colonnes de la requête ou un sur-ensemble étroit de ces colonnes. La création de vues indexées étroites contenant un sous-ensemble des colonnes d'une seule table est connue sous le nom de partitionnement vertical, parce qu'elle partage les tables verticalement. Imaginons par exemple la table suivante et une vue indexée :

    CREATE TABLE wide_tbl(a int PRIMARY KEY, b int, ..., z int)
CREATE VIEW v_abc WITH SCHEMABINDING AS
SELECT a, b, c
FROM dbo.wide_tbl
WHERE a BETWEEN 0 AND 1000
CREATE UNIQUE CLUSTERED INDEX i_abc ON v_abc(a)

    Il est possible de répondre à la requête suivante simplement en utilisant v_abc :

    SELECT b, count_big(*), SUM(c)
FROM wide_tbl 
WHERE a BETWEEN 0 AND 1000
GROUP BY b

    La vue v_abc occupe beaucoup moins de pages que la table wide_tbl. Par conséquent, il est préférable pour l'optimiseur de la choisir comme moyen d'accès pour résoudre la requête précédente.

    Si vous souhaitez fractionner verticalement toute une table, plutôt que juste un sous-ensemble, nous vous conseillons d'utiliser un index non-cluster sur la table avec une clause INCLUDE pour inclure uniquement les colonnes qui vous intéressent, au lieu d'une vue indexée. Pour plus d'informations, consultez CREATE INDEX (Transact-SQL).

Généralement, les vues indexées n'améliorent pas les performances des types de requêtes suivants :

  • Les systèmes OLTP avec de nombreuses opérations d'écriture.

  • Les bases de données faisant l'objet de nombreuses mises à jour.

  • Les requêtes n'impliquant ni agrégations ni jointures.

  • Les agrégations de données impliquant un degré élevé de cardinalité pour la clé GROUP BY. Lorsque le degré de cardinalité est élevé, la clé contient de nombreuses valeurs différentes. Une clé unique présente le degré de cardinalité le plus élevé possible car toutes ses valeurs sont différentes. Les vues indexées améliorent les performances en réduisant le nombre de lignes auxquelles une requête doit accéder. Si l'ensemble de résultats de la vue présente pratiquement autant de lignes que la table de base, l'utilisation de la vue offre un faible intérêt en termes de performances. Prenons comme exemple l'application de la requête suivante à une table contenant 1 000 lignes :

    SELECT PriKey, SUM(SalesCol)
FROM ExampleTable
GROUP BY PriKey

    Si la cardinalité de la clé de la table est de 100, une vue indexée construite à partir des résultats de cette requête ne contiendrait que 100 lignes. Les requêtes utilisant la vue auraient en moyenne besoin d'un dixième des lectures normalement requises par rapport à la table de base. Si la clé est unique, la cardinalité est de 1000 et l'ensemble de résultats de la vue retourne 1000 lignes. En termes de performances, une requête ne tire aucun avantage de l'utilisation de cette vue indexée au lieu d'une lecture directe de la table de base ExampleTable.

  • Jointures d'extension. Ce sont des vues dont les ensembles de résultats sont plus volumineux que les données d'origine des tables de base.

Combinaison de vues indexées et de requêtes

Bien que les limites associées aux types de vues pouvant être indexées puissent vous empêcher de concevoir une vue qui résolve un problème complet, vous pouvez élaborer plusieurs vues indexées de taille réduite afin d'accélérer certaines phases du processus.

Observez les exemples suivants :

  • Une requête fréquemment exécutée agrège les données d'une base de données, puis d'autres données d'une autre base, et joint ensuite les résultats. Étant donné qu'une vue indexée ne peut pas référencer des tables appartenant à plusieurs bases de données, vous ne pouvez pas concevoir une seule vue pour réaliser l'ensemble du processus. Cependant, vous pouvez créer une vue indexée dans chaque base de données, afin d'effectuer l'agrégation correspondante. Si l'optimiseur peut établir une concordance entre les vues indexées et les requêtes existantes, le traitement des agrégations sera pour le moins accéléré, sans qu'il soit nécessaire de réécrire les requêtes existantes. Même si le traitement de la jointure n'est pas plus rapide, la requête globale l'est, car elle utilise les agrégations stockées dans les vues indexées.

  • Une requête fréquemment exécutée agrège des données de plusieurs tables puis utilise une opération UNION pour combiner les résultats. L'UNION n'est pas autorisée dans une vue indexée. Vous pouvez là aussi concevoir des vues pour la réalisation de chaque opération d'agrégation. L'optimiseur peut ensuite sélectionner les vues indexées pour accélérer les requêtes sans qu'il soit nécessaire de les réécrire. Même si le traitement d'UNION n'est pas plus performant, chaque processus d'agrégation l'est.

Concevez des vues indexées pouvant satisfaire plusieurs opérations. Étant donné que l'optimiseur peut utiliser une vue indexée même si elle n'est pas spécifiée dans la clause FROM, la vue peut accélérer le traitement de nombreuses requêtes si elle est correctement élaborée.

Par exemple, prenons la création d'un index sur la vue suivante :

CREATE VIEW ExampleView WITH SCHEMABINDING
AS
SELECT GroupKey, SUM(Colx) AS SumColx, COUNT_BIG(Colx) AS CountColx
FROM MyTable
GROUP BY GroupKey

Non seulement cette vue peut satisfaire les requêtes qui référencent directement ses colonnes, mais elle satisfait également les requêtes portant sur la table de base et contenant des expressions telles que SUM(Colx), COUNT_BIG(Colx), COUNT(Colx), et AVG(Colx). Toutes ces requêtes s'avèrent plus rapides, car elles doivent uniquement extraire une faible quantité de lignes de la vue au lieu de lire toutes les lignes des tables de base.

De la même façon, une vue indexée qui agrège les données et les regroupe par jour peut être utilisée pour répondre aux besoins des requêtes qui produisent des agrégations sur plusieurs plages de plus de 1 jour, par exemple 7, 30 ou 90 jours.