> Data > Matérialisez vos vues

Matérialisez vos vues

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

par Michelle A. Poolet - Mis en ligne le 22/06/2005 - Publié en Octobre 2004

Si vous êtes prêt à  mettre le prix, les vues indexées peuvent dynamiser vos requêtes

SQL Server 2005, la prochaine version du système de base de données de Microsoft, inclura de nombreuses fonctions et extensions destinées à  accroître votre productivité, en particulier si vous développez des applications Web orientées base de données. Toutefois, Microsoft a retardé la sortie de la version définitive du produit jusqu'au premier semestre de l'année prochaine et il faudra encore attendre au moins un an après le lancement pour voir de nombreux services de production migrer vers cette nouvelle mouture. Entre temps, vous pouvez faire beaucoup en vue d'optimiser vos bases de données SQL Server 2000 ...En tant que modéliseur de données et architecte de conception, mon instinct me pousse à  examiner et optimiser le schéma des tables. Vous savez que je plaide en faveur d'une normalisation poussée des tables, une démarche essentielle pour préserver l'intégrité des données au cours des activités d'insertion et de gestion. Les avantages de la normalisation sont supérieurs au surcroît de travail généré par les jointures multitables nécessaires à  l'extraction des données. Il est néanmoins possible de réduire sensiblement le temps système consacré à  la création d'une jointure entre quatre ou cinq tables en vue d'extraire des données d'une base de données fortement normalisée ou lié à  l'agrégation de quantités importantes de données dans un rapport récapitulatif. Pour cela, vous pouvez créer une vue et, au lieu de laisser au moteur SQL Server le soin de la reconstruire dynamiquement chaque fois qu'elle est utilisée dans une requête, vous pouvez la « matérialiser ». Dans ce cas, il ne s'agit plus d'une table virtuelle, mais d'une table physique.

Une vue constitue assurément une table virtuelle dérivée. Son objet est d'améliorer la visibilité des données en les présentant dans un contexte plus informatif et de contrôler l'accès auxdites données en empêchant les utilisateurs non concernés de visualiser celles ayant un caractère critique ou sensible. SQL Server « matérialise » ou rend réel une vue en créant un index ordonné en clusters unique sur celle-ci. C'est pourquoi ce type de vue est parfois appelé vues indexées. Comme pour un index ordonné en clusters, des données sont associées à  une vue matérialisée.

Les vues matérialisées ne constituent pas une nouveauté dans le monde des bases de données. Même si les vues indexées faisaient figure de nouveauté dans SQL Server 2000, elles existent depuis des années dans d'autres systèmes de gestion de base de données (SGBD), notamment dans les systèmes Oracle et DB2 d'IBM. Les fournisseurs de SGBD destinés aux grandes plates-formes ont développé les vues matérialisées en vue d'améliorer leurs systèmes de data warehousing. Un data warehouse regroupe dans un même référentiel de grande taille des données techniques que vous avez intégrées à  partir de sources multiples. Ce référentiel peut héberger des données détaillées d'applications opérationnelles, des données récapitulatives de systèmes d'aide à  la décision ou une combinaison des deux. Dans un data warehouse, les données sont généralement synthétisées selon plusieurs dimensions (par ex., date, emplacement, produit), puis stockées pour les interrogations agrégées par OLAP et les applications d'aide à  la décision. Nous allons maintenant examiner l'utilité d'une vue matérialisée dans d'autres environnements que les data warehouse.

pour améliorer les performances au niveau
d’un data warehouse, pourquoi
envisager leur utilisation avec une base
de données transactionnelle ? Après
tout, lorsque vous concevez ce type de base de données, vos
préoccupations sont la rapidité et l’agilité, comme pour un
constructeur de voitures de sport. Vous souhaitez par conséquent
réduire au minimum le nombre de structures, telles
que les index, qui dégradent les performances. Dans une
base de données transactionnelle, les index servent à  améliorer
les performances au cours des interrogations, mais la
pratique générale démontre qu’il faut les employer avec parcimonie
et uniquement dans les cas nécessaires, en raison de
la charge supplémentaire liée à  l’insertion ou à  la mise à  jour
des données. Par ailleurs, comme une vue matérialisée est
une copie d’une ou de plusieurs tables, cela peut facilement
multiplier par deux vos besoins en capacité de stockage des
données.

Les vues matérialisées permettent des accès très rapides
aux données. L’accroissement des performances compense
généralement le supplément d’espace disque et de charge
processeur lié à  la synchronisation des données des tables
avec les données des vues matérialisées. Je ne peux pas affirmer
catégoriquement que les améliorations ainsi apportées
valent largement les contraintes en termes d’espace disque
et de cycles processeur supplémentaires ; tout dépend de la
situation. Je peux néanmoins suggérer les scénarios type suivants
afin de tester la validité d’une vue matérialisée par rapport
à  votre environnement. Il convient de garder à  l’esprit
l’aspect suivant : comme les vues indexées augmentent sensiblement
la charge au cours de la modification des données,
les tables fortement statiques ou qui font l’objet d’ajouts ou
de mises à  jour pendant les heures creuses sont les plus appropriées.

Synthèse des données dans une base de données opérationnelle.
Comme vous le savez, l’agrégation des données
dans une base de données opérationnelle monopolise beaucoup de ressources système. Le processus ne se contente pas
de calculer les sommes, quantités ou moyennes ; le gestionnaire
de verrous de SQL Server doit équilibrer les requêtes
portant sur les données et peut même
être amené à  retarder les requêtes de mise
à  jour pendant le processus d’agrégation.
De surcroît, si à  chaque exécution d’une
requête d’agrégation, les enregistrements
ne sont pas déjà  présents dans le cache de
données, SQL Server doit analyser physiquement
les données du disque dur vers
la mémoire. L’augmentation de la quantité
de données à  agréger entraîne aussi un accroissement
du nombre d’E/S physiques.
SQL Server peut faire appel à  plusieurs techniques pour minimiser
le retard, notamment en libérant les pages de données
immédiatement après le traitement des enregistrements,
mais le point essentiel est que les requêtes
d’agrégation peuvent ralentir les performances, en particulier
si la charge supportée par votre système est déjà  élevée.

Le listing 1 illustre un exemple de vue récapitulative matérialisée
portant sur la table Orders de la base de données
Northwind, laquelle table contient des données sur la destination
de toutes les commandes. Un rapport récapitulatif
des commandes par code postal (correspondant à  l’adresse
d’expédition) serait utile afin d’analyser les tendances des
ventes. La figure 1 montre un ensemble de résultat partiel retourné
par l’exécution du listing 1. Sur la base de ce rapport,
la société Northwind Traders peut déterminer facilement les
codes postaux recevant les quantités de produits les plus élevées.

Elimination des jointures multitables. Dans une base
de données opérationnelle, il n’est pas rare d’effectuer des
jointures sur plusieurs tables afin d’obtenir les informations
voulues. Par exemple, pour obtenir un rapport des employés
par région dans Northwind, il est nécessaire de lier quatre
tables. Le fait d’avoir la liste des employés d’une région spécifique
ou les informations relatives aux employés pour une
zone géographique précise n’est pas seulement pratique,
mais nécessaire. Une base fortement normalisée telle que
Northwind (voir le schéma entité-relation de la figure 2) ne
signifie pas nécessairement des délais d’interrogation importants
liés à  la réalisation par SQL Server des jointures entre
les tables pour retourner les données souhaitées. Le code du
listing 2 illustre un exemple de matérialisation d’une vue
créée à  partir d’une jointure sur quatre tables. La figure 3
montre une partie du rapport découlant de l’exécution du
code en question.

Séparation des colonnes calculées. Une règle inhérente
à  la bonne normalisation d’une base de données
consiste à  conserver les données calculées dans une table
distincte des données utilisateur. Les données calculées sont
créées ou générées à  partir des données collectées dans le
cadre de l’activité quotidienne de traitement des données
exécutée par votre système. Vous pouvez considérer cet aspect
comme un deuxième niveau de traitement des données.
Si vous souhaitez calculer les cumuls ou inscrire de manière permanente les totaux généraux dans la base de données,
il est peu probable que vous stockiez les calculs dans
les tables de données d’origine, en raison de la charge supplémentaire
induite par les opérations de recalcul
constantes.

vues classiques, les valeurs calculées ne sont pas stockées, de
sorte que SQL Server doit les reconstituer dynamiquement à 
chaque requête sur la table. Pour les tables comportant juste
quelques lignes, cette reconstitution dynamique ne pose pas
de problème ; en revanche, avec les tables d’une taille supérieure
au giga-octet, la dégradation des performances résultante
sera considérable. Vous pouvez toutefois parvenir à 
créer un index sur la colonne calculée et donc la rendre permanente
dans la base de données. (Pour plus d’informations,
consultez l’article de Brian Lawton « Des richesses insoupçonnées
à  portée de la main » dans cette édition de SQL
Server Magazine.) Mais il est probable que les besoins en calcul
évolueront dans le temps, imposant une modification de
la structure de la table. Si celle-ci comporte de nombreuses
colonnes et est sollicitée par vos applications, un changement
de sa structure peut s’avérer problématique. Une autre
possibilité consiste à  créer une vue contenant les données
calculées, puis à  la matérialiser afin de la rendre permanente
dans la base de données. Lorsque vous devez changer l’algorithme
de calcul, il est nettement plus simple de supprimer
et de recréer une vue indexée que d’utiliser une table avec
une colonne calculée.Vous pouvez aisément configurer une vue matérialisée
afin de synthétiser les données de détail, comme l’illustre le
listing 3. Le code de ce dernier matérialise une vue avec colonne
calculée contenant les totaux généraux des tables
Orders et Order Details. Au lieu de recalculer les totaux généraux
des commandes chaque fois que vous en avez besoin,
la vue matérialisée Order_Totals contient ces valeurs de manière
permanente. La figure 4 présente une partie de ces
données calculées synthétisées.

Prise en charge de votre
application Web locale. Par nature,
les applications Web génèrent
une charge importante. Il
faut du temps rien que pour afficher
l’écran dans le navigateur
Web. Lorsqu’un utilisateur du
Web envoie une requête à  une
base de données aux performances
médiocres, le délai supplémentaire
induit peut rendre
l’application Web inutilisable. Si
vous prenez en charge les applications
Web, assurez-vous que
votre base de données est paramétrée
pour des temps de
réponse très courts. Tous les ouvrages
que j’ai lus sur le développement
et la conception de bases de données Web
recommandent deux choses : normalisez la conception de
votre base de données et indexez vos tables. Comme
l’optimiseur de requêtes de SQL Server utilise autant que
possible les index, une vue indexée constitue un choix idéal
pour l’extraction de données. Ainsi, vous pourrez constater
des temps de réponse améliorés de vos applications Web si
vous indexez la vue correspondant à  une requête à  partir
d’un navigateur Web.

Téléchargez gratuitement cette ressource

Endpoint Security : Guide de Mise en œuvre

Endpoint Security : Guide de Mise en œuvre

Détournement d’applications légitimes, élévation de privilèges, logiciels malveillants furtifs : comment les solutions de Endpoint Security permettent elles de faire face aux nouvelles techniques d'attaques complexes ? Découvrez, dans ce Guide Endpoint Security, les perspectives associées à leur mise en œuvre.

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