Matérialisez vos vues

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.