Problème de performance, cherche à comprendre

**cfried** · 22/07/2013, 16h33

Bonjour,

J'ai une requête à optimiser ou du moins j'aimerais comprendre quelques petites choses qui me paraissent étranges.

J'ai pas mal de tables, de bases, de fonctions qui rentrent en jeu et je n'arrive pas à comprendre ce qui cloche.

J'ai cette requête :

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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
 
SELECT
		PDV_ID,
		PDV_LIB,
		PRODUIT_ID,
		PRODUIT_LIB,
		PRODUIT_REF_INTERNE,
		PRODUIT_CODE,
		PROD_ID,
		PROD_LIB,
		PROJET_ID,
		PROJET_CODE,
		CLIENT_ID,
		CLIENT_LIB,
		VEHICULE_ID,
		VEHICULE_LIB,
		PPAST.PASTILLAGE_ID,
		PPAST.PASTILLAGE_LIB,
		PP_DATE AS DATE_PASTILLAGE,
		ESSAI_ID,
		ESSAI_NAME,
		ESSAI_PREV,
		ESSAI_NO_DE,
		get_cr(ESSAI_ID) AS ESSAI_NO_CR,
		get_derog(ESSAI_ID) AS ESSAI_NO_DEROG,
		EE_REQUEST.EE_DATE as ESSAI_DATE_AJOUT,
		ESSAI_DATE_SOUHAITE,
		EE_START.EE_DATE as ESSAI_DATE_DEBUT,
		EE_REPORTED.EE_DATE as ESSAI_DATE_REALISE,
		ESSAI_CONFIDENTIEL,
		COMPOSANT_ID,
		COMPOSANT_REF,
		SOCIETE_LIB,
		CURRENT.ETAT_ID,
		CURRENT.ETAT_LIB,
		JALON_ID,
		JALON_LIB,
		RESULTAT_ID,
		RESULTAT_COM
		COND_ID,
		COND_LIB,
		EXIGENCE_ID,
		EXIGENCE_LIB, 
		GROUP_CONCAT(DISTINCT CONCAT_WS('/',CDC_LIB,CP_PAR) SEPARATOR 0x1D) AS LISTE_CDC_PAR,
		GROUP_CONCAT(DISTINCT CATEGORIE_ID SEPARATOR ';') AS LISTE_CAT_ID,
		GROUP_CONCAT(DISTINCT CATEGORIE_LIB SEPARATOR ' + ') AS LISTE_CATEGORIE,
		GROUP_CONCAT(DISTINCT CRITICITE_LIB SEPARATOR ' + ') AS LISTE_CRITICITE,
		GROUP_CONCAT(DISTINCT PROCEDURE_LIB SEPARATOR ' + ') AS LISTE_PROCEDURE,
		GROUP_CONCAT(DISTINCT OPERATEUR.MEMBRE_NOM SEPARATOR ' & ') AS LISTE_OPERATEUR,
		CONCAT(DEMANDEUR.MEMBRE_NOM, ' ',DEMANDEUR.MEMBRE_PRENOM) AS DEMANDEUR_NOM,
		CONCAT_WS (' - ', PRODUIT_LIB, COMPOSANT_LIB) AS PIECE,
		LABORATOIRE_ID,
		LABORATOIRE_LIB,
		TLAB_ID,
		TLAB_LIB,
		MOYEN_ID,
		MOYEN_LIB,
		get_avis(ESSAI_ID) AS AVIS_LIB
FROM CORP_PDV.ESSAI
INNER JOIN CORP_PDV.PDV ON pdv.PDV_ID = essai.ESSAI_PDV
INNER JOIN CORP_PDV.ESSAI_ETAT AS EE_CURRENT ON EE_CURRENT.EE_ESSAI = ESSAI_ID AND EE_CURRENT.EE_DATE=last_etat(ESSAI_ID) 
INNER JOIN CORP_PDV.ETAT AS CURRENT ON EE_CURRENT.EE_ETAT = CURRENT.ETAT_ID
 
INNER JOIN CORP_PDV.ESSAI_ETAT as EE_REQUEST ON EE_REQUEST.EE_ESSAI = ESSAI_ID AND EE_REQUEST.EE_ETAT=20
 
LEFT JOIN CORP_pdv.essai_piece ON essai_piece.EPIECE_ESSAI = essai.ESSAI_ID
LEFT JOIN CORP_pdv.piece ON piece.PIECE_ID = essai_piece.EPIECE_PIECE
 
LEFT JOIN CORP_pdv.produit_pastillage ON piece.PIECE_PPAST = produit_pastillage.PPAST_ID
LEFT JOIN CORP_PDV.PRODUIT ON produit.PRODUIT_ID = produit_pastillage.PP_PRODUIT
LEFT JOIN CORP_PDV.PASTILLAGE AS PPAST ON produit_pastillage.PP_PAST = PPAST.PASTILLAGE_ID
 
LEFT JOIN CORP_COMMON.PROJET_PRODUCT ON PRODUIT_PP = PP_ID
LEFT JOIN CORP_COMMON.PRODUCT ON PP_PROD = PROD_ID
LEFT JOIN CORP_COMMON.PROJET ON PP_PROJ = PROJET_ID
LEFT JOIN CORP_COMMON.VEHICULE_PROJ ON VP_PROJ = PROJET_ID
LEFT JOIN CORP_COMMON.VEHICULE ON VP_VEHICULE = VEHICULE_ID
LEFT JOIN CORP_COMMON.CLIENT ON VEHICULE_CLIENT = CLIENT_ID
 
LEFT JOIN CORP_PDV.ESSAI_ETAT as EE_START ON EE_START.EE_ESSAI = ESSAI_ID AND EE_START.EE_ETAT=50
LEFT JOIN CORP_PDV.ESSAI_ETAT as EE_REPORTED ON EE_REPORTED.EE_ESSAI = ESSAI_ID AND EE_REPORTED.EE_ETAT=70
 
LEFT JOIN CORP_pdv.comp_pastillage ON piece.PIECE_CPAST = comp_pastillage.CPAST_ID
LEFT JOIN CORP_pdv.composant ON comp_pastillage.CP_COMP = composant.COMPOSANT_ID
LEFT JOIN CORP_pdv.pastillage AS CPAST ON comp_pastillage.CP_PAST = CPAST.PASTILLAGE_ID
 
LEFT JOIN CORP_PDV.ESSAI_PAR ON EPAR_ESSAI = ESSAI_ID
LEFT JOIN CORP_PDV.CDC_PAR ON CP_ID = EPAR_PAR
LEFT JOIN CORP_PDV.CDC ON CP_CDC = CDC_ID
 
LEFT JOIN CORP_PDV.ESSAI_SOCIETE ON ES_ESSAI = ESSAI_ID
LEFT JOIN CORP_PDV.SOCIETE ON ES_SOCIETE = SOCIETE_ID
 
LEFT JOIN CORP_PDV.ESSAI_CATEGORIE ON ESSAI_ID = EC_ESSAI
LEFT JOIN CORP_PDV.CATEGORIE ON EC_CATEGORIE = CATEGORIE_ID
 
LEFT JOIN CORP_PDV.JALON ON ESSAI_JALON = JALON_ID
 
LEFT JOIN CORP_PDV.VALIDATION ON VALIDATION_ESSAI = ESSAI_ID
LEFT JOIN CORP_PDV.VALIDATION_COND ON VC_VALID = VALIDATION_ID
LEFT JOIN CORP_PDV.VALIDATION_EXIGENCE ON VE_VALID = VALIDATION_ID
LEFT JOIN CORP_PDV.VALIDATION_PROCEDURE ON VP_VALID = VALIDATION_ID
LEFT JOIN CORP_PDV.RESULTAT ON VALIDATION_RESULT = RESULTAT_ID
LEFT JOIN CORP_PDV.COND ON VC_COND = COND_ID
LEFT JOIN CORP_PDV.EXIGENCE ON VE_EXG = EXIGENCE_ID
LEFT JOIN CORP_PDV.`PROCEDURE` ON VP_PROC = PROCEDURE_ID
 
LEFT JOIN CORP_PDV.ESSAI_CRITICITE ON E_CRIT_ESSAI = ESSAI_ID
LEFT JOIN CORP_PDV.CRITICITE ON CRITICITE_ID = E_CRIT_CRITICITE
 
LEFT JOIN CORP_PDV.LABORATOIRE ON ESSAI_LABO = LABORATOIRE_ID
LEFT JOIN CORP_PDV.TYPE_LABO ON LABORATOIRE_ID = TLAB_ID
 
LEFT JOIN CORP_PDV.ESSAI_MOYEN ON EM_ESSAI = ESSAI_ID
LEFT JOIN CORP_PDV.MOYEN ON EM_MOYEN = MOYEN_ID
 
LEFT JOIN CORP_COMMON.MEMBRE AS DEMANDEUR ON ESSAI_CDV = DEMANDEUR.MEMBRE_ID
LEFT JOIN CORP_PDV.ESSAI_OPERATEUR ON EO_ESSAI = ESSAI_ID
LEFT JOIN CORP_COMMON.MEMBRE AS OPERATEUR ON EO_MEMBRE = OPERATEUR.MEMBRE_ID
 
WHERE ESSAI_NO_DE IS NOT NULL AND CURRENT.ETAT_ID BETWEEN 20 AND 29
GROUP BY ESSAI_ID
ORDER BY ESSAI_DATE_SOUHAITE DESC
LIMIT 0,20

et les fonctions :

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
 
CREATE DEFINER=`root`@`localhost` FUNCTION `get_avis`(`id` INT)
	RETURNS varchar(255)
	LANGUAGE SQL
	NOT DETERMINISTIC
	READS SQL DATA
	SQL SECURITY DEFINER
	COMMENT ''
BEGIN
	DECLARE ret VARCHAR(255) DEFAULT '';
	SELECT AVIS_LIB into ret 
		FROM FAURECIA_PDV.ESSAI_AVIS 
			INNER JOIN FAURECIA_PDV.AVIS 
				ON AVIS_ID = EA_AVIS 
		WHERE EA_ESSAI = id 
		ORDER BY EA_DATE DESC 
		LIMIT 1;
	RETURN ret;
CORP
 
 
--------------------------------
CREATE DEFINER=`root`@`localhost` FUNCTION `get_cr`(`id` INT)
	RETURNS varchar(255)
	LANGUAGE SQL
	DETERMINISTIC
	READS SQL DATA
	SQL SECURITY DEFINER
	COMMENT 'aa'
BEGIN
	DECLARE ret VARCHAR(255) DEFAULT '';
	SELECT ER_FILE into ret FROM ESSAI_RAPPORT WHERE ER_ESSAI = id ORDER BY ER_DATE DESC LIMIT 1;
	RETURN ret;
END
 
 
---------------------------------------
CREATE DEFINER=`root`@`localhost` FUNCTION `get_derog`(`id` INT)
	RETURNS varchar(255)
	LANGUAGE SQL
	NOT DETERMINISTIC
	READS SQL DATA
	SQL SECURITY DEFINER
	COMMENT ''
BEGIN
	DECLARE ret VARCHAR(255) DEFAULT '';
	SELECT ED_FILE into ret FROM ESSAI_DEROG WHERE ED_ESSAI = id;
	RETURN ret;
END
 
 
 
----------------------------------------------------
CREATE DEFINER=`root`@`localhost` FUNCTION `last_etat`(`ID_ESSAI` INT)
	RETURNS timestamp
	LANGUAGE SQL
	NOT DETERMINISTIC
	READS SQL DATA
	SQL SECURITY INVOKER
	COMMENT ''
BEGIN
	DECLARE max TIMESTAMP;
	SELECT MAX(EE_DATE) into max FROM ESSAI_ETAT WHERE EE_ESSAI = ID_ESSAI;
	RETURN max;
END

Le souci que j'ai et que je n'explique pas c'est que normalement, je devrais avoir des inner join au moins jusqu'au pastillage :

Code :

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

1
2
3
4
5
6
INNER JOIN faurecia_pdv.essai_piece ON essai_piece.EPIECE_ESSAI = essai.ESSAI_ID
INNER JOIN faurecia_pdv.piece ON piece.PIECE_ID = essai_piece.EPIECE_PIECE
 
INNER JOIN faurecia_pdv.produit_pastillage ON piece.PIECE_PPAST = produit_pastillage.PPAST_ID
INNER JOIN FAURECIA_PDV.PRODUIT ON produit.PRODUIT_ID = produit_pastillage.PP_PRODUIT
INNER JOIN FAURECIA_PDV.PASTILLAGE AS PPAST ON produit_pastillage.PP_PAST = PPAST.PASTILLAGE_ID

Mais quand je fais ça je multiplie par 3000 le temps d'exécution (passe de 0,016s à 3,230s)

Une idée ? Sachant que je pensais plus que ça fonctionnait dans le sens inverse ...

Je n'ai pas vu de différence notoire en faisant Explain des deux solutions.

Merci beaucoup !!

**punkoff** · 22/07/2013, 19h05

bonjour,

pour essayer de comprendre d'ou vient le problème il faudrai comparer les plans d'execution pour voir ce qui change ... Et connaitre un peu els volumétrie / index, etc..

En piste, changer petit à petit les left outer join en inner join pour voir sur quelle table il flanche.

**cfried** · 23/07/2013, 12h24

Bonjour et merci de la réponse et de vous intéresser au problème, je n'ai pas le temps de détailler tout de suite mais je le ferai prochainement et dans tous les cas je vous tiens au courant.

A plus tard.

Problème de performance, cherche à comprendre

Requêtes MySQL

Vue hybride

Discussions similaires

Partager

Partager