Requête SQL (problème vitesse de traitement)

**morpheuss** · 04/09/2017, 17h43

Bonjour,

Je suis débutant en php et sql, j'ai créé une requète qui fonctionne très bien mais sont résultat sort après 6 sec seulement.
Avez-vous une idée pour optimisé la vitesse ?

Voici ma requete:

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
SELECT e.id, e.initiale, e.nom, e.prenom, e.j1, e.j2, e.j3, e.j4, e.j5, c.nbconge, c.nbcongeanc, c.nbcongereport,
 
				(select SUM(l.minutes) FROM liste_hs as l
				WHERE l.initiale = e.Initiale and
				YEAR(date) = '2017' and MONTH(date) <= '9') as reportminutes,
 
				(select COUNT(*) FROM pointage as t
				WHERE t.initiale = e.Initiale and
				t.type = '6' and
				YEAR(datetimepointage) = '2017' and MONTH(datetimepointage) <= '9') as NbMaladie,
 
				(select COUNT(*) FROM pointage as t
				WHERE t.initiale = e.Initiale and
				t.type = '10' and
				YEAR(datetimepointage) = '2017' and MONTH(datetimepointage) <= '9') as NbCongePris,
 
				(select COUNT(*) FROM pointage as t2
				WHERE t2.initiale = e.Initiale and
				t2.type = '10' and
				YEAR(datetimepointage) = '2017' and MONTH(datetimepointage) = '9') as NbCongePrisCeMois
 
FROM employer as e 
LEFT JOIN conge as c ON c.id_employe = e.id and c.annee = '2017'
WHERE type = '1'
GROUP BY nom, prenom
ORDER BY nom, prenom

Pour info, si j'enlève les SUM & COUNT avec la requete ci-dessous, sont traitement prend 0.0034sec

Code :

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

1
2
3
4
5
6
SELECT e.id, e.initiale, e.nom, e.prenom, e.j1, e.j2, e.j3, e.j4, e.j5, c.nbconge, c.nbcongeanc, c.nbcongereport						
FROM employer as e 
LEFT JOIN conge as c ON c.id_employe = e.id and c.annee = '2017'
WHERE type = '1'
GROUP BY nom, prenom
ORDER BY nom, prenom

D'avance merci,

Steph

**al1_24** · 04/09/2017, 19h45

Les sous-requêtes scalaires sont rarement rapides.
Il vaut mieux déplacer les calculs dans des tables dérivées.
Par ailleurs, MONTH et YEAR retournent des valeurs numériques, autant éviter de les comparer avec des chaines de caractère.
Si la colonne type est aussi de type numérique, la même règle s'applique.

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
SELECT e.id, e.initiale, e.nom, e.prenom, e.j1, e.j2, e.j3, e.j4, e.j5, c.nbconge, c.nbcongeanc, c.nbcongereport,
                coalesce(r.reportminutes, 0)                    as reportminutes,
                case m.type when '6' then NbAbsence else 0 end  as NbMaladie,
                case m.type when '10' then NbAbsence else 0 end as NbCongePris,
                coalesce(p.NbCongePris, 0)                      as NbCongePrisCeMois 
FROM    employer    as e
    LEFT JOIN
        conge       as c
        ON  c.id_employe = e.id
        AND c.annee = '2017'
    LEFT JOIN
        (   select  l.initiale
                ,   SUM(l.minutes)  as reportminutes
            FROM    liste_hs as l
            WHERE   YEAR(date) = 2017
                and MONTH(date) <= 9
            GROUP BY l.initiale
        )   as r
        ON  r.initiale = e.Initiale
    LEFT JOIN
        (   select  t.initiale
                ,   t.type
                ,   COUNT(*) as NbAbsence
            FROM    pointage as t
            WHERE   t.initiale = e.Initiale
                and t.type in ('6', '10')
                and YEAR(datetimepointage) = 2017
                and MONTH(datetimepointage) <= 9
            GROUP BY t.initiale
                ,   t.type
        )   as m
        ON  m.initiale = e.Initiale
    LEFT JOIN
        (   select  t.initiale
                ,   COUNT(*) as NbCongePris
            FROM    pointage as t
            WHERE   t.initiale = e.Initiale
                and t.type = '10'
                and YEAR(datetimepointage) = 2017
                and MONTH(datetimepointage) = 9
            GROUP BY t.initiale
        )   as p
        ON  p.initiale = e.Initiale
WHERE   type = '1'
GROUP BY nom, prenom
ORDER BY nom, prenom

**morpheuss** · 04/09/2017, 21h04

Merci beaucoup pour votre aide.

J'ai tester vos modifications et j'ai les erreurs ci-dessous
Nom : C1.JPG
Affichages : 276
Taille : 126,2 Ko

Si j'enlève les lignes avec les 'case', j'ai cette erreur: "Champ 'e.initiale' inconnu dans where clause".
Nom : C2.JPG
Affichages : 275
Taille : 25,0 Ko

Je ne vois pas ou ce situe le problème.

D'avance merci

**al1_24** · 04/09/2017, 21h29

J'ai en effet oublié de retirer l'expression t.initiale = e.Initiale des deux dernières sous-requêtes.
En revanche, je suis étonné que MySQL ne connaisse pas CASE...

**morpheuss** · 05/09/2017, 09h01

Excellent, merci beaucoup, je gagne énormément de temps.
Dernier test, au lieu de 4.47sec je passe à 0.0502sec.

Et la fonction CASE fonctionne finalement ;-)

Voici ma requete finale

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
 
SELECT e.id, e.initiale, e.nom, e.prenom, e.j1, e.j2, e.j3, e.j4, e.j5, c.nbconge, c.nbcongeanc, c.nbcongereport,
                coalesce(r.reportminutes, 0)                    as reportminutes,
                case m.type when '6' then NbAbsence else 0 end  as NbMaladie,
                case m.type when '10' then NbAbsence else 0 end as NbCongePris,
                coalesce(p.NbCongePris, 0)                      as NbCongePrisCeMois 
FROM    employer    as e
    LEFT JOIN
        conge       as c
        ON  c.id_employe = e.id
        AND c.annee = '2017'
    LEFT JOIN
        (   select  l.initiale
                ,   SUM(l.minutes)  as reportminutes
            FROM    liste_hs as l
            WHERE   YEAR(date) = 2017
                and MONTH(date) <= 9
            GROUP BY l.initiale
        )   as r
        ON  r.initiale = e.Initiale
    LEFT JOIN
        (   select  t.initiale
                ,   t.type
                ,   COUNT(*) as NbAbsence
            FROM    pointage as t
            WHERE   t.type in ('6', '10')
                and YEAR(datetimepointage) = 2017
                and MONTH(datetimepointage) <= 9
            GROUP BY t.initiale
                ,   t.type
        )   as m
        ON  m.initiale = e.Initiale
    LEFT JOIN
        (   select  t.initiale
                ,   COUNT(*) as NbCongePris
            FROM    pointage as t
            WHERE   t.type = '10'
                and YEAR(datetimepointage) = 2017
                and MONTH(datetimepointage) = 9
            GROUP BY t.initiale
        )   as p
        ON  p.initiale = e.Initiale
WHERE   e.type = '1'
GROUP BY nom, prenom
ORDER BY nom, prenom

**CinePhil** · 05/09/2017, 09h36

Code :

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

GROUP BY nom, prenom

Pourquoi faire, ce GROUP BY ?
Il n'y a pas de fonction de calcul dans le SELECT.

**morpheuss** · 05/09/2017, 12h18

Si je ne fais pas le GROUP BY, j'ai des doublons et donc plusieurs fois les mêmes personnes affichées dans mon tableau.

**tbc92** · 05/09/2017, 12h58

Mettre un group by pour dédoublonner, quand on n'a pas de sum() ni de max() ou de fonctions du genre, ça permet de cacher une erreur. Mais ça ne corrige pas l'erreur.
Mysql accepte ta syntaxe, mais d'autres moteurs (par exemple Oracle) la refuseront.

Modifie ta requête ainsi :

Code :

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

1
2
3
4
5
6
7
8
9
 
SELECT e.id, e.initiale, e.nom, e.prenom, e.j1, e.j2, e.j3, e.j4, e.j5, c.nbconge, c.nbcongeanc, c.nbcongereport,
                coalesce(r.reportminutes, 0)                    as reportminutes,
                m.type
                case m.type when '6' then NbAbsence else 0 end  as NbMaladie,
                case m.type when '10' then NbAbsence else 0 end as NbCongePris,
                coalesce(p.NbCongePris, 0)                      as NbCongePrisCeMois 
FROM ...

et en enlevant le group by nom, prenom.

Tu vas voir que tu as 1 ou 2 lignes par individu, 1 pour le type 6, et 1 pour le type 10.

Ce que tu peux faire, c'est modifier la vue m :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
select  t.initiale
                ,   t.type
                ,   COUNT( case when t.type= '6' then 1 else null end ) as NbMaladie
                ,   COUNT( case when t.type= '10' then 1 else null end ) as NbCongesPris
            FROM    pointage as t
            WHERE   t.type in ('6', '10')
                and YEAR(datetimepointage) = 2017
                and MONTH(datetimepointage) <= 9
            GROUP BY t.initiale

Et du coup, tu peux faire cette requête finale , qui est beaucoup plus saine :

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
SELECT e.id, e.initiale, e.nom, e.prenom, e.j1, e.j2, e.j3, e.j4, e.j5, c.nbconge, c.nbcongeanc, c.nbcongereport,
                coalesce(r.reportminutes, 0)                    as reportminutes,
                m.NbMaladie,
                m.NbCongePris,
                coalesce(p.NbCongePris, 0)                      as NbCongePrisCeMois 
FROM    employer    as e
    LEFT JOIN
        conge       as c
        ON  c.id_employe = e.id
        AND c.annee = '2017'
    LEFT JOIN
        (   select  l.initiale
                ,   SUM(l.minutes)  as reportminutes
            FROM    liste_hs as l
            WHERE   YEAR(date) = 2017
                and MONTH(date) <= 9
            GROUP BY l.initiale
        )   as r
        ON  r.initiale = e.Initiale
    LEFT JOIN
        (    select  t.initiale
                ,   t.type
                ,   COUNT( case when t.type= '6' then 1 else null end ) as NbMaladie
                ,   COUNT( case when t.type= '10' then 1 else null end ) as NbCongesPris
            FROM    pointage as t
            WHERE   t.type in ('6', '10')
                and YEAR(datetimepointage) = 2017
                and MONTH(datetimepointage) <= 9
            GROUP BY t.initiale
        )   as m
        ON  m.initiale = e.Initiale
    LEFT JOIN
        (   select  t.initiale
                ,   COUNT(*) as NbCongePris
            FROM    pointage as t
            WHERE   t.type = '10'
                and YEAR(datetimepointage) = 2017
                and MONTH(datetimepointage) = 9
            GROUP BY t.initiale
        )   as p
        ON  p.initiale = e.Initiale
WHERE   e.type = '1'
 
ORDER BY nom, prenom

**morpheuss** · 05/09/2017, 14h31

Je viens de tester, en effet cette dernière requete fonctionne mais est légèrement plus lente.

Pensez-vous que le GROUP BY risque de créer un problème dans le futur de mon projet ?

D'avance merci pour votre aide

Steph

**CinePhil** · 05/09/2017, 15h06

Utiliser le GROUP BY sans fonction de regroupement dans le SELECT, c'est prendre le risque d'afficher un résultat faux. C'est un gros défaut de MySQL d'accepter ça.

Petit exemple...

Soit deux tables :
client (id, nom)
commande (id, id_client, objet, montant)

Peuplons les tables :
client (id, nom)
1, 'Dupont'
2, 'Durand'
3, 'Martin'

commande (id, id_client, objet, montant)
1, 1, 'Ordinateur portable', 500.00
2, 1, 'Ecran', 100.00
3, 2, 'Souris', 10.00
4, 1, 'Imprimante', 100.00
5, 3, 'Logiciel', 200.00

Faisons une jointure :

Code :

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

1
2
3
4
SELECT clt.id AS id_client, clt.nom, cmd.id AS id_commande, cmd.objet, cmd.montant
FROM client clt
INNER JOIN commande cmd ON cmd.id_client = clt.id
ORDER BY clt.id, cmd.id

Résultat :
id_client, nom, id_commande, objet, montant
1, 'Dupont', 1, 'Ordinateur portable', 500.00
1, 'Dupont', 2, 'Ecran', 100.00
1, 'Dupont', 4, 'Imprimante', 100.00
2, 'Durand', 3, 'Souris', 10.00
3, 'Martin', 5, 'Logiciel', 200.00

Ajoutons un GROUP BY :

Code :

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

1
2
3
4
5
SELECT clt.id AS id_client, clt.nom, cmd.id AS id_commande, cmd.objet, cmd.montant
FROM client clt
INNER JOIN commande cmd ON cmd.id_client = clt.id
GROUP BY clt.id
ORDER BY clt.id, cmd.id

Résultat :
id_client, nom, id_commande, objet, montant
1, 'Dupont', ici quelle commande va être choisie ?
2, 'Durand', 3, 'Souris', 10.00
3, 'Martin', 5, 'Logiciel', 200.00

**morpheuss** · 05/09/2017, 15h21

En effet; je vois bien le problème et je viens de vérifier cela cause bien problème dans mon cas. Merci pour l'explication limpide ;-)

Requête SQL (problème vitesse de traitement)

Requêtes MySQL

Discussions similaires

Partager

Partager