Optimisation de requête sous SQL Server

[Pitchoonet]

Bonjour,

Habituée d'Oracle, je découvre SQL-Server.
J'ai un modèle en étoile avec un table de fait d'environ 20 millions d'enregistrements et une centaine de colonnes qui sont des id de tables de référence. Ces tables de référence contiennent entre 2 et 10 enregistrements.
Je construit une requête du type :
Requête 1 :

Code :

1
2
3
4
5
6
7
8
9
SELECT TR1.libelle, 
         TR2.libelle, 
         count(DISTINCT TF.PrimaryKeyId)
FROM TF 
INNER JOIN TR1 ON (TF.TR1_ID=TR1.TR1_ID)
INNER JOIN TR2 ON (TF.TR2_ID=TR2.TR2_ID)
WHERE TF.TR3_ID=1
GROUP BY TR1.libelle, 
         TR2.libelle;

Requête 2 :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT TR1.libelle, 
         TR2.libelle, 
         TR4.libelle, 
         count(DISTINCT TF.PrimaryKeyId)
FROM TF 
INNER JOIN TR1 ON (TF.TR1_ID=TR1.TR1_ID)
INNER JOIN TR2 ON (TF.TR2_ID=TR2.TR2_ID)
INNER JOIN TR4 ON (TF.TR4_ID=TR4.TR4_ID)
WHERE TF.TR3_ID=1
GROUP BY TR1.libelle, 
         TR2.libelle, 
         TR4.libelle;

A chaque libelle de table de référence que j'ajoute dans ma requête, il s'ajoute 2mn30 au temps d'exécution.
Ainsi la requête 2 met 2mn30 de plus que la requête 1.

Dans Oracle, l'ajout de libellés de tables de référence dans le SELECT n'a aucune influence sur les temps d'exécution ?! Donc je cherche à comprendre pourquoi c'est différent sous SQL-Server.

J'ai testé la requête sans jointure et les performances sont bonnes.
Ce sont donc les jointures avec les tables de référence de qqs lignes qui ralentissent fortement le temps d'exécution.
Pouvez-vous m'expliquer pourquoi ? Et comment faire pour que ce ne soit pas le cas ?

Avec mes remerciements,
Pitchoonet

[SQLpro]

C'est un problématique de star join. Quelle version /édition de SQL Server ? (2005, 20088 enterpriss, standard.... ?
base relationnelle ou décisionnelle. ???
Évidemment ce serait mieux si c'était déjà un cube décisionnel !

A +

[elsuket]

Bonjour,

J'ai un modèle en étoile avec un table de fait d'environ 20 millions d'enregistrements et une centaine de colonnes qui sont des id de tables de référence

Vous confondez modèle relationnel et analytique.

Dans Oracle, l'ajout de libellés de tables de référence dans le SELECT n'a aucune influence sur les temps d'exécution?! Donc je cherche à comprendre pourquoi c'est différent sous SQL Server.

Ça me paraît un peu gros ...
Je suppose que vous avez les mêmes structures de table, mais avez-vous exactement les mêmes index ?

Si c'est le cas, il vous faut voir d'où l'activité disque provient, à l'aide de l'option de session SET STATISTICS IO ON :

Code :

1
2
3
4
SET STATISTICS IO ON
GO
 
script ...

Vous trouverez le nombre de pages lues par table dans l'onglet Messages de SQL Server Management Studio (SSMS) après exécution de la requête.

Prenez aussi le plan de requête réel, qui vous décrira ce que fait SQL Server.
Pour cela, il vous suffit d'activer son exposition :



Il sera affiché dans un nouvel onglet nommé Execution Plan.
Vous pouvez le sauvegarder en cliquant-droit sur celui-ci, et le poster ici ensuite (si cela vous est autorisé )

@++

[Pitchoonet]

Merci pour votre aide.

Version :
Microsoft SQL Server Enterprise Edition (64-bit) 2008 R2

Base de données relationnelle

Pitchoonet

[TheGuepard]

bonjour,
En plus de ce que vous a dit elsuket essayer de modifiant la requête comme ci-dessous, et comparer les plan d'executions avec votre requête d'origine.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT 
MIN(TR1.libelle) AS TR1_Libelle, 
MIN(TR2.libelle) AS TR2_Libelle, 
MIN(TR4.libelle) AS TR4_Libelle, 
count(DISTINCT TF.PrimaryKeyId)
FROM TF 
INNER JOIN TR1 ON (TF.TR1_ID=TR1.TR1_ID)
INNER JOIN TR2 ON (TF.TR2_ID=TR2.TR2_ID)
INNER JOIN TR4 ON (TF.TR4_ID=TR4.TR4_ID)
WHERE TF.TR3_ID=1
GROUP BY 
TF.TR1_ID, 
TF.TR2_ID, 
TF.TR4_ID;

[Pitchoonet]

Grâce à votre aide, j'ai pu identifier 2 facteurs de ralentissement de ma requête :
- le passage de 9 à 10 jointures avec des tables de référence : ma requête passe de 1mn30 à 8mn. J'ai essayer avec plusieurs jointures différentes afin d’identifier si le facteur discriminant était le nombre de jointures ou la volumétrie ramenée et j'obtiens donc 1mn30 d'exécution en ramenant 230 000 lignes et 8mn en ramenant 130 000 lignes.
Svp, existe-t-il une limitation de ce type à votre connaissance?

- l'utilisation d'un case dans ma fonction d'agrégation : count(distinct case when TF.TR_ID=1 then TF.PrimaryKeyId end)

J'imagine que le second facteur discriminant doit pouvoir se résoudre avec des indexs.
Svp, avez-vous des recos?

Avec mes remerciements,
Pitchoonet

[Waldar]

Il faudrait voir la structure de façon un peu plus globale, déjà est-ce que la centaine de colonnes se justifie totalement dans la table de faits ?

C'est beaucoup cent colonnes, j'espère que vous n'avez pas construit une matrice creuse pour coller au modèle en étoile.

Votre table de faits est-elle construite avec une clef primaire clustered ou non-clustered ?
Dans ce cas-ci, vu que l'accès aux données ne se fait pas sur la clef primaire, j'opterais pour une non-clustered, au contraire de toutes vos tables de références.

Est-ce que vos colonnes de références sont toutes indexées ?
Est-ce que vos relations sont correctement définies par des clefs étrangères ?
Est-ce que vous avez essayé votre requête avec des index composites ?

Si la requête que vous avez présenté au départ est représentative de votre vraie requête, le distinct dans le count est inutile.
Pour le case à l'intérieur de celui-ci, je ne sais pas, ça dépend de vos données et de ce que vous essayez d'en extirper.

[iberserk]

le passage de 9 à 10 jointures avec des tables de référence : ma requête passe de 1mn30 à 8mn

Postez le plan d’exécution...

Sur le plan d’exécution estimé, le nombre de lignes qu'il estime traiter est'il cohérent avec le nombre réel de lignes?
J'ai eu le soucis sur une requête avec de multiples LEFT JOIN... il manquait une statistiques sur un couple de colonnes... je suis ainsi passé de 5mn... à 3secondes :-)
Un coups d'oeil de ce côté là ne fera pas de mal...

[mikedavem]

J'ai eu le soucis sur une requête avec de multiples LEFT JOIN... il manquait une statistiques sur un couple de colonnes... je suis ainsi passé de 5mn... à 3secondes :-)
Un coups d'oeil de ce côté là ne fera pas de mal...

Ah tiens ca me rappelle qqch

[iberserk]

Ah tiens ca me rappelle qqch

A toi aussi

Une épidémie surement

[elsuket]

Effectivement, la Thaïlande étant également touchée ...
Et c'est probablement pas le seul pays

@++

[Pitchoonet]

Ce n'est pas moi qui ai construit le modèle et il est trop tard pour le modifier.
Je fais donc avec ce dont j'hérite...
Concernant les indexs de PK, ils sont en cluster. Je vais essayer de le passer en non cluster sur la table de fait.
A ce sujet, est-il possible de créer une contrainte de PK sans l'index sous SQL Server?
Pour l'histoire des statistiques, svp pouvez-vous m'indiquer comment les voir?

[iberserk]

Concernant les indexs de PK, ils sont en cluster. Je vais essayer de le passer en non cluster sur la table de fait.

?

A ce sujet, est-il possible de créer une contrainte de PK sans l'index sous SQL Server?

Non mais en quoi l'index vous gène t'il?

Pour l'histoire des statistiques, svp pouvez-vous m'indiquer comment les voir?

Code :

1
2
3
SELECT * 
FROM sys.stats 
WHERE object_id=OBJECT_ID(NOMDEVOTRETABLE)

Mais plus visuellement dans SSMS au niveau de votre table vous avez un node 'statistiques'

[iberserk]

Mais le mieu est vraiment de nous fournir le plan d'execution comme vous l'a expliqué elsuket

[Pitchoonet]

Voici les plans d'exécutions.

Avec mes remerciements,
Pitchoonet

[iberserk]

Voici les plans d'exécutions.

Déjà les requètes ne sont pas équivalentes... j'en veux pour preuve le group by...

Il vous manque également des index... SQL SERVER (est'il gentil tout de même) vous en propose dailleurs...


commencez par harmoniser vos Requètes et creez l'index manquant (profitez en pour aliaser vos tables!!! c'est illisible)

[Pitchoonet]

La proposition de SQL Server (il est gentil:-)) est de créer un index avec inclusion de toutes les colonnes de mon select...
C'est une solution trop spécifique pour être acceptable dans le contexte de mon projet...

[mikedavem]

Effectivement créer un index avec l'ensemble des colonnes devient abérant. Cependant la plus grosse consommation de votre requête vient de la table TF_COMPTE.

Que pouvez vous nous dire sur la cardinalité de valeurs de la colonne TF_COMPTE.EDITEUR_TF1_FR_ID ?

Quelle est la proportion de valeurs <> 0 dans cette table ?

Code :

TF1BDDCiblage.Contact.TF_COMPTE.EDITEUR_TF1_FR_ID <> 0

Est ce que l'ajout d'un index non cluster sur cette colonne est envisageable ?

++

[Pitchoonet]

La colonne EDITEUR_TF1_FR_ID est de type bit.
Voici la répartition des lignes dans la table TF_COMPTE:
0 10207666
1 7295917

J'ai déjà créé un index non cluster sur cette colonne. C'est ma seule condition de clause WHERE et donc mon premier réflexe.
Mais le plan d'exécution ne l'utilise pas...
Toute proposition de création d'index est la bienvenue.
Svp, y a-t-il l'équivalent des indexs bitmap en SQL Server?

Avec mes remerciements,
Pitchoonet

[Waldar]

C'est normal que l'optimiseur choisisse de ne pas utiliser l'index, il n'est absolument pas discriminant.

Si ce critère revient dans beaucoup de vos requêtes vous gagneriez à partitionner votre table sur cette colonne.