Discussion :

[dimainfo]

Bonjour à tous,les amis j'ai un probleme de performances avec une base de données d'une application web de traçabilité qui peut atteindre des centaines de millions d'enregistrements. L'utilisateur peut créer des filtres qu'il peut les voir à chaque fois il est connecté, sauf que j'ai besoin de vos propositions pour ameliorer les performances pour l'affichage de ses filtres, mise en cache du filtre ? c'est pas possible car des enregistrements peuvent s'ajouter à la base à chaque seconde ... je vous remercie d'avance et pour vos propositions.

[sabotage]

Tu as un problème de performance sur l'utilisation du filtre ou sur l'affichage du résultat filtré ?

[dimainfo]

Sur l'affichage du résultat filtré.

[sabotage]

Les lenteurs de requête viennent souvent d'index manquant. Par exemple si tu fais des jointures entre veille a ce que les colonnes de liaison soient indexées.

Quel est ton SGDB ?

[dimainfo]

je ne crois pas que j'ai des problemes d'index, mon SGBD est Mysql. la récuperation d'un filtre implique à chaque fois l’interrogation des criteres de sélection et des colonnes à afficher depuis plusieurs tables et c'est pour celà que je suis entrain de réflechir à une methode de régler ça.

[Vil'Coyote]

j'aurais plus tendance à revoir le choix du SGBD que d'essayer d'améliorer ce qui n'est pas possible.

[Celira]

ça pourrait servir de connaitre le SGDB actuellement utilisé avant d'envisager de le changer

Il faut que tu analyses réellement où est le problème de perf : l'exécution de la requête ? la quantité de données récupérées ? le traitement des données de la requête ?

[sabotage]

Une mauvaise structure ou des mauvaises requêtes donneront de mauvais résultats quelque soit le moteur utilisé.

[grunk]

peut atteindre des centaines de millions d'enregistrements

On est plus dans le domaine du big data que de la base relationelle classique avec cet ordre de grandeur.

Après tu es très vague dans ta demande , donc difficile de t'aider.
Quel est la structure des tables ?
Quel quantité de données tu remonte à l'utilisateur ?
Quel type de requête fais tu ?
Quel sont les résultats du profiling de ton code : qu'est ce qui prend le plus de temps (le sgbd , le php qui traite derrière , etc ...)

[dimainfo]

J'ai un exemple d'une requête qui me pause problème, tout en sachant que les utilisateurs peuvent créer des filtres avec des données differentes alors je n'ai pas une requete type du moment que celle ci se construit suite à la demande et au parametrage de cet utilidateur.
voici ma requete qui s'execute sur 3secondes:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT DISTINCT trk.TrkTypeID , trk.TrkID as TrkID, tst.TrkStatusTypeID, jb.StatusTypeID , trk.JobID as 'Job'  , jb.StartDate as `Date_Création`  ,(CASE WHEN trkt.LabelID IS NULL THEN trkt.Alias ELSE (SELECT Fr FROM label WHERE LabelID = trkt.LabelID) END)  as `Document`  ,
(CASE WHEN tstlabel.LabelID IS NULL THEN tst.Alias ELSE tstlabel.Fr END)  as `Statut`
 
FROM tracking trk FORCE INDEX ( IDX_EnvID_TrkTypeID_JobID ) 
INNER JOIN TrackingStatusType tst on tst.TrkStatusTypeID=trk.TrkStatusTypeID 
INNER JOIN Job jb on trk.JobID = jb.JobID 
INNER JOIN trackingtype trkt on trkt.TrkTypeID=trk.TrkTypeID 
LEFT JOIN label tstlabel on tstlabel.LabelID=tst.LabelID 
INNER JOIN TrackingEnv trkenv ON trk.TrkID = trkenv.TrkID  
WHERE trkenv.EnvID IN ( 1,40,41,22,21,23,24,37,25,26,32,31,36,30,34,20,27,35,29,28,43,33,45,47,46,50,62,52,54,57,56,59,60,58,61,39,38,48,44,49,42,51 )  
AND trk.TrkTypeID IN ( 2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,19,20,21,22,23,24,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,47,48 )  
Order BY  trk.JobID DESC   LIMIT 20

[Celira]

Si c'est la requête qui prend du temps, et que tu ne peux utiliser de cache, il va falloir se tourner avec l'optimisation de base.
Est-ce que les filtres que peut choisir ton utilisateur sont limités (par exemple, on remonte toujours les mêmes colonnes des mêmes tables, mais en filtrant sur telle ou telle colonne avec telle ou telle valeur) ou est-ce qu'il peut quasiment construire la requête lui-même ?

[dimainfo]

Les filtres ne sont pas limité justement c'est à l'utilisateur de créer son propore filtre (colonnes, ordre d'affichage, ordre de tri...) c'est bien ce qui complique le travail.

[Artemus24]

Salut Dimainfo.

As-tu vérifié que tu as bien un index sur :
--> trkenv.EnvID
--> trk.TrkTypeID

Ne met pas ceci ' ou cela ` pour encadrer un nom d'alias.
Met plutôt un souligné (underscore) pour séparer tes mots comme par exemple Date_Création.


Je pense que ton problème concerne le premier cas, que j'ai réécris en faisant une nouvelle jointure. Mes ajouts sont en rouge.
A tester !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT DISTINCT trk.TrkTypeID,
                trk.TrkID            as TrkID,
                tst.TrkStatusTypeID,
                 jb.StatusTypeID,
                trk.JobID            as Job,
                 jb.StartDate        as Date_Création,
                 CASE WHEN     trkt.LabelID IS NULL THEN trkt.Alias ELSE trklabel.Fr END as Document,
                 CASE WHEN tstlabel.LabelID IS NULL THEN  tst.Alias ELSE tstlabel.Fr END as Statut
 
FROM tracking trk FORCE INDEX ( IDX_EnvID_TrkTypeID_JobID ) 
INNER JOIN TrackingStatusType as tst      on      tst.TrkStatusTypeID =  trk.TrkStatusTypeID 
INNER JOIN Job                as jb       on      trk.JobID           =   jb.JobID 
INNER JOIN trackingtype       as trkt     on     trkt.TrkTypeID       =  trk.TrkTypeID 
LEFT  JOIN label              as tstlabel on tstlabel.LabelID         =  tst.LabelID

LEFT  JOIN label              as trklabel on trklabel.labelID         = trkt.labelID

INNER JOIN TrackingEnv        as trkenv   on      trk.TrkID = trkenv.TrkID  
WHERE trkenv.EnvID     IN ( 1,40,41,22,21,23,24,37,25,26,32,31,36,30,34,20,27,35,29,28,43,33,45,47,46,50,62,52,54,57,56,59,60,58,61,39,38,48,44,49,42,51 )  
AND      trk.TrkTypeID IN ( 2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,19,20,21,22,23,24,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,47,48 )  
Order BY  trk.JobID DESC   LIMIT 20
;

@+

[dimainfo]

Salut Artemus24,
En effet oui j'ai bien les index trkenv.EnvID et trk.TrkTypeID et ta requête a le même temps de réponse que la mienne :/ aucun changement.

[kolodz]

Juste par curiosité :
La même requête sans le order by : Quel est le temps d’exécution ?
La même requête sans le order by ni le LIMIT 20 : Quel est le temps d’exécution ?

Quel est la volumétrie en entrées des différentes tables ?

Note : Pour ma part, pour les requêtes de sélection gourmande en ressources et qui retourne un petit nombre d'entrées. Je préfère faire les jointures de décoration dans une secondes requêtes. Pour éviter que la base de données charge les informations de ces tables en mémoire pendant la recherche. Avoir si les décorations ralentissent la recherche...

Cordialement,
Patrick Kolodziejczyk.

[dimainfo]

La même requête sans le order by : 0s
La même requête sans le order by ni le LIMIT 20 : 0s aussi.
La volumétrie :
Job : 43 373 874
Tracking : 107490
TrackingStatusType : 23
trackingtype : 43
label : 395
TrackingEnv : 109 603

Qu'est ce que vous voulez dire par les jointures de décorations ?

[kolodz]

On a donc une dégradation des performance dû à l'order by... Ou c'est un facteur aggravant.

Les jointures de décorations sont toutes les jointures qui ne sont pas utile pour la sélection de tes données. Elles sont juste la pour récupérer des informations supplémentaire.
Dans ton cas :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT * /* les éléments sélectionnes ne sont pas intéressant ici*/
FROM tracking trk FORCE INDEX ( IDX_EnvID_TrkTypeID_JobID ) 
INNER JOIN TrackingStatusType tst on tst.TrkStatusTypeID=trk.TrkStatusTypeID 
INNER JOIN Job jb on trk.JobID = jb.JobID /* Table non utilisé dans le WHERE, si base cohérente peut-être supprimer dans la requête de filtre*/
INNER JOIN trackingtype trkt on trkt.TrkTypeID=trk.TrkTypeID /* Table non utilisé dans le WHERE, si base cohérente peut-être supprimer dans la requête de filtre*/
LEFT JOIN label tstlabel on tstlabel.LabelID=tst.LabelID  /* Table non utilisé dans le WHERE, si base cohérente peut-être supprimer dans la requête de filtre*/
INNER JOIN TrackingEnv trkenv ON trk.TrkID = trkenv.TrkID  
WHERE trkenv.EnvID IN ( 1,40,41,22,21,23,24,37,25,26,32,31,36,30,34,20,27,35,29,28,43,33,45,47,46,50,62,52,54,57,56,59,60,58,61,39,38,48,44,49,42,51 )  
AND trk.TrkTypeID IN ( 2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,19,20,21,22,23,24,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,47,48 )  
Order BY  trk.JobID DESC   LIMIT 20

En prenant, juste les limitations auraient donc :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
SELECT trk.JobID /* les éléments sélectionnes ne sont pas intéressant ici*/
FROM tracking trk
INNER JOIN TrackingEnv trkenv ON trk.TrkID = trkenv.TrkID  
WHERE trkenv.EnvID IN ( 1,40,41,22,21,23,24,37,25,26,32,31,36,30,34,20,27,35,29,28,43,33,45,47,46,50,62,52,54,57,56,59,60,58,61,39,38,48,44,49,42,51 )  
AND trk.TrkTypeID IN ( 2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,19,20,21,22,23,24,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,47,48)
Order BY  trk.JobID DESC   LIMIT 20

A ce niveau, la requête doit être instantané. Les index qui sont nécessaire sont :
Sur la table tracking :
INDEX sur la colonne JobID
Si la base de données construit le résultat sur l'index tracking.JobID à partir du moment où celui-ci a les 20 premiers résultat, la requête est terminé. (Ce qui n'est pas le cas avec l'index forcé)
Dans le cas, de la requête avec l'index forcé, on oblige la base de données de parcourir l'ensemble des lignes, faire les jointures, faire un trie, puis filtrer.

Conseil général : on ne force jamais l'utilisation d'index sur une base de données. En particulier, quand la requête est généré.

Sur la table TrackingEnv :
INDEX sur la colonne TrkID

Note: Les filtre de type IN ne sont pas les plus rapides. Si il est possible de modifier celui-ci, il y a peut-être moyen de faire une amélioration.


Sur le résultat de cette requête, il ne reste plus qu'à faire les autres jointures. Dans, une requête basé sur le résultat de celle-ci.

Cordialement,
Patrick Kolodziejczyk.

[dimainfo]

Effectivement après l’enlèvement des autres attributs le temps de réponse passe de 3s à 0.5s ce qui est logique mais je ne sais pas comment faire pour construire une autre requete pour récupérer ces autres informations.
Pour la clause IN, je n'ai pas franchement d'idées comment l'améliorer !!

[kolodz]

Ajoute progressivement les colonnes/tables à la requête. Et indique nous à quel moment tu constate un dégradation net des performances.

Cordialement,
Patrick Kolodziejczyk.

[dimainfo]

Malheureusement après quelques tests je me rend compte que ce n'est pas le même résultat que j'obtiens des 2 requêtes avec et sans les informations complémentaires.