Add new comment

Multiples index simples en OLAP (Décisionnel)

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.

Table Colonne Densité Sélectivité,

nb de lignes
Séléctivité,

%
facts d1 2,00E-07 5000000 50
  d2 2,00E-07 5000000 50
  d3 3,00E-07 3333333 33
  d4 4,00E-07 2500000 25
  d5 5,00E-07 2000000 20
  d6 7,00E-07 1428571 14
  d7 1,00E-06 1000000 10
  d8 1,50E-06 666666 6
  d9 2,00E-06 500000 5
  d10 2,50E-06 400000 4
         
agg1 d1 2,00E-07 4994443 50
  d2 2,00E-07 4994443 50
  d3 3,00E-07 3329629 33
  d4 4,00E-07 2497221 25
  d5 5,01E-07 1997777 20
  d6 7,01E-07 1426983 14
  d7 1,00E-06 998888 10
  d8 1,50E-06 665925 6
  d9 2,00E-06 499444 5
  d10 2,50E-06 399555 4
  u_date 3,70E-06 269969 2

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).

Table Requête CPU time, msec Elapsed time, msec Scan count Logical reads Physical reads Read-ahead reads Worktables Plan
facts Q01 2528 69042 3 112336 550 111877 0 Cluster index scan
  Q02 5351 88025 2 662070 1447 105418 0 Seek IX6, seek IX7, merge join, key lookup
  Q03 2698 66461 3 112311 570 111873 0 Cluster index scan (forced)
  Q04 1139 20739 6 32228 33 35981 2 Seek IX6, seek IX7, merge join, key lookup
  Q05 2745 67108 3 112311 517 111875 1 Cluster index scan (forced)
agg1 Q06 94 649 1 1996 12 1996 1 Cluster index seek
  Q07 31 507 1 1996 12 1996 0 Cluster index seek
  Q08 78 730 2 2913 6 1797 1 Seek IX9, seek IX10, merge join, key lookup
  Q09 62 674 1 1996 13 1996 0 Cluster index seek (forced)
  Q10 78 82 1 1996 13 1996 1 Cluster index seek

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
AttachmentSize
Package icon olapsimpleindexes.zip4.15 KB