Discussion :

[dimainfo]

Bonjour à tous,
J'ai un probleme d'amelioration de performanaces d'une de mes requetes, le temps de traitement s'éleve à 2s.
Ci-dessous la requete :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT trk.TrkTypeID , trk.TrkID as TrkID, tst.TrkStatusTypeID, jb.StatusTypeID , trk.JobID  , 
jb.StartDate ,  trkm.DocumentType ,  trkm.InterchangeNb 
 
FROM tracking trk 
INNER JOIN TrackingStatusType tst on tst.TrkStatusTypeID=trk.TrkStatusTypeID 
INNER JOIN Job jb on trk.JobID = jb.JobID  
INNER JOIN _Interchange trkm ON trkm.TrkID=trk.TrkID  
WHERE trk.EnvID = 42   AND trk.TrkTypeID = 15  
Order BY  jb.StartDate DESC   LIMIT 20

la table tracking contient 775 615 enregistrements
la table TrackingStatusType contient 22 enregistrements
la table Job contient 17 millions enregistrements
la table _Interchange contient 315 000 enregistrements

[kolodz]

Peux-tu nous donner la listes des index existant pour les tables concernées par cette requête ?

Cordialement,
Patrick Kolodziejczyk.


EDIT :
Que retourne le plan d'exécution pour la requête ? :

Code :

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

1
2
3
4
5
6
7
8
9
EXPLAIN SELECT trk.TrkTypeID , trk.TrkID as TrkID, tst.TrkStatusTypeID, jb.StatusTypeID , trk.JobID  , 
jb.StartDate ,  trkm.DocumentType ,  trkm.InterchangeNb 
 
FROM tracking trk 
INNER JOIN TrackingStatusType tst on tst.TrkStatusTypeID=trk.TrkStatusTypeID 
INNER JOIN Job jb on trk.JobID = jb.JobID  
INNER JOIN _Interchange trkm ON trkm.TrkID=trk.TrkID  
WHERE trk.EnvID = 42   AND trk.TrkTypeID = 15  
Order BY  jb.StartDate DESC   LIMIT 20

Note : Le order by étant réalisé avant le limit, le coût de la requête est équivalant à la même requête sans le limit;
Combien de ligne retourne cette requête sans limit ?

Code :

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

1
2
3
4
5
6
SELECT count(*)
FROM tracking trk 
INNER JOIN TrackingStatusType tst on tst.TrkStatusTypeID=trk.TrkStatusTypeID 
INNER JOIN Job jb on trk.JobID = jb.JobID  
INNER JOIN _Interchange trkm ON trkm.TrkID=trk.TrkID  
WHERE trk.EnvID = 42   AND trk.TrkTypeID = 15

[Gaulouis]

Salut,

Peux tu nous retourner le résultat de :

Code :

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

mysql> describe tracking;

Code :

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

mysql> describe TrackingStatusType;

Code :

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

mysql> describe Job;

Code :

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

mysql> describe _Interchange;

Code :

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

1
2
3
4
5
6
7
8
9
mysql> describe SELECT trk.TrkTypeID , trk.TrkID as TrkID, tst.TrkStatusTypeID, jb.StatusTypeID , trk.JobID  , 
jb.StartDate ,  trkm.DocumentType ,  trkm.InterchangeNb 
 
FROM tracking trk 
INNER JOIN TrackingStatusType tst on tst.TrkStatusTypeID=trk.TrkStatusTypeID 
INNER JOIN Job jb on trk.JobID = jb.JobID  
INNER JOIN _Interchange trkm ON trkm.TrkID=trk.TrkID  
WHERE trk.EnvID = 42   AND trk.TrkTypeID = 15  
Order BY  jb.StartDate DESC   LIMIT 20;

Ca prend 2s, ok, mais sur quel machine ?

[dimainfo]

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
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
 
CREATE TABLE IF NOT EXISTS `_interchange` (
  `TrkID` int(10) unsigned NOT NULL,
  `SyntaxID` varchar(4) DEFAULT NULL,
  `SyntaxVersion` varchar(1) DEFAULT NULL,
  `TestID` varchar(1) DEFAULT NULL,
  `FileName` varchar(100) DEFAULT NULL,
  `MessageNb` int(10) DEFAULT NULL,
  `InterchangeDate` date DEFAULT NULL,
  `InterchangeNb` varchar(35) DEFAULT NULL,
  `SourceFile` varchar(70) DEFAULT NULL,
  `OutputFile` varchar(70) DEFAULT NULL,
  `Recipient` varchar(50) DEFAULT NULL,
  `Sender` varchar(50) DEFAULT NULL,
  `DocumentType` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`TrkID`),
  KEY `InterchangeDate` (`InterchangeDate`),
  KEY `InterchangeNb` (`InterchangeNb`),
  KEY `SourceFile` (`SourceFile`),
  KEY `OutputFile` (`OutputFile`),
  KEY `Recipient` (`Recipient`),
  KEY `Sender` (`Sender`),
  KEY `DocumentType` (`DocumentType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
CREATE TABLE IF NOT EXISTS `Tracking` (
  `TrkID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `TrkTypeID` smallint(5) unsigned NOT NULL,
  `JobID` int(10) unsigned NOT NULL,
  `Visible` tinyint(1) NOT NULL DEFAULT '1',
  `EnvID` int(5) unsigned NOT NULL,
  `TrkStatusTypeID` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `LockUserID` int(10) unsigned DEFAULT NULL,
  `CreationDate` datetime DEFAULT NULL,
  PRIMARY KEY (`TrkID`),
  KEY `TrkTypeID` (`TrkTypeID`),
  KEY `JobID` (`JobID`),
  KEY `TrkStatusTypeID` (`TrkStatusTypeID`),
  KEY `LockUserID` (`LockUserID`),
  KEY `EnvID_TrkTypeID` (`EnvID`,`TrkTypeID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1460347 ;
 
 
CREATE TABLE IF NOT EXISTS `TrackingStatusType` (
  `TrkStatusTypeID` mediumint(8) unsigned NOT NULL,
  `Alias` varchar(20) NOT NULL,
  `LabelID` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`TrkStatusTypeID`),
  KEY `LabelID` (`LabelID`),
  KEY `AliasID` (`Alias`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
CREATE TABLE IF NOT EXISTS `job` (
  `JobID` int(10) unsigned NOT NULL,
  `StartDate` datetime NOT NULL,
  `EndDate` datetime DEFAULT NULL,
  `Duration` int(10) unsigned DEFAULT NULL,
  `ParentJobID` int(10) unsigned DEFAULT NULL,
  `SessionJobID` int(10) unsigned DEFAULT NULL,
  `JobTypeID` smallint(5) unsigned NOT NULL,
  `StatusTypeID` mediumint(8) unsigned NOT NULL,
  `Visible` tinyint(1) NOT NULL,
  `EnvID` int(5) unsigned NOT NULL,
  `Description` text,
  PRIMARY KEY (`JobID`),
  KEY `ParentJobID` (`ParentJobID`),
  KEY `SessionJobID` (`SessionJobID`),
  KEY `JobTypeID` (`JobTypeID`),
  KEY `StatusTypeID` (`StatusTypeID`),
  KEY `EnvID` (`EnvID`),
  KEY `StartDate` (`StartDate`),
  KEY `Duration` (`Duration`),
  KEY `StartDate_DESC` (`StartDate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[dimainfo]

Le résultat d' explain de la requete.
Le résultat de la requete :

Code :

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

1
2
3
4
5
6
SELECT count(*)
FROM tracking trk 
INNER JOIN TrackingStatusType tst on tst.TrkStatusTypeID=trk.TrkStatusTypeID 
INNER JOIN Job jb on trk.JobID = jb.JobID  
INNER JOIN _Interchange trkm ON trkm.TrkID=trk.TrkID  
WHERE trk.EnvID = 42   AND trk.TrkTypeID = 15

est de 308 236

[kolodz]

Du coup, il a-t-il un index sur la table tracking pour les colonnes EnvID et TrkTypeID ?
Note : Peux-tu ajouter le nombre de ligne retournée par la requête ajouté dans mon édite de mon dernier message ?

[dimainfo]

@kolodz
EnvID_TrkTypeID est un index que j'avais rajouté sur la table tracking pour répondre à ma requete qui contient toujours ces 2 criteres.
Le nombre de la requete que vous m'avez demandé est sur mon dernier poste, merci de raffrachir le navigateur.

[kolodz]

A mon avis le temps perdu est lié à l'order by avant le limit. Car, le nombre d'entrées à trier est assez conséquence.

A confirmer avec une requête sans l'order by :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT trk.TrkTypeID , trk.TrkID as TrkID, tst.TrkStatusTypeID, jb.StatusTypeID , trk.JobID  , 
jb.StartDate ,  trkm.DocumentType ,  trkm.InterchangeNb 
 
FROM tracking trk 
INNER JOIN TrackingStatusType tst on tst.TrkStatusTypeID=trk.TrkStatusTypeID 
INNER JOIN Job jb on trk.JobID = jb.JobID  
INNER JOIN _Interchange trkm ON trkm.TrkID=trk.TrkID  
WHERE trk.EnvID = 42   AND trk.TrkTypeID = 15  
LIMIT 20;

[dimainfo]

@kolodz
Vous avez raison le order by est la source de cette lenteur par contre on ne peut NI limiter les résultats avant les trier parce que ma requete ne répondera plus au besoin de client NI la supprimer.
J'ai besoin d'une solution pour palier à ce probleme

[Gaulouis]

* Une table tampon de Job qui contient uniquement les 20 dernier StartData
Ensuite, si le client fait un next page (limit 20, 20) tu tape dans la table job (et la oui ca rame, par ce que ca cherche dans les archive)

x Tu pourrais aussi bricoler sur le fait que StartDate est image de l'auto-incremente de job.id (exit le order by), mais a mon avis le client créer des jobs et ensuite affect la date de départ donc ca pourrai bugger.

[kolodz]

Pas sûr que cela aide au niveau d'un order by, mais il y a un index spécifique sur cette colonne ?

Il n'est pas possible d'utiliser la colonne CreationDate de la table tracking à la place ?

D'après ce que j'ai compris de la requête on veux les 20 derniers tracking. Or, il me semble plus logique de se basé sur leur date de création que sur la date de début du job associé.
et en réalisant cette modification, le filtre et le tri ne se font plus que sur la table tracking ce qui est beaucoup plus facile à optimiser.

[dimainfo]

@Gaulouis
Table tampon je ne crois pas que ca marchera vu que nous devons avec les 22 derniers lignes en relation avec la table tracking et ça sera aussi couteux lors de l'insertion des jobs.
Sinon j'ai pas compris comment StartDate sera l'image de l'auto-increment ..

@kolodz
Oui il y'a déjà un index sur StartDate DESC.
La date de creation du tracking n'est pas celle du job.
Il nous faudra des informations qui existent dans la table job du coup on aura toujours besoin de mettre la relation avec tracking.

[kolodz]

@Gaulouis
Table tampon je ne crois pas que ca marchera vu que nous devons avec les 22 derniers lignes en relation avec la table tracking et ça sera aussi couteux lors de l'insertion des jobs.
Sinon j'ai pas compris comment StartDate sera l'image de l'auto-increment ..

L'idée est que les job sont crée de manière chronologique et donc que l'ordre des identifiants et le même que celui des startDate. C'est une hypothèse de travail qui n'est pas toujours vrai, mais qui simplifie l’ordonnancement, Car on se base seulement sur la table tracking pour cela.



Question sur les données retournées :
On a 775 615 entrées dans la table tracking et 17 millions dans la tables jobs. A-t-on une relation 1 vers 1 entre ces deux tables ou n vers 1 ?
Si c'est du n vers 1, il est peut-être possible de limité le nombre de résultat à trier par un moyen détourné.

Sinon, contenu du volume de données à trier, il est aussi possible d'envisager d'ajouter un filtre.
Peut-être est-il possible d’ignorer une partie des jobs ayant une date dé début inférieur à X. Cela réduirait le nombre de ligne à trier et donc le temps de la requête.

Cordialement,
Patrick Kolodziejczyk.

[dimainfo]

L'idée est que les job sont crée de manière chronologique et donc que l'ordre des identifiants et le même que celui des startDate. C'est une hypothèse de travail qui n'est pas toujours vrai, mais qui simplifie l’ordonnancement, Car on se base seulement sur la table tracking pour cela.

J'ai compris que effectivement l'ordre des identifiants est le meme que celui des StartDate, mais à quoi ça pourra m'aider, vous voulez dire que je faire un order by par l'identifiant au lieu de StartDate ?

Question sur les données retournées :
On a 775 615 entrées dans la table tracking et 17 millions dans la tables jobs. A-t-on une relation 1 vers 1 entre ces deux tables ou n vers 1 ?
Si c'est du n vers 1, il est peut-être possible de limité le nombre de résultat à trier par un moyen détourné.

Sinon, contenu du volume de données à trier, il est aussi possible d'envisager d'ajouter un filtre.
Peut-être est-il possible d’ignorer une partie des jobs ayant une date dé début inférieur à X. Cela réduirait le nombre de ligne à trier et donc le temps de la requête.

Cordialement,
Patrick Kolodziejczyk.

On a une relation n vers 1, d'ailleurs sur la table Tracking nous avons la clé etrangers JobID provenant de la table Job.

[kolodz]

Car, il est toujours plus facile de faire un filtre / trie sur une seule table. Même si le gain de performance reste à vérifier

Peux-tu vérifier les performances de cette requête :

Code :

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

1
2
3
4
5
6
7
8
SELECT trk.TrkTypeID , trk.TrkID as TrkID, tst.TrkStatusTypeID, jb.StatusTypeID , trk.JobID  , 
jb.StartDate ,  trkm.DocumentType ,  trkm.InterchangeNb 
FROM tracking trk 
INNER JOIN TrackingStatusType tst on tst.TrkStatusTypeID=trk.TrkStatusTypeID 
INNER JOIN Job jb on trk.JobID = jb.JobID  
INNER JOIN _Interchange trkm ON trkm.TrkID=trk.TrkID  
WHERE trk.EnvID = 42   AND trk.TrkTypeID = 15  
Order BY trk.JobID DESC LIMIT 20;

Requête uniquement sur la table tracking qui doit retourner les même lignes que la requête précédente :

Code :

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

1
2
3
4
SELECT trk.*
FROM tracking trk 
WHERE trk.EnvID = 42   AND trk.TrkTypeID = 15  
Order BY trk.JobID DESC LIMIT 20;

Note : Dans ce cas, il me semble que l'index le plus intéressant serait (EnvID,TrkTypeID,JobID). Car, la consultation de celui-ci devrait permettre à la base de données de retournée facilement les 20 premières lignes.

Cordialement,
Patrick Kolodziejcyzk.

[Gaulouis]

Ou peut être avec une procédure stocké

Code :

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

1
2
3
4
5
6
7
8
9
10
SET Job_ids = SELECT Job.ID FROM Job Order BY Job.StartDate LIMIT 10;
 
SELECT trk.TrkTypeID , trk.TrkID as TrkID, tst.TrkStatusTypeID, jb.StatusTypeID , trk.JobID  , 
jb.StartDate ,  trkm.DocumentType ,  trkm.InterchangeNb 
FROM tracking trk 
INNER JOIN TrackingStatusType tst on tst.TrkStatusTypeID=trk.TrkStatusTypeID 
INNER JOIN Job jb on trk.JobID = jb.JobID  
INNER JOIN _Interchange trkm ON trkm.TrkID=trk.TrkID  
WHERE trk.EnvID = 42   AND trk.TrkTypeID = 15  AND trk.JobID IN (@Job_ids)
Order BY jb.StartDate DESC LIMIT 20;

[dimainfo]

Car, il est toujours plus facile de faire un filtre / trie sur une seule table. Même si le gain de performance reste à vérifier

Peux-tu vérifier les performances de cette requête :

Code :

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

1
2
3
4
5
6
7
8
SELECT trk.TrkTypeID , trk.TrkID as TrkID, tst.TrkStatusTypeID, jb.StatusTypeID , trk.JobID  , 
jb.StartDate ,  trkm.DocumentType ,  trkm.InterchangeNb 
FROM tracking trk 
INNER JOIN TrackingStatusType tst on tst.TrkStatusTypeID=trk.TrkStatusTypeID 
INNER JOIN Job jb on trk.JobID = jb.JobID  
INNER JOIN _Interchange trkm ON trkm.TrkID=trk.TrkID  
WHERE trk.EnvID = 42   AND trk.TrkTypeID = 15  
Order BY trk.JobID DESC LIMIT 20;

La réponse est instantanée, et ça me retourne les meme résultats je vous remercie

Requête uniquement sur la table tracking qui doit retourner les même lignes que la requête précédente :

Code :

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

1
2
3
4
SELECT trk.*
FROM tracking trk 
WHERE trk.EnvID = 42   AND trk.TrkTypeID = 15  
Order BY trk.JobID DESC LIMIT 20;

Note : Dans ce cas, il me semble que l'index le plus intéressant serait (EnvID,TrkTypeID,JobID). Car, la consultation de celui-ci devrait permettre à la base de données de retournée facilement les 20 premières lignes.

Cordialement,
Patrick Kolodziejcyzk.

Et celle-ci aussi elle est instantanée.
Merci infiniement, je crois que c'est bon maintenant il faillait passer par une seule table au lieu de 2 et surtout que la 2eme qui Job est volumineuse.

[Gaulouis]

La discutions n'est pas marqué résolut ?

Order BY trk.JobID

Donc Job.StartDate n'est pas le paramètre qui dis que le Job est actife a partir de la date StartDate ? La colonne StartDate peut être modifier après l'enregistrement d'un Job ?

[dimainfo]

La discutions n'est pas marqué résolut ?



Donc Job.StartDate n'est pas le paramètre qui dis que le Job est actife a partir de la date StartDate ? La colonne StartDate peut être modifier après l'enregistrement d'un Job ?

Avant de marquer résolu je fais quelques tests tout d'abord.
job.StartDate est un attribut qui marque la date de creation du job, cette colonne ne peut en aucun etre modifié.