Les vues indexées de SQL Server 2000 pour une amélioration des performances

SQL Server 2000

Gail Erickson, auteur
Lubor Kollar, collaborateur
Jason Ward, collaborateur
Microsoft Corporation

Septembre 2000

Résumé : Ce document décrit une nouvelle fonctionnalité de SQL Server 2000 Enterprise Edition, les vues indexées. Outre une explication de ces vues, vous y trouverez des scénarios illustrant les améliorations qu'elles peuvent apporter aux performances.

Table des matières

Présentation d'une vue indexée
   Les vues indexées et l'amélioration des performances
Quand utiliser les vues indexées ?
   Les vues indexées et l'optimiseur de requête
Remarques relatives à la conception
   Conseils de conception
   Utilisation de l'Assistant Paramétrage d'index
   Maintenance des vues indexées
Création de vues indexées
   Des résultats cohérents avec les options SET
   Utilisation de fonctions déterministiques
   Autres impératifs
Exemples
Informations complémentaires



Présentation d'une vue indexée

Depuis de nombreuses années, Microsoft® SQL Server™ prend en charge la création de tables virtuelles, appelées vues. A l'origine, ces vues avaient deux objectifs principaux :

  1. Pour des raisons de sécurité, limiter l'accès des utilisateurs à un sous-ensemble particulier de données d'une ou de plusieurs tables de la base.
  2. Pour que l'affichage logique des données stockées dans les tables d'une base soit mieux adapté aux utilisateurs, permettre aux développeurs de le personnaliser.
Avec SQL Server 2000, les vues SQL Server se dotent de fonctionnalités plus puissantes qui améliorent les performances du système. Il est possible de créer sur une vue un index unique en cluster ou non qui améliore l'accès aux données même si les requêtes sont très complexes. Dans SQL Server 2000, une vue à laquelle est associé un index en cluster unique est appelée vue indexée. Remarque   La fonction Vues indexées n'existe que dans SQL Server 2000 Enterprise Edition et SQL Server 2000 Developer Edition.

Dans un contexte de système de gestion de bases de données (SGBD), une vue est une description de données (métadonnées). Généralement, pour définir ces métadonnées lors de la création d'une vue, une instruction SELECT est encapsulée pour déterminer l'ensemble des résultats à représenter sous forme de table virtuelle. Lorsqu'une vue est référencée dans la clause FROM d'une autre requête, ces métadonnées sont extraites du catalogue système et développée à la place de la référence de la vue. Ensuite, l'optimiseur de requête compile un seul et même plan d'exécution de la requête d'exécution.

Dans le cas d'une vue non indexée, cette dernière est matérialisée au moment de l'exécution. Tous les calculs, tels que les jointures ou agrégations, sont réalisés lors de l'exécution de chaque requête référençant la vue. (Il n'est pas toujours nécessaire de matérialiser complètement la vue. La requête peut contenir d'autres prédicats, jointures ou agrégations qui peuvent être appliqués aux tables et aux vues référencées dans la vue). Si on crée un index en cluster unique sur la vue, l'ensemble des résultats est immédiatement matérialisé et conservé en mémoire physique dans la base de données, évitant ainsi d'utiliser un temps précieux au moment de l'exécution.

Il y a deux façons d'utiliser la vue indexée dans l'exécution d'une requête. Dans le premier cas, la requête référence directement la vue indexée ou, mieux, l'optimiseur de requête sélectionne la vue s'il détermine qu'elle peut remplacer tout ou partie de la requête et considère qu'il s'agit du plan le plus économique. Dans le second, la vue est utilisée à la place des tables sous-jacentes et de leurs index habituels. Il est inutile de référencer la vue dans la requête pour que l'optimiseur de requête l'utilise à l'exécution. Cela permet aux applications existantes de bénéficier sans modification des dernières vues indexées.

Les vues indexées et l'amélioration des performances

L'amélioration des performances des requêtes à l'aide d'index n'est pas un concept nouveau. Cependant, les vues indexées apportent d'autres avantages impossibles à obtenir avec des index standard. En voici quelques-uns :

  • Calcul anticipé et stockage des agrégations dans l'index, ce qui réduit des calculs coûteux lors de l'exécution de la requête.
     
  • Jointure anticipée des tables et stockage des données obtenues.
     
  • Stockage de combinaisons de jointures et d'agrégations.

Le graphique ci-dessous illustre les gains de performances réalisés lorsque l'optimiseur de requête utilise une vue indexée. Les requêtes représentées se différenciaient par leur degré de complexité (par exemple, le nombre de calculs d'agrégation, le nombre de tables utilisées et le nombre de prédicats) et incluaient des tables de plusieurs millions de lignes tirées d'un environnement de production réel.

indexedviews1.gif
Figure 1. Gains de performances réalisés lorsque l'optimiseur de requêtes utilise une vue indexée

Utilisation d'index secondaires sur des vues

Des index secondaires non en cluster améliorent également les performances des requêtes lorsqu'ils sont placés sur des vues. Similaires aux index secondaires placés sur des tables, ils permettent à l'optimiseur de requête de disposer de plus d'options lors de la compilation. Par exemple, si la requête inclut des colonnes non couvertes par l'index en cluster, l'optimiseur peut choisir un ou plusieurs index secondaires du plan et éviter un balayage complet et long de la vue indexée ou des tables de la base.

L'ajout d'index au schéma augmente le temps système utilisé par la base de données à cause de la maintenance constante que demandent les index. Il est donc important de trouver le bon équilibre entre les index et le temps système requis pour leur maintenance.



Quand utiliser les vues indexées ?

Avant d'implémenter des vues indexées, analysez la charge de travail de votre base de données. Faites appel à votre connaissance des requêtes et des différents outils (tels que SQL Profiler) pour identifier les cas dans lesquels des vues indexées présentent un intérêt. Elles se justifient particulièrement lorsque les requêtes comportent des agrégations et des jointures fréquentes.

Les vues indexées ne conviennent pas toujours à toutes les requêtes. Tout comme les index ordinaires, si elles ne sont pas utilisées, l'avantage est nul. Dans ce cas, non seulement il n'y a aucune amélioration des performances, mais des coûts supplémentaires sont engendrés par l'utilisation d'espace disque, la maintenance et l'optimisation. Cependant, les vues indexées peuvent apporter des améliorations significatives dans l'accès aux données (par ordre d'importance). En effet, comme l'optimiseur de requête utilise les résultats précalculés stockés dans la vue indexée, l'exécution des requêtes est beaucoup moins coûteuse.

L'optimiseur de requête ne fait appel aux vues indexées que dans le cas de requêtes coûteuses. Cela évite que des tentatives de comparaison entre plusieurs vues indexées lors de l'optimisation de la requête n'engendrent un coût supérieur aux économies réalisées par l'utilisation de ces vues. Les vues indexées sont rarement utilisées dans des requêtes d'un coût inférieur à 1.

Voici des applications pouvant bénéficier de l'implémentation de vues indexées :

  • Charges de travail relatives à l'aide à la prise de décision.
     
  • Mini-entrepôts.
     
  • Magasins et sources de traitement analytique en ligne (OLAP).
     
  • Charges de travail dues à l'exploration de données.

Si on considère le type de requête et le modèle, les applications bénéficiaires sont celles qui contiennent :

  •  
  • Des jointures et agrégations de tables de grande taille.
     
  • Des modèles répétitifs de requêtes.
     
  • Des agrégations répétitives d'ensembles de colonnes identiques ou superposés.
     
  • Des jointures répétitives des mêmes tables avec les mêmes clés.
     
  • Des combinaisons des conditions ci-dessus.
A l'opposé, dans le cas de systèmes de traitement transactionnel en ligne (OLTP) où les écritures sont fréquentes, ou de bases de données souvent mises à jour, les vues indexées ne sont pas toujours intéressantes car il faut mettre à jour à la fois la vue et les tables sous-jacentes.

Les vues indexées et l'optimiseur de requête

L'optimiseur de requête de SQL Server détermine automatiquement si une vue indexée peut être utilisée pour exécuter une requête donnée. Il est inutile de référencer directement la vue dans la requête pour que l'optimiseur l'utilise dans son plan d'exécution. Par conséquent, les vues indexées peuvent bénéficier aux applications existantes sans que ces dernières soient modifiées. Il suffit de créer les vues indexées.

Remarques relatives à l'optimiseur

L'optimiseur de requête étudie plusieurs conditions pour déterminer le bien-fondé d'une vue indexée sur tout ou partie d'une requête. Ces conditions, qui correspondent à une clause FROM de la requête, sont les suivantes :

  • Les tables de la clause FROM de la requête doivent être un sur-ensemble des tables de la clause FROM de la vue indexée.
     
  • Les conditions de jointure de la requête doivent être un sur-ensemble des conditions de jointure de la vue.
     
  • Les colonnes d'agrégat de la requête doivent être un sous-ensemble des colonnes d'agrégat de la vue.
     
  • Toutes les expressions de la liste de sélection de la requête doivent pouvoir être dérivées de la liste de sélection de la vue ou des tables non incluses dans la définition de la vue.
     
  • Les prédicats des conditions de recherche de la requête doivent être un sur-ensemble des prédicats des conditions de recherche de la définition de la vue. Dans le prédicat de recherche de la vue et dans le prédicat de recherche de la requête, les conjoints doivent figurer sous la même forme.
     
  • Toutes les colonnes des prédicats des conditions de recherche de la requête appartenant à des tables de la définition de la vue doivent apparaître dans un ou plusieurs des éléments suivants :
    • Le même prédicat, dans la définition de la vue.
       
    • Une liste GROUP BY.
       
    • La liste de sélection de la vue s'il n'y a pas de clause GROUP BY.

Si la requête contient plusieurs clauses FROM (sous-requêtes, tables dérivées, UNION), l'optimiseur peut sélectionner plusieurs vues indexées.

Remarque   Dans certaines situations exceptionnelles, l'optimiseur peut réduire deux clauses FROM en une (transformation d'une sous-requête ou d'une table dérivée en jointure). Dans ce cas, la vue indexée englobe plusieurs clauses FROM de la requête initiale.

Vous trouverez à la fin de ce document des exemples de requêtes illustrant ces conditions. Il est recommandé d'autoriser l'optimiseur de requête à déterminer les index à utiliser dans son plan d'exécution.

Utilisation de l'option NOEXPAND

L'option NOEXPAND force l'optimiseur de requête à traiter la vue comme une table ordinaire avec index en cluster. Dans ce cas, la vue indexée doit être directement référencée dans la clause FROM. Par exemple :

SELECT Colonne1, Colonne2, ... FROM Table1, Vue1 WITH (NOEXPAND)WHERE ...
Utilisation des options EXPAND VIEWS

L'utilisateur peut aussi demander explicitement à ne pas prendre les vues indexées en considération en utilisant l'option EXPAND VIEWS à la fin de la requête. Par exemple :

SELECT Colonne1, Colonne2, ... FROM Table1, Vue1 WHERE ...OPTION (EXPAND VIEWS)

Si cette option est active, l'optimiseur de requête ignore tous les index de vues lorsqu'il évalue la méthode la plus économique pour couvrir les colonnes référencées dans la requête.



Remarques relatives à la conception

Dans un système de bases de données, identifier un jeu approprié d'index peut être un exercice complexe. De nombreuses possibilités sont déjà à prendre compte lors de la conception d'index ordinaires et le fait d'ajouter des vues indexées au schéma augmente considérablement la complexité de la conception et les résultats potentiels. Par exemple, il est possible d'utiliser des vues indexées :

  • Sur un sous-ensemble quelconque de tables référencé dans la requête.
     
  • Sur un sous-ensemble quelconque des conditions de la requête relatives à ce sous-ensemble de tables.
     
  • Lors du regroupement de colonnes.
     
  • Avec des fonctions d'agrégation, telles que SUM.

Pensez à concevoir les index des tables et les vues indexées simultanément pour obtenir les meilleurs résultats de chaque construction. Comme l'index et la vue indexée ont tous deux leur utilité pour une requête donnée, les concevoir séparément risque de provoquer des redondances dans les recommandations et par conséquent d'augmenter l'utilisation de la mémoire et le temps système de maintenance. Lors du réglage de la conception physique d'une base de données, il faut trouver un moyen terme entre les impératifs de performances malgré la diversité des requêtes d'une part et les mises à jour que le système de bases de données doit prendre en charge d'autre part. Par conséquent, le défi consiste à trouver la conception physique la mieux adaptée aux vues indexées. C'est pourquoi il est conseillé d'utiliser l'assistant Paramétrage d'index chaque fois que c'est possible.

L'optimisation des requêtes peut devenir une opération très coûteuse si, pour une même requête, l'optimiseur étudie de nombreuses vues indexées. Un optimiseur de requêtes peut étudier toutes les vues indexées définies sur un sous-ensemble quelconque de tables. Il analyse chaque vue et, avant de la rejeter, étudie le bien-fondé de la substitution. Ce qui risque de prendre du temps, en particulier s'il y a des centaines de vues indexées pour une requête donnée.

Une vue doit respecter plusieurs impératifs si vous voulez lui associer un index en cluster unique. Lors de la phase de conception, tenez compte des impératifs suivants :

  • Placez la vue et toutes les tables qu'elle référence dans la même base de données et attribuez-les au même propriétaire.
     
  • Il n'est pas indispensable que la vue indexée contienne toutes les tables référencées dans la requête que l'optimiseur utilisera.
     
  • Créez un index en cluster unique avant de pouvoir créer d'autres index sur la vue.
     
  • Définissez correctement certaines options SET (traitées plus loin dans ce document) lorsque les tables, la vue et l'index de la base sont créés et chaque fois que des données des tables et de la vue de la base sont modifiées. De plus, l'optimiseur de requête ne prend pas la vue indexée en considération si ces options SET ne sont pas correctes.
     
  • Créez la vue en utilisant une liaison de schéma. Créez toutes les fonctions définies par l'utilisateur auxquelles elle fait référence avec l'option SCHEMABINDING.
     
  • Prévoyez la place que prendront sur le disque les données définies par la vue indexée.

Conseils de conception

Lors de la conception de vues indexées, suivez ces quelques conseils :

  • Plusieurs requêtes ou plusieurs opérations doivent pouvoir utiliser les vues indexées que vous concevez.
    Par exemple, une vue indexée contenant les calculs SUM et COUNT_BIG d'une colonne peut être utilisée par des requêtes contenant les fonctions SUM, COUNT, COUNT_BIG ou AVG. Les requêtes s'exécutent plus rapidement car il suffit d'extraire un petit nombre de lignes de la vue au lieu d'extraire toutes les lignes des tables de la base et une partie des calculs nécessaires à l'exécution de la fonction AVG a déjà été réalisée.
  • Veillez à garder un index compact.
    Plus vous réduisez le nombre de colonnes et d'octets, plus l'optimiseur trouve facilement les lignes concernées. A l'inverse, si la clé d'index en cluster est longue, les index secondaires non en cluster définis sur la vue sont eux aussi plus longs, leurs entrées contenant la clé de gestion des clusters en plus des colonnes définies par l'index.
  • Pensez à la taille que fera la vue indexée obtenue.
    Dans le cas d'une simple agrégation, le gain de performances risque d'être nul si la vue indexée et la table d'origine font la même taille.
  • Plusieurs vues indexées de plus petite taille peuvent accélérer certaines parties du processus.
    Il n'est pas toujours possible de concevoir une seule et même vue indexée pour l'ensemble de la requête. Dans ce cas, pensez à créer plusieurs vues indexées, chacune exécutant une partie de la requête.
    Étudions quelques exemples :
    • Une requête, fréquemment exécutée, regroupe des données d'une base par agrégation, puis celles d'une autre, et rassemble les résultats par jointure. Une vue indexée ne pouvant pas référencer des tables de plusieurs bases de données, elle ne peut pas assurer l'ensemble du processus à elle seule. Cependant, vous pouvez, dans chaque base de données, créer une vue indexée qui effectue une agrégation des données de la base. Si l'optimiseur peut effectuer une correspondance entre les vues indexées et les requêtes existantes, au moins l'agrégation est plus rapide, sans qu'il soit nécessaire de recoder des requêtes existantes. Bien que la jointure ne soit pas plus rapide, l'exécution globale de la requête l'est, puisqu'elle utilise les agrégations stockées dans les vues indexées.
       
    • Une requête, fréquemment exécutée, regroupe des données de plusieurs tables par agrégation, puis rassemble les résultats avec UNION. Cependant, UNION n'est pas autorisé dans une vue indexée. Vous pouvez concevoir des vues d'exécution de chaque agrégation. Ensuite, l'optimiseur sélectionne les vues indexées pour accélérer l'exécution des requêtes sans qu'il soit nécessaire de recoder ces dernières. L'exécution de UNION reste inchangée, mais vous gagnez du temps sur les différentes agrégations.

Utilisation de l'Assistant Paramétrage d'index

L'Assistant Paramétrage d'index recommande l'utilisation d'index mais aussi de vues indexées des tables d'une base. L'utilisation de l'Assistant aide l'administrateur à déterminer la combinaison d'index et de vues indexées la plus efficace selon l'ensemble de requêtes exécutées sur une base de données.

Comme l'Assistant Paramétrage d'index force l'application de toutes les options SET requises (pour assurer un ensemble de résultats correct), il crée les vues indexées. Cependant, si vos options ne sont pas correctement paramétrées, votre application risque de ne pas bénéficier des vues. Les insertions, mises à jour ou suppressions risquent d'échouer sur des tables utilisées dans les vues indexées.

Maintenance des vues indexées

SQL Server assure automatiquement la maintenance des vues indexées comme celle de tout autre index. Dans le cas d'index ordinaires, chacun est directement lié à une seule table. Lors des insertions, mises à jour et suppressions réalisées dans la table sous-jacente, l'index est modifié en conséquence, pour assurer la cohérence de ses valeurs avec les données de la table.

La maintenance des vues indexées est similaire. Cependant, si la vue référence plusieurs tables, la mise à jour de l'une d'entre elles peut nécessiter la mise à jour de la vue indexée. A la différence des index ordinaires, l'insertion d'une seule ligne dans une des tables participantes peut entraîner l'insertion de plusieurs lignes dans la vue indexée. Cela est également vrai des mises à jour et suppressions. Par conséquent, la maintenance d'une vue indexée peut coûter plus cher que celle d'un index.

Dans SQL Server 2000, certaines vues peuvent être mises à jour. Si c'est le cas, les tables sous-jacentes de la base sont modifiées directement dans la vue avec les instructions INSERT, UPDATE et DELETE. Créer un index sur une vue n'interdit pas la mise à jour. Pour plus d'informations sur les possibilités de mise à jour des vues, consultez Modifying Data Through a View dans SQL Server Books Online for SQL Server 2000.

Coûts liés à la maintenance

Étudiez les points suivants lorsque vous concevez des vues indexées :

  • La vue indexée nécessite de la mémoire supplémentaire dans la base de données. L'ensemble des résultats de la vue est physiquement conservé dans la base de données, comme le serait une table.
     
  • SQL Server assure une maintenance automatique des vues. Par conséquent, toute modification apportée à la table sous-jacente de la vue peut entraîner des modifications des index de la vue. Ce qui demande de consacrer davantage de temps à la maintenance.

Le bénéfice net des améliorations est la différence entre les économies réalisées par la vue lors de l'exécution d'une requête et le coût du stockage et de la maintenance de la vue.

Il est relativement facile de connaître la place de stockage approximative qu'utilise la vue. Évaluez l'instruction SELECT encapsulée par la définition de la vue avec l'outil Afficher le plan d'exécution estimé de l'analyseur de requêtes SQL. Cet outil donne le nombre approximatif de lignes renvoyées par la requête et la taille de la ligne. Une multiplication de ces deux valeurs permet de trouver la taille potentielle approximative de la vue. Cependant, ce résultat étant imprécis, la seule façon de déterminer la véritable taille de l'index est de le créer.

Si l'on considère les incidences de la maintenance automatique effectuée par SQL Server, la fonctionnalité Afficher le plan d'exécution estimé donne une indication de leur importance. Si une instruction de modification de la vue (UPDATE sur la vue, INSERT dans une base de la table) est évaluée avec l'Analyseur de requêtes SQL, SHOWPLAN tient compte de la maintenance requise. Multipliez le coût de la maintenance par le nombre d'occurrences de l'opération en environnement de production et vous obtiendrez le coût potentiel de maintenance de la vue.

Il est généralement conseillé d'effectuer les modifications ou les mises à jour de la vue ou des tables de la base par lots plutôt qu'individuellement, chaque fois que c'est possible. Vous réduirez ainsi le temps de maintenance de la vue.



Création de vues indexées

Pour réussir l'implémentation de la vue, il est important de suivre les étapes ci-dessous lors de sa création.

  1. Vérifiez les options SET de toutes les tables existantes à référencer dans la vue.
     
  2. Avant de créer de nouvelles tables et la vue, vérifiez que les options SET de votre session sont correctement paramétrées.
     
  3. Vérifiez que la définition de la vue est déterministique.
     
  4. Créez la vue avec l'option WITH SCHEMABINDING.
     
  5. Créez l'index unique en cluster sur la vue.

Des résultats cohérents avec les options SET

L'évaluation de la même expression risque de donner des résultats différents dans SQL Server si différentes options SET sont actives lors de l'exécution de la requête. Par exemple, si l'option SET CONCAT_NULL_YIELDS_NULL a la valeur ON, l'expression 'abc' + NULL renvoie la valeur NULL. Si elle a la valeur OFF, la même expression donne 'abc'. Dans les vues indexées, les différentes options SET doivent avoir des valeurs fixes pour que l'exactitude et la cohérence soient assurées.

Les options SET du tableau suivant doivent présenter les valeurs que montre la colonne Valeur requise chaque fois que ces conditions se produisent :

  • La vue indexée est créée.
     
  • Une opération INSERT, UPDATE ou DELETE est effectuée sur une des tables utilisées dans la vue indexée.
     
  • L'optimiseur de requête utilise la vue indexée pour produire le plan de requête.
Options SET Valeur requise Valeur du serveur par défaut Valeur OLE DB
et
ODBC
Valeur DB LIB
ANSI_NULLS ON OFF ON OFF
ANSI_PADDING ON ON ON OFF
ANSI_WARNING ON OFF ON OFF
ARITHABORT ON OFF OFF OFF
CONCAT_NULL_YIELDS_NULL ON OFF ON OFF
NUMERIC_ROUNDABORT OFF OFF OFF OFF
QUOTED_IDENTIFIER ON OFF ON OFF

Si vous connectez un serveur OLE DB ou ODBC, ne modifiez que la valeur du paramètre ARITHABORT. Toutes les valeurs DB LIB doivent être correctement définies soit au niveau du serveur, avec sp_configure, soit dans l'application, avec la commande SET. Pour plus d'informations sur les options SET, consultez Using Options in SQL Server dans SQL Server Books Online for SQL Server 2000.

Utilisation de fonctions déterministiques

La définition d'une vue indexée doit être déterministique. Une vue est déterministique si toutes les expressions de la liste de sélection et les clauses WHERE et GROUP BY le sont. Des expressions déterministiques renvoient toujours le même résultat chaque fois qu'elles sont évaluées avec un jeu spécifique de valeurs en entrée. Des fonctions ne peuvent participer à des expressions déterministiques que si elles le sont également. Par exemple, la fonction DATEADD est déterministique car elle renvoie toujours le même résultat lorsque ses trois paramètres sont toujours évalués avec le même ensemble donné de valeurs d'argument. GETDATE n'est pas déterministique car, bien qu'elle soit toujours appelée avec le même argument, la valeur qu'elle renvoie change chaque fois qu'elle est exécutée. Pour plus d' informations, consultez Deterministic and Nondeterministic Functions dans SQL Server Books Online for SQL Server 2000.

Même si une expression est déterministique, lorsqu'elle contient des expressions flottantes, le résultat exact dépend de l'architecture du processeur ou de la version du microcode. Pour que l'intégrité des données soit garantie dans SQL Server 2000, les colonnes de telles expressions ne doivent pas être utilisées comme clés de vues indexées. Des expressions déterministiques qui ne contiennent pas d'expressions flottantes sont des expressions précises. Seules des expressions déterministiques précises peuvent devenir des colonnes clés et des clauses WHERE ou GROUP BY de vues indexées.

Utilisez la fonction COLUMNPROPERTY et la propriété IsDeterministic pour déterminer si une colonne d'une vue est déterministique. Utilisez la fonction COLUMNPROPERTY et la propriété IsPrecise pour déterminer si une colonne d'une vue avec liaison de schéma est précise. COLUMNPROPERTY renvoie 1 si elle a la valeur TRUE, 0 si elle a la valeur FALSE et NULL si l'entrée est incorrecte (la colonne n'est pas déterministique). Par exemple :

SELECT COLUMNPROPERTY(Object_Id('Vdiscount1'),'SumDiscountPrice','IsPrecise')

renvoie 0 parce que la colonne SumDiscountPrice fait référence à la colonne flottante Discount de la table Order Details. Par contre, la colonne SumPrice de la même vue est à la fois déterministique et précise.

Remarque   Cette instruction SELECT est illustrée dans la vue 1 des exemples.

Autres impératifs

En plus de ceux évoqués dans les conseils de conception et dans les sections Utilisation d'options SET pour obtenir des résultats cohérents et Utilisation de fonctions déterministiques, pensez également à respecter les impératifs suivants.

Impératifs relatifs aux tables de la base
  • Les options SET de la base doivent être correctement définies au moment de la création de la table pour que cette dernière puisse être référencée par la vue avec liaison de schéma.
     
  • Dans la définition de la vue, les tables doivent être référencées par des noms en deux parties (propriétaire.nomtable).
Impératifs relatifs aux fonctions
  • Les fonctions définies par l'utilisateur doivent être créées avec l'option WITH SCHEMABINDING.
     
  • Les fonctions définies par l'utilisateur doivent être référencées par des noms en deux parties (propriétaire.fonction).
Impératifs relatifs aux vues
  • La vue doit être créée avec l'option WITH SCHEMABINDING.
     
  • La vue ne doit faire référence qu'à des tables appartenant à la même base de données. Elle ne peut pas faire référence à d'autres vues.
Règles de syntaxe

La syntaxe de définition d'une vue doit respecter certaines règles. La définition d'une vue ne doit pas contenir :

  • COUNT(*)
     
  • la fonction ROWSET
     
  • une table dérivée
     
  • une auto-jointure
     
  • DISTINCT
     
  • STDEV, VARIANCE, AVG
     
  • des colonnes Float*, text, ntext, image
     
  • une sous-requête
     
  • des prédicats texte intégral (CONTAIN, FREETEXT)
     
  • une fonction SUM sur une expression avec Mise à Null autorisée
     
  • MIN, MAX
     
  • TOP
     
  • une jointure externe (OUTER)
     
  • UNION
Remarque   La vue indexée peut contenir des colonnes flottantes mais ces dernières ne peuvent pas être utilisées dans la clé de l'index en cluster.
Limites de GROUP BY

Si GROUP BY n'est pas utilisé, aucune expression ne peut être utilisée dans la liste de sélection.

Si GROUP BY est présent, la définition de la vue :

  • Doit contenir COUNT_BIG(*).
     
  • Ne doit contenir ni HAVING, ni CUBE, ni ROLLUP.
Ces restrictions ne s'appliquent qu'à la définition de la vue indexée. Une requête peut utiliser une vue indexée dans son plan d'exécution même si elle ne respecte pas ces restrictions GROUP BY.
Impératifs relatifs aux index
  • Seul le propriétaire de la vue peut exécuter l'instruction CREATE INDEX.
     
  • Si la définition de la vue contient une clause GROUP BY, la clé de l'index unique en cluster ne peut faire référence qu'aux colonnes spécifiées dans la clause GROUP BY.


Exemples

Les exemples de cette section illustrent l'utilisation de vues indexées avec deux types importants de requêtes : les agrégations et les jointures. Ils illustrent également les conditions utilisées par l'optimiseur de requête pour décider de l'application d'une vue indexée. Pour obtenir la liste complète de ces conditions, consultez Les vues indexées et l'optimiseur de requête.

Les requêtes sont basées sur les tables de Northwind, base de données exemple fournie avec SQL Server 2000 et elles peuvent être exécutées telles quelles. Pour voir les plans sélectionnés par l'optimiseur de requête avant et après la création des vues, vous pouvez utiliser l'outil Afficher le plan d'exécution de l'Analyseur de requêtes SQL. Bien que les exemples montrent comment l'optimiseur choisit le plan d'exécution le plus économique, la base de données exemple Northwind est trop petite pour illustrer l'amélioration de performances réalisée.

Les requêtes suivantes montrent deux méthodes permettant de trouver dans la table Order Details les cinq produits bénéficiant de la remise totale la plus élevée.

Requête 1

SELECT TOP 5 ProductID, SUM(UnitPrice*Quantity) - SUM(UnitPrice*Quantity*(1.00-Discount))AS Rebate
FROM [Order Details]
GROUP BY ProductID
ORDER BY Rebate DESC
Requête 2
SELECT TOP 5 ProductID, SUM(UnitPrice*Quantity*Discount)AS Rebate
FROM [Order Details]
GROUP BY ProductID
ORDER BY Rebate DESC

Le plan d'exécution sélectionné par l'optimiseur de requête prévoit :

  • Un balayage de l'index en cluster de la table Order Details, dont le nombre de lignes est estimé à 2nbsp;155.
     
  • Un opérateur Hash Match/Aggregate qui se base sur la colonne GROUP BY pour placer les lignes sélectionnées dans une table de hachage et qui calcule l'agrégation SUM pour chaque ligne.
     
  • Un opérateur de tri TOP 5 basé sur la clause ORDER BY.
Vue 1

L'ajout d'une vue indexée incluant les agrégations nécessaires à la colonne Rebate modifie le plan d'exécution de la requête. Si la table fait plusieurs millions de lignes, l'exécution de la requête est considérablement améliorée.

CREATE VIEW Vdiscount1 WITH SCHEMABINDING AS
SELECT SUM(UnitPrice*Quantity)AS SumPrice, 
 SUM(UnitPrice*Quantity*(1.00-Discount))AS SumDiscountPrice, COUNT_BIG(*) AS Count, ProductID 
FROM dbo.[Order Details]
GROUP BY ProductID
   GO
CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount1 (ProductID)

Le plan d'exécution de la première requête montre que l'optimiseur utilise la vue Vdiscount1. Cependant, la vue ne sera pas utilisée par la deuxième requête car elle ne contient pas l'agrégat SUM(UnitPrice*Quantity*Discount). Il est possible de créer une autre vue indexée qui tienne compte des deux requêtes.

Vue 2

CREATE VIEW Vdiscount2 WITH SCHEMABINDING AS
SELECT SUM(UnitPrice*Quantity)AS SumPrice, SUM(UnitPrice*Quantity*(1.00-Discount))AS SumDiscountPrice,
 SUM(UnitPrice*Quantity*Discount)AS SumDiscountPrice2, COUNT_BIG(*) AS Count, ProductID
FROM dbo.[Order Details]
GROUP BY ProductID
GO
CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount2 (ProductID)

Avec cette vue indexée, le plan d'exécution des deux requêtes contient maintenant :

  • Un balayage de l'index en cluster de la vue Vdiscount2 dont le nombre de lignes est estimé à 77
     
  • Une fonction de tri TOP 5 basée sur la clause ORDER BY

L'optimiseur de requête a sélectionné la vue parce que son exécution est la plus économique, bien qu'elle n'ait pas été référencée dans la requête.

Requête 3

La troisième requête ne diffère des deux précédentes que par le remplacement de ProductID par la colonne OrderID, non incluse dans la définition de la vue. Ceci est en contradiction avec la condition selon laquelle toutes les expressions de la liste de sélection de la requête doivent pouvoir être dérivées de la liste de sélection de la vue issue des tables non incluses dans la définition de la vue.

SELECT TOP 3 OrderID, SUM(UnitPrice*Quantity*Discount) OrderRebate
FROM dbo.[Order Details]
GROUP BY OrderID
ORDER BY OrderRebate desc

Pour traiter cette requête, il faudrait utiliser une vue indexée séparée. Il serait possible d'inclure OrderID dans Vdiscount2. Cependant, la vue obtenue contiendrait autant de lignes que la table d'origine, un résultat qui n'améliorerait en rien les performances par rapport à l'utilisation de la table.

Requête 4

Cette requête calcule le prix moyen par produit.

SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount)) AS 
 AvgPrice, SUM(od.Quantity) AS Units 
FROM [Order Details] od, Products p 
WHERE od.ProductID=p.ProductID 
GROUP BY ProductName, od.ProductID

La définition d'une vue indexée ne peut pas inclure d'agrégats complexes (tels que STDEV, VARIANCE ou AVG). Cependant, une vue indexée peut exécuter une requête contenant un calcul de moyenne (AVG) en incluant des fonctions d'agrégation simple qui sont associées pour réaliser une agrégation complexe.

Vue 3

Cette vue indexée contient des fonctions d'agrégation simple nécessaires à l'exécution d'une fonction AVG. Après la création de la vue 3 et l'exécution de la requête 4, le plan d'exécution montre la vue utilisée. L'optimiseur peut dériver l'expression AVG des colonnes d'agrégation simple Price et Count de la vue.

CREATE VIEW View3 WITH SCHEMABINDING AS 
SELECT ProductID, SUM(UnitPrice*(1.00-Discount))AS Price, COUNT_BIG(*)AS Count, SUM(Quantity)AS Units 
FROM dbo.[Order Details] 
GROUP BY ProductID 
Go
CREATE UNIQUE CLUSTERED INDEX iv3 ON View3 (ProductID)
Requête 5

Cette requête, similaire à la quatrième, ne s'en différencie que par une condition de recherche supplémentaire. La vue 3 continue à s'appliquer à cette requête même si cette condition ne référence que des colonnes appartenant à une table non incluse dans la définition de la vue.

SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount))AS 
 AvgPrice, SUM(od.Quantity)AS Units 
FROM [Order Details] AS od, Products AS p 
WHERE od.ProductID=p.ProductID 
AND p.ProductName like '%Tofu%' 
GROUP BY ProductName, od.ProductID
Requête 6

Dans le cas suivant, l'optimiseur de requête ne peut pas utiliser la vue 3. Une condition de recherche, od.UnitPrice>10 a été ajoutée. Elle contient une colonne appartenant à la table concernée par la définition de la vue, mais cette colonne n'apparaît pas dans la liste GROUP BY et le prédicat de recherche n'apparaît pas non plus dans la définition de la vue.

SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount)) AS 
 AvgPrice, SUM(od.Quantity) AS Units 
FROM [Order Details] od, Products p 
WHERE od.ProductID = p.ProductID 
AND od.UnitPrice > 10 
GROUP BY ProductName, od.ProductID
Requête 7

L'optimiseur de requête peut utiliser la vue 3 dans la requête 7. En effet, la colonne définie dans la nouvelle condition de recherche od.ProductID in (1,2,13,41) est incluse dans la clause GROUP BY de la définition de la vue.

SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount)) AS 
 AvgPrice, SUM(od.Quantity) AS Units
FROM [Order Details] AS od, Products AS p
WHERE od.ProductID = p.ProductID
AND od.ProductID in (1,2,13,41)
GROUP BY ProductName, od.ProductID
Vue 4

Cette vue remplit les conditions de la requête 6. Elle inclut dans la définition de la vue la colonne od.Discount.

CREATE VIEW View4 WITH SCHEMABINDING AS 
SELECT ProductName, od.ProductID, SUM(od.UnitPrice*(1.00-Discount)) AS 
 AvgPrice, SUM(od.Quantity) AS Units, COUNT_BIG(*) AS Count 
FROM dbo.[Order Details] AS od, dbo.Products AS p 
WHERE od.ProductID = p.ProductID 
AND od.UnitPrice > 10
GROUP BY ProductName, od.ProductID
GO
CREATE UNIQUE CLUSTERED INDEX VdiscountInd on View4 (ProductName, ProductID)
Requête 8

L'index de la vue 4 peut également être utilisé dans une requête ajoutant une jointure vers la table Orders. Cette requête respecte la condition selon laquelle les tables citées dans sa clause FROM sont un sur-ensemble des tables de la clause FROM de la vue indexée.

SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount)) 
 AS AvgPrice, SUM(od.Quantity) AS Units 
FROM dbo.[Order Details] AS od, dbo.Products AS p, dbo.Orders AS o 
WHERE od.ProductID = p.ProductID and o.OrderID = od.OrderID 
AND od.UnitPrice > 10 
GROUP BY ProductName, od.ProductID

Les deux dernières requêtes sont des variantes de la huitième. Comme elles ne respectent pas une des conditions de l'optimiseur, à la différence de la huitième requête, elles ne peuvent pas utiliser la vue 4.

Requête 8a

La requête 8a ne peut pas utiliser la vue indexée en raison d'une différence dans la clause WHERE entre UnitPrice qui est supérieur à 10 dans la définition de la vue et à 25 dans la requête. Le prédicat des conditions de recherche de la requête doit être un sur-ensemble des prédicats de conditions de recherche de la définition de la vue.

SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount)) AvgPrice, SUM(od.Quantity) 
 AS Units 
FROM dbo.[Order Details] AS od, dbo.Products AS p, dbo.Orders AS o 
WHERE od.ProductID = p.ProductID and o.OrderID = od.OrderID 
AND od.UnitPrice > 25 
GROUP BY ProductName, od.ProductID
Requête 8b

La table Orders ne fait pas partie de la définition de la quatrième vue indexée. Malgré cela, ajouter un prédicat à cette table désactive l'utilisation de la vue indexée. Sinon, le prédicat ajouté pourrait éliminer des lignes supplémentaires participant aux agrégats, comme le montre la requête 8b.

SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount)) 
 AS AvgPrice, SUM(od.Quantity) AS Units 
FROM dbo.[Order Details] AS od, dbo.Products AS p, dbo.Orders AS o 
WHERE od.ProductID = p.ProductID and o.OrderID = od.OrderID 
AND od.UnitPrice > 10 
AND o.OrderDate > '01/01/1998' 
GROUP BY ProductName, od.ProductID


Informations complémentaires

La documentation en ligne de Microsoft SQL Server 2000 contient davantage d'informations sur les vues indexées. Pour plus d'informations, consultez les ressources suivantes :


Les informations contenues dans ce document représentent l'opinion actuelle de Microsoft Corporation sur les points cités à la date de publication. Microsoft s'adapte aux conditions fluctuantes du marché et cette opinion ne doit pas être interprétée comme un engagement de la part de Microsoft ; de plus, Microsoft ne peut pas garantir la véracité de toute information présentée après la date de publication.

Ce livre blanc est fourni à titre d'information uniquement. MICROSOFT EXCLUT TOUTE GARANTIE, EXPLICITE OU IMPLICITE, EN CE QUI CONCERNE CE DOCUMENT.

Il incombe à l'utilisateur de respecter toutes les lois applicables en matière de droits d'auteur. Aucune partie de ce document ne peut être reproduite, stockée ou introduite dans un système de restitution, ou transmise à quelque fin ou par quelque moyen que ce soit (électronique, mécanique, photocopie, enregistrement ou autre) sans la permission expresse et écrite de Microsoft Corporation.

Microsoft peut détenir des brevets, avoir déposé des demandes d’enregistrement de brevets ou être titulaire de marques, droits d’auteur ou autres droits de propriété intellectuelle portant sur tout ou partie des éléments qui font l’objet du présent document. Sauf stipulation expresse contraire d’un contrat de licence écrit de Microsoft, la fourniture de ce document n’a pas pour effet de vous concéder une licence sur ces brevets, marques, droits d’auteur ou autres droits de propriété intellectuelle.

Microsoft, ActiveX, JScript, Visual Basic et Visual C++ sont soit des marques déposées de Microsoft Corporation, soit des marques de commerce de Microsoft Corporation, aux États-Unis d'Amérique et/ou dans d'autres pays.

Les noms de sociétés et de produits mentionnés dans ce document sont des marques de leurs propriétaires respectifs.

Dernière mise à jour le jeudi 28 décembre 2000



Pour en savoir plus
Afficher: