Des données faussées causent beaucoup de dégâts

0,05 %
des clients dans l’exemple précédent,
le prédicat que le listing 3 montre sélectionnera
50 clients au plus. La relation
entre un prédicat et le nombre de
lignes qu’il choisit illustre bien la notion
de sélectivité. La sélectivité d’un
prédicat est le ratio du nombre de
lignes satisfaisant à  la clause WHERE
par rapport au nombre total de lignes
dans la table. La sélectivité d’un index
est considérée haute, comme ici,
quand la valeur clé d’index n’identifie qu’un petit nombre de lignes. Si la sélectivité
est haute et si un index correspondant
existe, l’optimiseur de requêtes
utilise en principe l’accès par
index parce que l’extraction d’un nombre de lignes relativement petit
est plus rapide avec l’ordre de lignes
triées de l’accès par index, que le balayage
séquentiel de toute la table. En
appliquant l’accès par index, l’optimiseur
de requêtes utilise l’index existant
i_country pour satisfaire la requête,
comme le montre la figure 1. En revanche,
le prédicat du listing 4, qui
identifie les clients US, sélectionne
beaucoup plus de lignes. Dans ce cas,
la meilleure méthode d’accès est un
balayage de table, illustré figure 2,
parce que la lecture séquentielle de
toutes les pages de données est nettement
plus rapide que la lecture de
pages spécifiques à  l’aide de l’index
existant. Nous voyons donc que l’optimiseur
de requêtes choisit un balayage
de table si l’estimation d’I/O pour l’accès
par index est supérieure au
nombre de pages que la table remplit.
Dans ce cas, la sélectivité est basse.

Dans le listing 3 et le listing 4, l’optimiseur
de requêtes choisit le plan de
requête auquel vous vous attendez.
Toutefois, si vous essayez d’écrire une
instruction SELECT générique que
vous pourrez utiliser pour interroger
n’importe quel pays dans lequel habitent
vos clients, le choix de l’optimiseur
de requêtes pour l’accès aux données
risque de vous surprendre.
D’après le raisonnement précédent,
vous pourriez conclure que l’optimiseur
de requêtes choisira le balayage
d’index pour exécuter le batch présenté
dans le listing 5, parce que la valeur
de la variable @country est réglée
sur Germany. Or, l’optimiseur de requêtes
utilise plutôt un balayage de
tables – même si l’index correspondant
pour la colonne country existe. La
raison de ce comportement est
simple : l’optimiseur de requêtes ne
peut pas utiliser les statistiques existantes
comme l’ont fait les requêtes du
listing 3 et du listing 4, parce que l’un
des prédicats du listing 5 contient une
variable. Et le batch du listing 5 déclare
la variable @country dans le même domaine
que la requête. L’optimiseur de
requêtes ne connaît pas la valeur de la
variable quand il choisit la méthode
d’accès, parce qu’il n’effectue pas des
passage multiples quand il optimise la
requête. Donc, la figure 3 montre que
l’optimiseur de requêtes a utilisé le balayage de tables parce que le paramètre
Row count a une valeur de 0.
L’optimiseur de requêtes attribue cette
valeur quand il ne connaît pas le
nombre de lignes sélectionnées.

Opérations de jointure. Les colonnes
contenant des données faussées peuvent
aussi influencer la performance
d’opérations de jointure. Soit une table
de 500.000 lignes appelée orders. Le
listing 6 montre l’instruction qui crée la
table orders et le listing 7 contient le
batch qui charge les lignes de la table.
Supposons que 20 % de l’activité viennent
des ventes de PC, et que les
contrôleurs ne représentent que
0,05 % des ventes totales. Supposons
également que toutes les colonnes
pertinentes dans la requête (customers.
customerid, orders.customerid,
item_name) soient indexées. Si vous
demandez le revenu moyen des clients
ayant acheté des PC, comme le montre
le listing 8, SQL Server doit lire toutes
les lignes de la table customers et
80.000 de la table orders pour effectuer
la jointure. Compte tenu du grand
nombre de lignes que les deux tables
contiennent, l’optimiseur de requêtes
choisit de ne pas utiliser les index existants
et exécute plutôt l’opération de
jointure en utilisant la méthode de
jointure hash illustrée figure 4. Une
jointure hash est plus performante sur
des tables dont la taille diffère considérablement,
sur des lignes non triées, et
sur des colonnes non indexées. Cette
méthode de jointure s’effectue en
deux phases. Premièrement, SQL
Server construit une structure hash
constituée des valeurs de la table la
plus petite (extérieure), qui ont été
« hashed », puis sonde chaque ligne de
la table plus grande (intérieure) pour
trouver des correspondances dans la
table hash. La jointure hash ne balaie la
table intérieure qu’une fois. Par opposition,
pour exécuter une jointure des
tables customers et orders, fondée sur
les ventes de contrôleurs, comme le
montre le listing 9, SQL Server ne doit
lire que 200 lignes dans la table orders.
C’est pourquoi l’optimiseur de requêtes
choisit d’exécuter la jointure en
utilisant la jointure par boucle imbriquée,
illustrée figure 5. Dans une jointure
en boucle imbriquée, SQL Server
accède à  la table extérieure ligne par
ligne. Ensuite, en ne recherchant que
les lignes correspondantes, SQL Server
balaie la table intérieure, qui doit
contenir une colonne de jointure indexée,
Cette méthode de jointure produit
des I/O pour chaque balayage d’index
de la table intérieure (c’est-à -dire,
l’I/O pour 200 balayages).