Utiliser les vues indexées

productid

L’optimiseur de requêtes de SQL Server se rend compte que les sommes pré-calculées
de toutes les valeurs Quantity de chaque productid sont déjà  disponibles dans
l’index pour la vue Product_Totals. L’optimiseur évaluera le coût d’utiliser cette
vue indexée dans le traitement de la requête. Toutefois, le fait de disposer d’une
vue indexée n’implique pas que l’optimiseur de requêtes l’utilisera systématiquement
pour le plan d’exécution de la requête. En fait, même si on référence directement
la vue indexée dans la clause FROM, l’optimiseur peut décider d’accéder au contraire
directement à  la table de la base.

Pour déterminer si l’optimiseur utilise la vue indexée, on peut consulter le plan
de la requête dans l’analyseur de requêtes. Le graphique du plan d’exécution estimé
de l’écran 1 indique que la requête utilise l’index clusterisé sur la vue.
On peut comparer le coût d’utilisation des valeurs pré-calculées d’une vue indexée
au coût d’un accès direct aux informations de tri de la table de la base. Une
nouvelle option de requête SQL Server 2000, OPTION (EXPAND VIEWS), contraint SQL
Server à  étendre toutes les vues indexées pour faire ressortir leurs instructions
SELECT sous-jacentes, et ce pour que l’optimiseur ne prenne pas en compte l’index
des vues. Pour comparer le coût relatif à  l’accès aux mêmes données avec ou sans
vue indexée, exécutez l’instruction SET STATISTICS IO ON, exécutez le code proposé
dans le listing 2 (lequel exécute un SELECT utilisant la vue indexée, et un autre
qui utilise EXPAND VIEWS), puis comparez les valeurs renvoyées pour les lectures
logiques. Lorsque j’ai exécuté ce code, j’ai obtenu une valeur de 2 lectures logiques
lorsque le système utilisait la vue indexée et 19 lectures logiques lorsque j’ai
contraint SQL Server à  étendre la vue.

Une deuxième option nouvelle des index, NOEXPAND, fait le contraire d’EXPAND VIEWS.
Utilisez NOEXPAND dans la clause FROM de votre requête pour forcer SQL Server
à  utiliser la vue indexée, mais seulement si vous avez référencé la vue dans l’instruction
FROM. Le listing 3 présente une vue indexée correspondant à  la table Orders de
Northwind. Une requête utilisant cette vue n’utilise pas l’index sur cette vue,
comme on peut le constater si vous affichez le plan d’exécution de la requête.
Cependant, l’utilisation de l’option WITH (NOEXPAND) contraint l’optimiseur de
requêtes à  considérer uniquement la vue (et les index de cette vue) dans le plan
d’exécution. L’optimiseur ne considère pas la table de la base ni ses index. Si
on utilise l’option WITH (NOEXPAND) avec une vue qui n’est pas indexée, on obtient
le message d’erreur suivant :

Server: Msg 8171, Level 16, State 2, Line 1
Hint ‘noexpand’ on object ‘MyRegularView’ is invalid.

La base de données doit toujours étendre les vues non-indexées de manière à  afficher
leur instruction SELECT sous-jacente, et n’accéder aux données qu’à  partir de
la table de la base.
Notez que si on accède à  la table sous-jacente dans une clause FROM, l’option
OPTION(EXPAND VIEWS) empêchera SQL Server d’utiliser une vue indexée sur cette
table, mais SQL Server 2000 ne possède pas d’option permettant de faire l’inverse.
On ne peut pas contraindre la base de données à  utiliser une vue indexée sans
référencer cette vue dans votre requête. L’optimiseur décide si l’arbre de la
requête de la vue correspond à  l’arbre de la requête de la requête soumise, ensuite,
il choisit l’option la plus rentable : utiliser la vue indexée ou plutôt la table
de base. De plus, l’optimiseur SQL Server 2000 peut décider qu’utiliser la table
de base est assez peu cher et ne prendra même pas le temps de déterminer un coût
pour l’utilisation de la vue indexée.

Comme je l’ai fait remarquer précédemment, on peut forcer l’optimiseur à  utiliser
l’index de la vue en référençant la vue indexée dans la clause FROM et en utilisant
l’option NOEXPAND. Cependant, on ne peut pas forcer l’optimiseur à  utiliser l’index
sur la vue en utilisant l’option INDEX qui indique à  SQL Server d’utiliser un
index particulier. Si on utilise INDEX=1 pour essayer de contraindre l’optimiseur
à  utiliser l’index clusterisé, SQL Server utilisera plutôt l’index clusterisé
sur la table de base. Même si on indique le nom de l’index clusterisé sur la vue
dans l’option INDEX, SQL Server utilisera malgré tout l’index clusterisé sur la
table de base. Sauf si on utilise l’option NOEXPAND, SQL Server remplacera la
vue par sa définition sous-jacente avant que l’optimiseur ne commence à  traiter
la requête, et l’optimiseur ne verra pas la vue.

Pour effectuer des tests, on peut contraindre SQL Server à  ne pas utiliser les
vues indexées en remplaçant simplement une option SET requise par une valeur erronée.
Par exemple, le fait de spécifier ANSI_NULLS OFF désactivera les vues indexées.