> Data > Une introduction aux vues indexées

Une introduction aux vues indexées

Data - Par iTPro.fr - Publié le 24 juin 2010
email

par Karen Delaney
SQL Server 2000 ouvre une voie alternative pour atteindre des performances maximales


Remarque : Les auteurs ont basé leurs articles SQL Server 2000 sur des versions antérieures à  la Bêta 2. Aussi, il se peut que vous remarquiez quelques différences entre la Bêta 2 et le comportement ou les interfaces décrits dans cet article. En particulier, veuillez noter que la fonction vues indexées ne sera disponible que dans SQL Server 2000 Enterprise Edition. Toutefois, on peut installer Entreprise Edition sur un serveur NT 4 ou Windows 2000 (W2K). On n'est pas obligé d'utiliser NT 4.0 Enterprise ou W2K Advanced Server.

Le puissant et récent support de SQL Server 2000 des vues indexées promet de nous faciliter la tâche tout en exécutant les applications et requêtes plus rapidement. Cela vous semble trop beau pour être vrai ? Les vues indexées permettent de précalculer toutes sortes de jointures, agrégations et formules pour que l'on n'ait plus à  écrire ces conditions dans chaque requête. De plus, Microsoft déclare obtenir des gains de performance de 10 à  100 fois supérieurs au sein des applications et requêtes accédant aux vues indexées par rapport aux tables de base. Bien qu'Oracle supporte une fonctionnalité similaire, appelée vues matérialisées, les nouvelles vues indexées de SQL Server vont bien au-delà  de ce qu'offre la concurrence.

Les nouvelles vues indexées de SQL Server vont bien au-delà  de ce qu'offre la concurrence


Vues indexées ou vues matérialisées ?

Vous avez peut-être entendu parler du concours doté d'un prix d'un million de dollars organisé par Oracle lorsque Microsoft a lancé SQL Server 7.0. Ce concours a été modifié trois fois, mais la version finale ressemble à  ceci :
"Oracle Corp. versera la somme d'un million de dollars à  la première personne capable de démontrer que SQL Server 7.0, avec une base de données TPC-D d'un Tera-octets peut se rapprocher à  1/100 près des meilleures performances publiées à  ce jour par Oracle pour la requête n°5 du standard TPC-D actuel (version 1.3.1). Pour être éligible, le candidat doit réaliser un test TPC-D sur 1 To complet, répondant à  toutes les contraintes de chargement, de mise à  jour et de recherche des données, et publier un rapport intégral de toutes les mesures de performances. Le candidat peut utiliser n'importe quelle plate-forme habilitée à  héberger SQL Server 7.0. Les tests doivent être validés par un organisme certifié TPC".

Oracle était sûr de son fait, car son système prenait déjà  en charge une fonctionnalité appelée "Vues matérialisées". Etant donné que le TPC (Transaction Processing Performance Council) documente de manière exhaustive les spécifications de ses benchmarks, Oracle a été en mesure de créer des vues matérialisées correspondant aux requêtes exécutées au cours du benchmark. Ainsi, il suffit à  la base de données de lire les résultats pré-calculés depuis le disque, sans avoir à  effectuer aucun traitement. Si SQL Server 7.0 avait pris en charge les vues indexées, Oracle n'aurait jamais organisé ce concours.
Les vues indexées de SQL Server 2000 présentent plusieurs avantages par rapport aux vues matérialisées d'Oracle. Tout d'abord, les vues matérialisées ne sont pas dynamiques. Il faut les rafraîchir manuellement pour prendre en compte les modifications intervenues dans les données. Ensuite, l'optimiseur de requêtes d'Oracle ne prévoit pas de façon automatique l'utilisation d'une vue matérialisée si on ne précise pas directement le nom de la vue dans la clause FROM de la requête.

Qu'on utilise des vues indexées ou matérialisées, dans les deux cas, il faut con

SQL Server prend en charge les vues depuis ses toutes premières versions. Une
vue est schématiquement une instruction SELECT enregistrée qui agit comme un filtre.
Supposons par exemple que l’on dispose d’une grande table contenant les enregistrements
de tous les clients de l’entreprise, et que quelques fois seuls les noms et les
numéros de téléphone des clients possédant le même code postal que vous, vous
intéressent. On pourrait créer une vue semblable à  celle-ci :

CREATE VIEW local_customers
AS
SELECT name, phone_number
FROM big_customer_list
WHERE zip = ‘98370’

A présent, on peut accéder à  cette vue comme s’il s’agissait d’une table, mais
sans avoir besoin de l’espace de stockage distinct associé à  une table. A chaque
fois qu’une requête fait référence à  la vue, SQL Server intègre le code définissant
la vue à  toute autre condition spécifiée. Ensuite, SQL Server optimise, compile
et exécute la requête résultante. En utilisant quelques-uns des mécanismes spéciaux
de mise en mémoire cache de SQL Server 7.0, la base de données peut être capable
de réutiliser le plan, sans que cela soit aussi probable qu’avec les procédures
cataloguées de SQL Server.
Les vues présentent deux avantages principaux. D’abord, elles simplifient l’écriture
des requêtes parce qu’elles n’exigent pas d’accéder directement aux tables, et
que souvent, elles contiennent déjà  des restrictions, calculs, agrégations, et
jointures (ce qui dispense d’avoir à  retaper les conditions). Ensuite, les vues
permettent de mettre en oeuvre un mécanisme de sécurité, et d’octroyer des droits
d’accès aux utilisateurs pour une vue particulière des données d’une table et
non à  l’intégralité de la table.

Une vue ne contient aucune donnée sauvegardée : il s’agit simplement d’une requête
sauvegardée. Par conséquent, le contenu d’une vue est dynamique. Si on ajoute
des enregistrements à  une table ou qu’on modifie les valeurs des données, la vue
affichera les informations mises à  jour. Le revers de la médaille, c’est que si
la vue contient des calculs basés sur les colonnes de la table, SQL Server doit
effectuer ces calculs à  chaque fois qu’un utilisateur accède à  la vue. En outre,
pour faciliter le traitement de la requête, la base de données ne peut pas utiliser
d’index sur une colonne impliquée dans un calcul.
Prenons une vue type, qui affiche le jour de la semaine auquel les commandes ont
été passées. La table de base possède une colonne Orderdate, mais supposons que
l’on souhaite également extraire des informations concernant les commandes. Créez
par exemple une vue comme ceci :

USE northwind
GO
CREATE VIEW OrderDates
AS
SELECT orderId, Day = datepart(month, Orderdate), Orderdate, ShipName, ShipCity,
ShipPostalCode
FROM Orders

On pourra alors utiliser la vue pour extraire toutes les commandes passées en
décembre (pour analyser l’augmentation du chiffre d’affaire de l’entreprise pendant
les fêtes), mais aucun index ne permettrait de trouver ces enregistrements. Un
index sur la colonne Orderdate localiserait les enregistrements par date exacte,
mais pas par le biais du mois auquel la commande a été passée.
Avec les vues indexées de SQL Server 2000, on peut à  présent construire un index
clusterisé sur une vue. Un index clusterisé est le seul type d’index SQL Server
qui contienne des données ; l’index clusterisé sur une vue contient toutes les
données constituant la définition de la vue. Dès que l’on crée cette index clusterisé,
la vue est matérialisée. En d’autres termes, SQL Server alloue un espace de stockage
pour elle. On peut alors traiter la vue comme une table, construisant de multiples
index non clusterisés sur elle (j’ai basé les explications et les exemples suivants
sur SQL Server 2000 EAP4 (Early Adopters Program) build 047, une version intermédiaire
entre la bêta 1 et la bêta 2).

Les meilleures vues à  indexer

Certaines applications et requêtes ne tireront pas parti des vues indexées
de SQL Server 2000. A l’instar des index classiques, on ne tire aucun avantage
de la création d’index inutiles ou qui ne peuvent être utilisés par les
requêtes. La création de vues indexées inutilisées se fait aux dépends de
l’espace disque supplémentaire et d’une maintenance accrue lors de modifications
des données dans les tables de la base. Toutefois, lorsque les applications
et les requêtes utilisent les résultats pré-calculés enregistrés dans les
vues indexées, on peut constater une amélioration très sensible des performances
(plusieurs fois meilleures).
En règle générale, les applications de datamart, datamining et de prise
de décisions tirent parti de la plupart des vues indexées. Les requêtes
susceptibles de subir une nette augmentation des performances en utilisant
des vues indexées contiennent les éléments suivants :
· jointures et agrégation de tables de grande taille
· schémas de requêtes répétitifs
· agrégations répétitives sur les mêmes groupes de colonnes ou sur des colonnes
qui se recoupent
· toute combinaison des éléments précédents

A l’inverse, les systèmes OLTP (Online Transaction Processing), qui effectuent
de nombreuses opérations d’écriture sur des enregistrements pris au hasard
ne pourront pas utiliser les vues indexées. Les bases de données à  forte
activité de mise à  jour ne pourront pas non plus bénéficier des vues indexées
car il est peu probable que les mises à  jour portent sur le même groupe
d’enregistrements de la vue. De plus, une vue qui n’est rien d’autre qu’un
sous-ensemble d’enregistrements et de colonnes, sans autre agrégation ou
calcul, n’est pas un bon candidat à  l’indexation.

Téléchargez cette ressource

Checklist de protection contre les ransomwares

Checklist de protection contre les ransomwares

Comment évaluer votre niveau de protection contre les ransomwares à la périphérie du réseau, et améliorer vos défenses notamment pour la détection des ransomwares sur les terminaux, la configuration des appareils, les stratégies de sauvegarde, les opérations de délestage... Découvrez la check list complète des facteurs clés pour améliorer immédiatement la sécurité des terminaux.

Data - Par iTPro.fr - Publié le 24 juin 2010