Est-il profitable de créer les index simples sur les colonnes des dimensions ? Afin de rentrer dans les détails je vous propose faire quelques tests simples.

Les particularités

Pour utiliser N index de la même table dans la même requête le moteur doit confronter (l'intersection en cas "AND") les N sous-ensembles au moyen de "Merge Join" (le coût O(n) environ), "Hash Match" (le coût O(n * log2 n) environ) ou une autre méthode i.e. "Bitmap".

Les particularités d'OLAP (relationnel) :

• le nombre de lignes dépasse facilement plusieurs dizaines et centaines millions alors que le nombre de modalités des tables de références reste quasiment le même par rapporte de OLTP. Donc le coût de l'intersection des sous-ensembles est beaucoup plus chère par rapport de OLTP
• les tables n'ont pas toujours les clés primaires et surtout les clés simples
• pour les tables des faits dans un entrepôt la clé habituelle est composite (identifiant d'entité + date/temps)
• pour les tables de faits agrégées dans un datamart la clé devrait inclure toutes les colonnes dimensions. En pratique, on ne crée pas cette clé en choisissant une des colonnes de dimension comme le cluster

Comment choisir un index cluster ?

1. En fonction des requêtes plus fréquentes il faut prendre quelques colonnes de dimension dont la filtrage est plus souvent.
2. Parmi les colonnes choisis sélectionner la plus sélective (combinaison des colonnes est possible aussi).

Souvent, la colonne choisi est celle de la date ou période.

Préparer les tests

Téléchargez le package des scripts SQL. Nous allons créer 2 tables typiques (voir CreateDatabase.sql). On ne prend pas en compte les tables de références pour garder la simplicité.

Table de faits (entrepôt)

CREATE TABLE dbo.facts (
  u_id int NOT NULL,
  u_time datetime NOT NULL,
  d1 smallint NOT NULL,
  d2 smallint NOT NULL,
  d3 smallint NOT NULL,
  d4 smallint NOT NULL,
  d5 smallint NOT NULL,
  d6 smallint NOT NULL,
  d7 smallint NOT NULL,
  d8 smallint NOT NULL,
  d9 smallint NOT NULL,
  d10 smallint NOT NULL,
  m1 int NOT NULL,
  m2 money NOT NULL,
  m3 float NOT NULL,
  CONSTRAINT PK_facts PRIMARY KEY CLUSTERED (u_id, u_time)
)

Table de faits agrégée (datamart)

CREATE TABLE dbo.agg1 (
  u_date date NOT NULL,
  d1 smallint NOT NULL,
  d2 smallint NOT NULL,
  d3 smallint NOT NULL,
  d4 smallint NOT NULL,
  d5 smallint NOT NULL,
  d6 smallint NOT NULL,
  d7 smallint NOT NULL,
  d8 smallint NOT NULL,
  d9 smallint NOT NULL,
  d10 smallint NOT NULL,
  u_cnt int NOT NULL,
  avg_m1 int NOT NULL,
  sum_m2 int NOT NULL,
  sum_m3 int NOT NULL,
)

Puis il nous faut remplir ces tables. Les 10 million lignes seront suffisantes déjà pour comprendre les contraintes et règles de la création des index. Il est possible paramétrer ce nombre ainsi que les nombres des modalités de dimensions (voir InitData.sql).

Après avoir remplir les tables vous devriez voir la distribution des valeurs dans le dimensions similaire la mienne.

Les requêtes

Le fichier "Queries.sql" contient l'ensemble des requêtes ainsi que les instructions de statistiques. Voici les résultats obtenus sur la machine relativement faible (UC 2 coeur, RAM 6 Go, no RAID).

Les explications des résultats

Q01

SELECT AVG(convert(bigint, m1)) FROM facts WHERE d5 = 1 AND d6 = 2

La sélectivité des index des dimensions d1..d5 n'est pas suffisante pour l'utilisation ces index ce que impose l'analyse de table (cluster) malgré l’existence des index.

Q02 et Q03

SELECT AVG(convert(bigint, m1)) FROM facts WHERE d6 = 3 AND d7 = 5

SELECT AVG(convert(bigint, m1)) FROM facts WITH (INDEX(0)) WHERE d6 = 3 AND d7 = 5

La sélectivité des index des dimensions d6..d10 est suffisante pour l'utilisation. Par contre l'intersection de sous-ensembles selon le plan Q02 est chère et donc moins performante que l'analyse de table en Q03.

Q04 et Q05

Mais ce n'est pas le cas suite à la condition supplémentaire "u_time BETWEEN". Il est moins chère d'en appliquer sur l'intersection de sous-ensembles.

SELECT SUM(m1) / COUNT(DISTINCT u_id), d2, d3, d4, d5 
FROM facts 
WHERE u_time BETWEEN '20110101' AND '20110131 23:59:59' AND d6 = 3 AND d7 = 5
GROUP BY d2, d3, d4, d5

SELECT SUM(m1) / COUNT(DISTINCT u_id), d2, d3, d4, d5 
FROM facts WITH (INDEX(1)) 
WHERE u_time BETWEEN '20110101' AND '20110131 23:59:59' AND d6 = 3 AND d7 = 5
GROUP BY d2, d3, d4, d5

Q06 et Q07

SELECT SUM(avg_m1) / SUM(u_cnt), d2, d3, d4, d5 
FROM agg1 
WHERE u_date BETWEEN '20110101' AND '20110101' AND d6 = 3 AND d7 = 5
GROUP BY d2, d3, d4, d5

SELECT SUM(avg_m1) / SUM(u_cnt), d2, d3, d4, d5 
FROM agg1 
WHERE u_date BETWEEN '20110101' AND '20110101' AND d8 = 3 AND d9 = 5
GROUP BY d2, d3, d4, d5

L'index cluster est utilisé et les autres sont ignorés malgré la sélectivité suffisante.

Q08 et Q09

SELECT SUM(avg_m1) / SUM(u_cnt), d2, d3, d4, d5 
FROM agg1 
WHERE u_date BETWEEN '20110101' AND '20110101' AND d9 = 3 AND d10 = 5
GROUP BY d2, d3, d4, d5

SELECT SUM(avg_m1) / SUM(u_cnt), d2, d3, d4, d5 
FROM agg1 WITH (INDEX(1)) 
WHERE u_date BETWEEN '20110101' AND '20110101' AND d9 = 3 AND d10 = 5
GROUP BY d2, d3, d4, d5

Enfin en Q08 le coût de l'intersection de deux sous-ensembles (plus petits) sur les colonnes plus sélectives dépasse le coût d'utilisation d'index cluster.

Mais si nous recommandons à l'optimiseur utiliser l'index cluster quand même, la performance ne dégrade pas.

Q10

SELECT SUM(avg_m1) / SUM(u_cnt), d2, d3, d4, d5
FROM agg1 
WHERE u_date BETWEEN '20110101' AND '20110101' AND d10 = 5
GROUP BY d2, d3, d4, d5

L'optimiseur ignore l'index plus sélective au profit d'index cluster. Également, il ignore de les utiliser ensemble.

Conclusions

L'utilisation des index simples n'est pas évidente en OLAP relationnel et doit être le sujet d'optimisation pointue par DBA. Cela explique aussi pourquoi l’assistant DTA ou l'avertissement "missing index" vous propose l'index composite avec des colonnes incluses.

Les éléments à prendre en considération :

• L'index cluster doit être le meilleur par les critères de séléctivité et d'utilisation ces colonnes dans WHERE des requêtes.
• Le moteur systématiquement refuse d'utiliser l'index cluster et les index supplémentaires ensemble ce qu'est expliqué par le coût élevé de l'intersection.
• Dans plusieurs cas l'analyse de table (cluster) ne sera pas moins performante ou même plus rapide que l'utilisation des 2 ou plusieurs index simples.
• Les index non-cluster augmente la taille de la BDD rapidement. Dans notre exemple, les index ont doublés la taille des tables.
• En cas de performance inacceptable des requêtes le passage vers les cubes OLAP (SSAS) est nécessaire