Requêtes très lentes malgré peu de données [Résolu]

[The zxeno prophet]

Bonjour j'aurais besoins d'aide sur des requêtes sur des tables contenant peu de données.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
SELECT
commande.NUM_COMMANDE,
client.INTITULE,
article.REF_ARTICLE,
ligne.DESIGNATION,
article.EPAISSEUR,
article.MATIERE,
ligne.QUANTITE,
commande.DATE_LIV,
article.DESSIN,
getNumImbrication(article.REF_ARTICLE, commande.NUM_COMMANDE) AS IMB,
getTotDecoupeCommande(article.REF_ARTICLE, commande.NUM_COMMANDE) AS `DEC`,
IF(article.ASOUSTRAITER = -1, getTotSousTraitRecep(article.REF_ARTICLE,commande.NUM_COMMANDE), 'N/A') AS ST,
getTotExp(article.REF_ARTICLE, commande.NUM_COMMANDE) AS Exp
FROM commande
INNER JOIN ligne ON commande.NUM_COMMANDE = ligne.NUM_COMMANDE
INNER JOIN article ON ligne.REF_ARTICLE = article.REF_ARTICLE
INNER JOIN client ON commande.NUM_CLIENT = client.NUM_CLIENT
WHERE (commandeFini(commande.NUM_COMMANDE) = 0)
ORDER BY commande.DATE_LIV DESC

Cette requête par exemple demande 2 à 3 sec pour être exécutée pour seulement 400 lignes ! (Sachant que ma plus grosse table dans cette requêtes contient 5000 lignes, on est quand même loin de la BDD gigantesque il me semble)

Le EXPLAIN me donne ceci

1, SIMPLE, commande, ALL, PRIMARY,COMMANDE0, , , , 157, Using where; Using filesort
1, SIMPLE, client, eq_ref, PRIMARY,CLIENT0, PRIMARY, 19, lp_ades.commande.NUM_CLIENT, 1,
1, SIMPLE, ligne, ref, LIGNE1,LIGNE2, LIGNE2, 12, lp_ades.commande.NUM_COMMANDE, 3, Using where
1, SIMPLE, article, eq_ref, PRIMARY,ARTICLE0, PRIMARY, 21, lp_ades.ligne.REF_ARTICLE, 1, Using where

Je ne suis pas un grand expert du EXPLAIN mais il me semble que c'est plutôt bon
Quelqu'un aurait il une piste ? Merci d'avance

EDIT:
Soluce sur toute la discussion en gros les fonctions qui font des requêtes qu'on utilise dans d'autres requêtes c'est pas bien

[CinePhil]

Ne serait-ce pas tes fonctions qui plombent l'exécution ?

[The zxeno prophet]

Envoyé par CinePhil

Ne serait-ce pas tes fonctions qui plombent l'exécution ?

Je ne suis pas au travail là tout de suite maintenant, je n'y donc pas accès, cependant ces fonctions ne font que des SUM, sur d'autre tables, ça peu gêner à ce point ?
Du coup je ferais l'essaie de la requête sans les fonctions histoires de voir ce que ça donne.
Merci

[CinePhil]

ces fonctions ne font que des SUM, sur d'autre tables, ça peu gêner à ce point ?

Au plus simple, une fonction contient donc une requête du type :

Code :

1
2
3
4
SELECT SUM(une_colonne)
FROM une_table
WHERE REF_ARTICLE = @num_article
  AND NUM_COMMANDE = @num_commande

Si la requête que tu présentes contient 100 lignes de résultats, le SGBD va devoir lancer 100 fois ces fonctions, donc lancer 100 autres requêtes.

Je pense qu'il vaudrait beaucoup mieux tout rassembler dans la grosse requête.
Ça te ferait une sous-requête avec tous les calculs lancés par tes fonctions, avec un GROUP BY sur REF_ARTICLE et NUM_COMMANDE puisque ce sont les paramètres de tes fonctions. Puis dans la requête principale, tu récupères les autres colonnes souhaitées à l'aide d'une jointure sur la sous-requête.

Essaie de la faire tout seul et si tu n'y arives pas, on t'aidera à la faire si tu nous donnes la structure des tables impliquées, le but rechercher et les règles de calcul + bien sûr la requête que tu auras essayée.

[The zxeno prophet]

Ah bordel à cul c'était vraiment les fonctions qui plombaient, il faut pardonner au développeur de client lourd que je suis qui a l'habitude d'écrire des fonctions partout >_>
bon j'ai écrit ça du coup

Code :

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
 
SELECT
commande.NUM_COMMANDE,
client.INTITULE,
article.REF_ARTICLE,
ligne.DESIGNATION,
article.EPAISSEUR,
article.MATIERE,
ligne.QUANTITE,
commande.DATE_LIV,
article.DESSIN,
IF(ISNULL(IMB.IMB),0,IMB.IMB) AS IMB,
IF(ISNULL(DECO.DECO),0,DECO.DECO) AS DECO,
IF(article.ASOUSTRAITER = -1,IF(ISNULL(SUM( STR.QteRecep)),0,SUM( STR.QteRecep)),'N/A') AS ST,
IF(ISNULL(SUM(ligneExpe.QteExp)),0,SUM(ligneExpe.QteExp)) AS EXP 
FROM commande
INNER JOIN ligne ON commande.NUM_COMMANDE = ligne.NUM_COMMANDE
INNER JOIN article ON ligne.REF_ARTICLE = article.REF_ARTICLE
INNER JOIN client ON commande.NUM_CLIENT = client.NUM_CLIENT
LEFT OUTER JOIN 
    (SELECT SUM(DecoupeTole.qteImberique) AS IMB, DecoupeTole.refDessin, DecoupeTole.refCommande
    FROM DecoupeTole
    INNER JOIN Tole ON Tole.REFERENCE = DecoupeTole.refTole 
    WHERE Tole.IMBRIQUER = 1
    GROUP BY DecoupeTole.refCommande, DecoupeTole.refDessin) AS IMB
ON ligne.NUM_COMMANDE = IMB.refCommande AND ligne.REF_ARTICLE = IMB.refDessin 
LEFT OUTER JOIN 
    (SELECT SUM(DecoupeTole.qteDecoupe) AS DECO, DecoupeTole.refDessin, DecoupeTole.refCommande
    FROM DecoupeTole
    INNER JOIN Tole ON Tole.REFERENCE = DecoupeTole.refTole 
    WHERE Tole.DECOUPE = 1
    GROUP BY DecoupeTole.refCommande, DecoupeTole.refDessin) AS DECO
ON ligne.NUM_COMMANDE = DECO.refCommande AND ligne.REF_ARTICLE = DECO.refDessin 
LEFT OUTER JOIN SousTraitanceCommandeLigneRecep AS STR ON ligne.ID_LIGNE = STR.ID_LIGNE
LEFT OUTER JOIN ligneExpe ON ligne.ID_LIGNE = ligneExpe.ID_LIGNE
GROUP BY commande.NUM_COMMANDE, article.REF_ARTICLE 
HAVING (ligne.QUANTITE - IF(ISNULL(SUM(ligneExpe.QteExp)),0,SUM(ligneExpe.QteExp)) > 0) 
ORDER BY commande.DATE_LIV DESC

Ça marche beaucoup mieux mais le EXPLAIN est moins tendre avec moi

Code :

1
2
3
4
5
6
7
8
9
10
11
12
'1', 'PRIMARY', '<derived2>', 'system', NULL, NULL, NULL, NULL, '0', 'const row not found'
'1', 'PRIMARY', '<derived3>', 'system', NULL, NULL, NULL, NULL, '0', 'const row not found'
'1', 'PRIMARY', 'commande', 'ALL', 'PRIMARY,COMMANDE0', NULL, NULL, NULL, '157', 'Using temporary; Using filesort'
'1', 'PRIMARY', 'client', 'eq_ref', 'PRIMARY,CLIENT0', 'PRIMARY', '19', 'lp_ades.commande.NUM_CLIENT', '1', ''
'1', 'PRIMARY', 'ligne', 'ref', 'LIGNE1,LIGNE2', 'LIGNE2', '12', 'lp_ades.commande.NUM_COMMANDE', '3', 'Using where'
'1', 'PRIMARY', 'STR', 'ref', 'STCLR1', 'STCLR1', '8', 'lp_ades.ligne.ID_LIGNE', '1', ''
'1', 'PRIMARY', 'ligneExpe', 'ref', 'LEX1', 'LEX1', '8', 'lp_ades.ligne.ID_LIGNE', '1', ''
'1', 'PRIMARY', 'article', 'eq_ref', 'PRIMARY,ARTICLE0', 'PRIMARY', '21', 'lp_ades.ligne.REF_ARTICLE', '1', ''
'3', 'DERIVED', 'DecoupeTole', 'ALL', 'PRIMARY,DT0', NULL, NULL, NULL, '6511', 'Using temporary; Using filesort'
'3', 'DERIVED', 'Tole', 'eq_ref', 'PRIMARY,TOLE0', 'PRIMARY', '257', 'lp_ades.DecoupeTole.refTole', '1', 'Using where'
'2', 'DERIVED', 'DecoupeTole', 'ALL', 'PRIMARY,DT0', NULL, NULL, NULL, '6511', 'Using temporary; Using filesort'
'2', 'DERIVED', 'Tole', 'eq_ref', 'PRIMARY,TOLE0', 'PRIMARY', '257', 'lp_ades.DecoupeTole.refTole', '1', 'Using where'

Ce qui me gène c'est ces deux ALL sur DecoupeTole alors qu'il y a des indexes (sur refCommande, refDessin et refTole bien-sur), le temps d'exécution est carrément meilleur de 3 secs je suis passé à 0.17 (c'est un petit plus raisonnable) du coup ces deux ALL c'est un choix de l'optimizer ou ma requête est mal écrite ?

[CinePhil]

1) Optimisation potentielle
Les sous-requêtes IMB et DECO ne pourraient-elles être rassemblées en une seule puisqu'elles sont construites pareil ?
Ou bien les deux conditions Tole.IMBRIQUER = 1 et Tole.DECOUPE = 1 sont-elles incompatibles ?

2) Données aléatoires dans le SELECT !
Comme tu groupes ta grosse requête seulement par commande.NUM_COMMANDE, article.REF_ARTICLE, les colonnes du SELECT qui ne font pas l'objet d'un calcul et qui ne sont pas en dépendance directe des colonnes du GROUP BY donneront des valeurs aléatoires ! Un autre SGBD que le trop permissif MySQL refuserait ta requête à cause de ça !
J'avais dit dans mon message qu'il fallait regrouper dans une sous-requête puis récupérer les autres colonnes par une jointure.

Au fait, un article pour une commande ne figure que dans une seule ligne non ? N'est-ce donc pas tout simplement un regroupement par ligne ?

3) COALESCE
Au lieu de IF(ISNULL(une_colonne, 0, quelquechose), il existe la fonction COALESCE qui retourne la première expression non nulle dans une liste :

Code :

1
2
3
4
5
6
7
-- Ceci :
IF(ISNULL(SUM(ligneExpe.QteExp)), 0, SUM(ligneExpe.QteExp)) AS EXP
 
-- devient cela :
COALESCE(SUM(ligneExpe.QteExp), 0) AS EXP
 
-- Plus simple non ?

4) Alias
Pour alléger la lecture de la requête, je préfère utiliser les alias partout.

Appliquons tout ça à ta requête, en commençant par le point 2 puisqu'il y a une interrogation sur le point 1.

Dans ton SELECT, les deux lignes suivantes contiennent des calculs :

Code :

1
2
    IF(article.ASOUSTRAITER = -1, IF(ISNULL(SUM( STR.QteRecep)), 0, SUM( STR.QteRecep)), 'N/A') AS ST,
    IF(ISNULL(SUM(ligneExpe.QteExp)), 0, SUM(ligneExpe.QteExp)) AS EXP

La sous-requête ne contiendra donc que les éléments de jointure nécessaires à ces lignes et aux colonnes du GROUP BY commande.NUM_COMMANDE, article.REF_ARTICLE

Ce qui donne ceci, avec le COALESCE et les alias :

Code :

1
2
3
4
5
6
7
8
9
10
11
FROM 
(
    SELECT l.ID_LIGNE, l.DESIGNATION, l.QUANTITE
        IF(a.ASOUSTRAITER = -1, COALESCE(SUM( STR.QteRecep), 0), 'N/A') AS ST,
        COALESCE(SUM(ligneExpe.QteExp), 0) AS EXP
    FROM ligne AS l
    INNER JOIN article AS a ON l.REF_ARTICLE = a.REF_ARTICLE
    LEFT OUTER JOIN SousTraitanceCommandeLigneRecep AS STR ON l.ID_LIGNE = STR.ID_LIGNE
    LEFT OUTER JOIN ligneExpe AS le ON l.ID_LIGNE = le.ID_LIGNE
    GROUP BY l.ID_LIGNE, l.DESIGNATION, l.QUANTITE
) AS tmp

Et la requête complète deviendrait, en laissant les deux sous-requête de calcul IMB et DECO :

Code :

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
SELECT 
    c.NUM_COMMANDE,
    cl.INTITULE,
    a1.REF_ARTICLE,
    tmp.DESIGNATION,
    a1.EPAISSEUR,
    a1.MATIERE,
    tmp.QUANTITE,
    c.DATE_LIV,
    article.DESSIN,
    COALESCE(IMB.IMB, 0) AS IMB,
    COALESCE(DECO.DECO, 0) AS DECO,
    tmp.ST,
    tmp.EXP
FROM 
(
    SELECT l.ID_LIGNE, l.DESIGNATION, l.QUANTITE
        IF(a.ASOUSTRAITER = -1, COALESCE(SUM( STR.QteRecep), 0), 'N/A') AS ST,
        COALESCE(SUM(ligneExpe.QteExp), 0) AS EXP
    FROM ligne AS l
    INNER JOIN article AS a ON l.REF_ARTICLE = a.REF_ARTICLE
    LEFT OUTER JOIN SousTraitanceCommandeLigneRecep AS STR ON l.ID_LIGNE = STR.ID_LIGNE
    LEFT OUTER JOIN ligneExpe AS le ON l.ID_LIGNE = le.ID_LIGNE
    GROUP BY l.ID_LIGNE, l.DESIGNATION, l.QUANTITE
) AS tmp
 
INNER JOIN commande AS c ON c.NUM_COMMANDE = tmp.NUM_COMMANDE
    INNER JOIN client AS cl ON cl.NUM_CLIENT = c.NUM_CLIENT
 
INNER JOIN article AS a1 ON a1.REF_ARTICLE = tmp.REF_ARTICLE
 
LEFT OUTER JOIN
(
    SELECT dt.refDessin, dt.refCommande
        SUM(dt.qteImberique) AS IMB,
    FROM DecoupeTole AS dt
    INNER JOIN Tole AS t ON t.REFERENCE = dt.refTole
    WHERE t.IMBRIQUER = 1
    GROUP BY dt.refCommande, dt.refDessin
) AS IMB
    ON tmp.NUM_COMMANDE = IMB.refCommande
    AND tmp.REF_ARTICLE = IMB.refDessin
 
LEFT OUTER JOIN 
(
    SELECT dt.refDessin, dt.refCommande
        SUM(dt.qteDecoupe) AS IMB,
    FROM DecoupeTole AS dt
    INNER JOIN Tole AS t ON t.REFERENCE = dt.refTole
    WHERE t.DECOUPE = 1
    GROUP BY dt.refCommande, dt.refDessin
) AS DECO
    ON tmp.NUM_COMMANDE = IMB.refCommande
    AND tmp.REF_ARTICLE = IMB.refDessin
ORDER BY c.DATE_LIV DESC

À essayer, en espérant que mes hypothèses sont justes.

[The zxeno prophet]

Envoyé par CinePhil

1) Optimisation potentielle
Les sous-requêtes IMB et DECO ne pourraient-elles être rassemblées en une seule puisqu'elles sont construites pareil ?
Ou bien les deux conditions Tole.IMBRIQUER = 1 et Tole.DECOUPE = 1 sont-elles incompatibles ?

Malheureusement oui, dans le contexte mon client fait de la découpe laser (avec des grosses machines super classes), dans ma modélisation la tole physiquement découpé ou le plan de découpe (appelé imbrication par le client) c'est le même objet à deux état différent

Envoyé par CinePhil

2) Données aléatoires dans le SELECT !
Comme tu groupes ta grosse requête seulement par commande.NUM_COMMANDE, article.REF_ARTICLE, les colonnes du SELECT qui ne font pas l'objet d'un calcul et qui ne sont pas en dépendance directe des colonnes du GROUP BY donneront des valeurs aléatoires ! Un autre SGBD que le trop permissif MySQL refuserait ta requête à cause de ça !
J'avais dit dans mon message qu'il fallait regrouper dans une sous-requête puis récupérer les autres colonnes par une jointure.

Au fait, un article pour une commande ne figure que dans une seule ligne non ? N'est-ce donc pas tout simplement un regroupement par ligne ?

Oui c'est vrai qu'un GROUPE BY par ligne marche aussi, mais du coup pour cette histoire de donnée aléatoire je ne comprends pas très bien, en fait mon
groupement m'assure d'une relation 1 à 1 pour chaque table, un client pour une commande et une ligne pour une paire commande/article, donc en théorie j'ai toujours le bon résultat, ou j'ai oublié mes cours de SQL à ce point ?

Envoyé par CinePhil

3) COALESCE
Au lieu de IF(ISNULL(une_colonne, 0, quelquechose), il existe la fonction COALESCE qui retourne la première expression non nulle dans une liste :

Code :

Je prends note, je ne connaissais pas merci

Envoyé par CinePhil

4) Alias
Pour alléger la lecture de la requête, je préfère utiliser les alias partout.

Appliquons tout ça à ta requête, en commençant par le point 2 puisqu'il y a une interrogation sur le point 1.

Dans ton SELECT, les deux lignes suivantes contiennent des calculs :

Code :

La sous-requête ne contiendra donc que les éléments de jointure nécessaires à ces lignes et aux colonnes du GROUP BY commande.NUM_COMMANDE, article.REF_ARTICLE

Ce qui donne ceci, avec le COALESCE et les alias :

Code :

J'utilise moi même les alias le plus souvent, mais j'utilise ces requêtes dans un générateur de site oueb, très sensible, qui m'a généré les requêtes de base sans alias, donc je préfère ne pas en rajouter

Je vais voir ce que je peux faire avec ta requête
Bon en gros trois fautes de frappes à corriger, ça marche aussi bien mais franchement j'ai du mal à voir l'intérêt

[CinePhil]

Envoyé par The zxeno prophet

Malheureusement oui, dans le contexte mon client fait de la découpe laser (avec des grosses machines super classes), dans ma modélisation la tole physiquement découpé ou le plan de découpe (appelé imbrication par le client) c'est le même objet à deux état différent

Donc j'ai bien fait de ne pas rassembler les deux sous-requêtes IMB et DECO.

Oui c'est vrai qu'un GROUPE BY par ligne marche aussi, mais du coup pour cette histoire de donnée aléatoire je ne comprends pas très bien, en fait mon
groupement m'assure d'une relation 1 à 1 pour chaque table, un client pour une commande et une ligne pour une paire commande/article, donc en théorie j'ai toujours le bon résultat, ou j'ai oublié mes cours de SQL à ce point ?

Ne connaissant pas le schéma de la BDD ni la signification exacte de toutes ces colonnes et tables, rien ne me laissait supposer qu'il pouvait n'y avoir qu'une seule ligne de résultat par ligne de la table "ligne" à l'issue des jointures.

Si tu as la table A
idA, colA1
1, toto
2, titi

Et la table B
idB, idA, colB1, colB2
1, 1, papa, 12
2, 1, maman, 8
3, 2, frérot, 5

Et que tu fais la requête suivante, autorisée par MySQL mais refusée par tout SGBDR normatif :

Code :

1
2
3
4
SELECT A.idA, A.colA1, MIN(B.colB2), B.colB1
FROM A
INNER JOIN B ON B.idA = A.idA
GROUP BY A.idA

Il est probable que tu aies pour résultat ceci :
A.idA, A.colA1, MIN(B.colB2), B.colB1
1, toto, 8, papa
2, titi, 5, frérot

Alors que la valeur MIN de B.colB2 est bien 8 mais que ça correspond à maman et non à papa.

Bon en gros trois fautes de frappes à corriger,

Quelles fautes de frappes ?

[The zxeno prophet]

Envoyé par CinePhil

Ne connaissant pas le schéma de la BDD ni la signification exacte de toutes ces colonnes et tables, rien ne me laissait supposer qu'il pouvait n'y avoir qu'une seule ligne de résultat par ligne de la table "ligne" à l'issue des jointures.

Si tu as la table A
idA, colA1
1, toto
2, titi

Et la table B
idB, idA, colB1, colB2
1, 1, papa, 12
2, 1, maman, 8
3, 2, frérot, 5

Et que tu fais la requête suivante, autorisée par MySQL mais refusée par tout SGBDR normatif :

Code :

Il est probable que tu aies pour résultat ceci :
A.idA, A.colA1, MIN(B.colB2), B.colB1
1, toto, 8, papa
2, titi, 5, frérot

Alors que la valeur MIN de B.colB2 est bien 8 mais que ça correspond à maman et non à papa.

Oui pardon j'aurais du mettre le schémas, mais du coup sur ton exemple là c'est clair

Envoyé par CinePhil

Quelles fautes de frappes ?

Une ou deux virgules manquantes, et un copié/collé un peu foireux pour la sous requête DECO

Mais du coup merci mon problème est résolu