Problème de MCD ou simplement de requête ?

Version imprimable

26/04/2013, 10h38
tavarlindar

Problème de MCD ou simplement de requête ?

Bonjour, :P

C'est en voulant résoudre un problème de performance de requête que je me pose la question de savoir si au départ, ce n'est pas un problème de conception.

Voici le problème rencontré

Je suis surpris par les propositions qui me sont faites : création d'une table emails, d'une table permissions.

Qu'en pensez vous ?

Voici le MCD tel que je le vois aujourd'hui :
http://dianamahe.free.fr/images/mcd_...erritoire1.png

En vous remerciant par avance,

Tavar
26/04/2013, 15h30
fsmrel

Et revoilà Tavar !

A mon avis, il n’est nul besoin de modifier votre MCD. Cela dit, je ne connais pas MySQL, mais si je devais utiliser par exemple DB2, je dirais que construire des vues dynamiquement est propre à disqualifier l’utilisation des index lors de l’exécution des SELECT.

Histoire d'y voir plus clair, pourriez-vous exécuter vos requêtes sans les vues ? Ou les remplacer par des vues statiques, en utilisant dans un 1er temps un user_id « en dur », histoire de mieux cerner l'origine du problème ?

Bonjour fsmrel,

Eh oui, me revoilà dans tous les sens du terme ou presque.
Tout d'abord merci de répondre présent ! Je vous prie de bien vouloir m'excuser pour ces délais de réponse, mais on ne fait pas toujours ce que l'on veut. Cette situation me met d'autant plus mal à l'aise que j'ai besoin d'aide et que je ne vous réponds pas en temps et en heure. Ceci est d'autant plus embarrassant lorsqu'on connait votre niveau d'expertise.

Ceci étant dit, voici les résultats des premiers tests :

CAS A - l'utilisateur PMA a accès à tous les contacts.

CAS A.1 On affiche les demandes sans critère spécifique : requête par défaut . L'utilisateur PMA a accès à tous les contacts. Ici CREATE OR REPLACE VIEW v_contact_PMA AS SELECT contact.* FROM `contact`.
v_contact_PMA = la table contact dans sa globalité.

Code:

1
2
3
4
5
6
7
8
9
10
11
SELECT d.id_demande, d.date_demande, site_wi.abreviation_site, c.nom, c.email1, statut_abonnement.statut_ab_court_anglais AS statut_news, d.status,d.source_lien,source_keyword, pays.pays_lib_anglais, c.commercial_id AS trigramme, d.num_dossier, d.type_prestation, c.category, c.cycle, d.commentaireWI AS commentaireWI,COUNT(*) AS nb 
FROM v_contact_PMA c INNER JOIN 
( SELECT contact_id, id_demande, date_demande, num_dossier, commentaireWI,site_source, status,source_lien,type_prestation FROM demande d3 INNER JOIN v_contact_PMA c ON d3.contact_id = c.id_contact ORDER BY d3.id_demande DESC LIMIT 0,20) AS d ON d.contact_id = c.id_contact 
INNER JOIN contact c2 ON c.email1 = c2.email1 
INNER JOIN demande d2 ON d2.contact_id = c2.id_contact 
LEFT JOIN commercial ON c.commercial_id = commercial.id_commercial 
LEFT JOIN pays ON c.pays = pays.id_pays 
LEFT JOIN site_wi ON d.site_source=site_wi.site_wi
 LEFT JOIN statut_abonnement ON c.statut_abonnement_id = statut_abonnement.id_statut_abonnement 
GROUP BY c.email1, d.id_demande 
ORDER BY id_demande DESC

Résultat : en moyenne 0,0052 secondes
Si on remplace la vue par sa table donc en remplaçant v_contact_PMA par contact, on aboutit à des résultats analogues.

CAS A.2 On affiche les demandes qui ne sont pas "BKE" .

Code:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT d.id_demande, d.date_demande, site_wi.abreviation_site, c.nom, c.email1, statut_abonnement.statut_ab_court_anglais AS statut_news, d.status,d.source_lien,source_keyword, pays.pays_lib_anglais, c.commercial_id AS trigramme, d.num_dossier, d.tag_suivi_direction,d.type_prestation, c.category, c.cycle, d.commentaireWI AS commentaireWI,COUNT(*) AS nb 
FROM v_contact_PMA c 
INNER JOIN ( SELECT contact_id, id_demande, date_demande, num_dossier, commentaireWI,tag_suivi_direction, site_source, status,source_lien,type_prestation FROM demande d3 ) AS d ON d.contact_id = c.id_contact 
INNER JOIN contact c2 ON c.email1 = c2.email1 
INNER JOIN demande d2 ON d2.contact_id = c2.id_contact
LEFT JOIN commercial ON c.commercial_id = commercial.id_commercial 
LEFT JOIN pays ON c.pays = pays.id_pays
LEFT JOIN site_wi ON d.site_source=site_wi.site_wi
LEFT JOIN statut_abonnement ON c.statut_abonnement_id = statut_abonnement.id_statut_abonnement
 WHERE 1 AND d.type_prestation !='BKE' 
GROUP BY c.email1, d.id_demande 
ORDER BY id_demande DESC LIMIT 0,20

résultat : 8,6584 secondes

ce qui correspond à :
http://dianamahe.free.fr/images/cas_explain_A2.png

idem si on remplace directement la vue par la table contact, on obtient des résultats analogue en terme de temps. (8,6198).

Cas A.3v : on affiche les 20 première demandes faites par des contacts dont le pays est la France. On utilise ici un critère associé au contact (le pays).

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT d.id_demande, d.date_demande, site_wi.abreviation_site, c.nom, c.email1, statut_abonnement.statut_ab_court_anglais AS statut_news, d.status, d.source_lien, source_keyword, pays.pays_lib_anglais, c.commercial_id AS trigramme, d.num_dossier, d.tag_suivi_direction, d.type_prestation, c.category, c.cycle, d.commentaireWI AS commentaireWI, COUNT( * ) AS nb
FROM v_contact_PMA c
INNER JOIN (

SELECT contact_id, id_demande, date_demande, num_dossier, commentaireWI, tag_suivi_direction, site_source, 
STATUS , source_lien, type_prestation
FROM demande d3
) AS d ON d.contact_id = c.id_contact
INNER JOIN contact c2 ON c.email1 = c2.email1
INNER JOIN demande d2 ON d2.contact_id = c2.id_contact
LEFT JOIN commercial ON c.commercial_id = commercial.id_commercial
LEFT JOIN pays ON c.pays = pays.id_pays
LEFT JOIN site_wi ON d.site_source = site_wi.site_wi
LEFT JOIN statut_abonnement ON c.statut_abonnement_id = statut_abonnement.id_statut_abonnement
WHERE 1 
AND c.pays = 'FR'
GROUP BY c.email1, d.id_demande
ORDER BY id_demande DESC 
LIMIT 0 , 20

résultat : 2,6221 secondes idem si on rempace al vue par la table, les résultats sont analogues.
ce qui correspond à :
http://dianamahe.free.fr/images/cas_explain_A3v.png

cas A4v : on utilise un critère de recherche associé à la demande autre que le type de prestation : le status .

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT d.id_demande, d.date_demande, site_wi.abreviation_site, c.nom, c.email1, statut_abonnement.statut_ab_court_anglais AS statut_news, d.status, d.source_lien, source_keyword, pays.pays_lib_anglais, c.commercial_id AS trigramme, d.num_dossier, d.tag_suivi_direction, d.type_prestation, c.category, c.cycle, d.commentaireWI AS commentaireWI, COUNT( * ) AS nb
FROM v_contact_PMA c
INNER JOIN (

SELECT contact_id, id_demande, date_demande, num_dossier, commentaireWI, tag_suivi_direction, site_source, 
STATUS , source_lien, type_prestation
FROM demande d3
) AS d ON d.contact_id = c.id_contact
INNER JOIN contact c2 ON c.email1 = c2.email1
INNER JOIN demande d2 ON d2.contact_id = c2.id_contact
LEFT JOIN commercial ON c.commercial_id = commercial.id_commercial
LEFT JOIN pays ON c.pays = pays.id_pays
LEFT JOIN site_wi ON d.site_source = site_wi.site_wi
LEFT JOIN statut_abonnement ON c.statut_abonnement_id = statut_abonnement.id_statut_abonnement
WHERE 1 
AND d.status = 'Pending'
GROUP BY c.email1, d.id_demande
ORDER BY id_demande DESC

Résultat : 1.0599
ce qui donne avec un explain :
http://dianamahe.free.fr/images/cas_explain_A4v.png

Cas A5v On fait une autre recherche sur le type de prestation :

Code:

1
2
3
4
5
6
7
8
9
10
SELECT d.id_demande, d.date_demande, site_wi.abreviation_site, c.nom, c.email1, statut_abonnement.statut_ab_court_anglais AS statut_news, d.status, d.source_lien, source_keyword, pays.pays_lib_anglais, c.commercial_id AS trigramme, d.num_dossier, d.tag_suivi_direction, d.type_prestation, c.category, c.cycle, d.commentaireWI AS commentaireWI, COUNT( * ) AS nb FROM v_contact_PMA c 
INNER JOIN ( SELECT contact_id, id_demande, date_demande, num_dossier, commentaireWI, tag_suivi_direction, site_source, STATUS , source_lien, type_prestation FROM demande d3 ) AS d ON d.contact_id = c.id_contact INNER JOIN contact c2 ON c.email1 = c2.email1 
INNER JOIN demande d2 ON d2.contact_id = c2.id_contact 
LEFT JOIN commercial ON c.commercial_id = commercial.id_commercial 
LEFT JOIN pays ON c.pays = pays.id_pays 
LEFT JOIN site_wi ON d.site_source = site_wi.site_wi 
LEFT JOIN statut_abonnement ON c.statut_abonnement_id = statut_abonnement.id_statut_abonnement 
WHERE 1 AND d.type_prestation = 'BKE' 
GROUP BY c.email1, d.id_demande 
ORDER BY id_demande DESC

Résultat (en moyenne) : 0,3332 secondes sachant qu'il y a que 6 demandes sur 46550 qui répondent au critère type_prestation = 'BKE'.

Cas A6v
Si on utilise type_prestation = 'B'.

Code:

1
2
3
4
5
6
7
8
9
10
SELECT d.id_demande, d.date_demande, site_wi.abreviation_site, c.nom, c.email1, statut_abonnement.statut_ab_court_anglais AS statut_news, d.status,d.source_lien,source_keyword, pays.pays_lib_anglais, c.commercial_id AS trigramme, d.num_dossier, d.tag_suivi_direction,d.type_prestation, c.category, c.cycle, d.commentaireWI AS commentaireWI,COUNT(*) AS nb 
FROM v_contact_PMA c INNER JOIN ( SELECT contact_id, id_demande, date_demande, num_dossier, commentaireWI,tag_suivi_direction, site_source, status,source_lien,type_prestation FROM demande d3 ) AS d ON d.contact_id = c.id_contact 
INNER JOIN contact c2 ON c.email1 = c2.email1 
INNER JOIN demande d2 ON d2.contact_id = c2.id_contact 
LEFT JOIN commercial ON c.commercial_id = commercial.id_commercial 
LEFT JOIN pays ON c.pays = pays.id_pays 
LEFT JOIN site_wi ON d.site_source=site_wi.site_wi 
LEFT JOIN statut_abonnement ON c.statut_abonnement_id = statut_abonnement.id_statut_abonnement 
WHERE 1 AND d.type_prestation='B' GROUP BY c.email1, d.id_demande ORDER BY id_demande DESC LIMIT 0,20

On a un résultat moyen de 4,7687

Voici un premier jet de test.

Bonsoir tavarlindar,

Ne vous désolez pas, je n'ai même pas vu le temps passer !

A propos du cas A.2 à 8,6584 sec. :

Il faudrait que vous fournissiez l’EXPLAIN du cas A.1 pour qu’on puisse comparer avec celui que vous avez fourni pour le cas A.2.

Je n’utilise pas MySQL Server, mais si j’interprète l’EXPLAIN de A.2, vous avez manifestement deux balayages de table, un 1er pour le SELECT emboîté et un 2e probablement pour la paire GROUP BY / ORDER BY (Type = 'ALL', 46550 lignes à chaque fois), ce qui est pour le moins pénalisant.

J’observe que dans le cas A.1 (0,0052 sec.), vous limitez le balayage à 20 lignes à l’occasion du 1er JOIN (ligne 3) :
Code:

1 2 3 4 5 SELECT contact_id, id_demande, date_demande, num_dossier, commentaireWI, site_source, status,source_lien,type_prestation FROM demande d3 INNER JOIN v_contact_PMA c ON d3.contact_id = c.id_contact ORDER BY d3.id_demande DESC LIMIT 0,20
Ceci explique cela : les opérations à suivre mettront peu de lignes en jeu, tandis que dans le cas A.2, vous n’avez pas défini de limite, d’où un handicap de 46550 lignes impliquées dans le SELECT ci-dessous et a priori un nombre de lignes du même ordre de grandeur (tout dépend du rendement du filtre sur 'BKE') pour le GROUP BY en fin de requête :
Code:

1 2 3 4 SELECT contact_id, id_demande, date_demande, num_dossier, commentaireWI, tag_suivi_direction, site_source, status,source_lien,type_prestation FROM demande d3
Quelle est la durée de ce simple SELECT ?

23/05/2013, 13h18
tavarlindar
Bonjour,

L'explain du cas 1 donne ceci :
http://dianamahe.free.fr/images/cas_explain_A1.png

Concernant le temps d'exécution de la requête
Code:

1 2 3 SELECT contact_id, id_demande, date_demande, num_dossier, commentaireWI, tag_suivi_direction, site_source, STATUS , source_lien, type_prestation FROM demande d3
on obtient en moyenne 0,0005 sec.
27/05/2013, 02h11
fsmrel

Bonsoir Tavar,

Appelons J1 la jointure des tables d3 et contact_PMA.

L’EXPLAIN du cas A1 montre que MySQL commence par effectuer cette jointure, manifestement rapide et donc non pénalisée par le temps de parcours de d3, puisque celui-ci est très court.

Pourrait-on savoir combien la table des contacts contient de lignes ?

La grande différence entre l’EXPLAIN du cas A.1 et les autres est quand même que dans le cas A.1, après avoir effectué la jointure J1, MySQL montre qu’il roule une « boule de neige » de très faible volume (20 rows), ce qui pour effectuer les 6 autres jointures est epsilon, alors que dans les autres cas il y a un handicap important : MySQL entame chaque jointure avec une boule de 46000 rows ou plus :(. Histoire d’en avoir le cœur net, quelle est la performance du cas A.1 quand on enlève la clause LIMIT ? (et tant qu’à faire, en enlevant la clause ORDER BY ?)
01/06/2013, 02h28
fsmrel

Bonsoir Tavar,

Allo, Allo ? Quelle nouvelle ?

Bonsoir fsmrel,

Citation:

Pourrait-on savoir combien la table des contacts contient de lignes ?

il y a 48 866 contacts.

Si on omet le LIMIT 0,20 en effectuant donc la requête :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT d.id_demande, d.date_demande, site_wi.abreviation_site, c.nom, c.email1, statut_abonnement.statut_ab_court_anglais AS statut_news, d.status, d.source_lien, source_keyword, pays.pays_lib_anglais, c.commercial_id AS trigramme, d.num_dossier, d.type_prestation, c.category, c.cycle, d.commentaireWI AS commentaireWI, COUNT( * ) AS nb
FROM v_contact_PMA c
INNER JOIN (
SELECT contact_id, id_demande, date_demande, num_dossier, commentaireWI, site_source,
STATUS , source_lien, type_prestation
FROM demande d3
INNER JOIN v_contact_PMA c ON d3.contact_id = c.id_contact
ORDER BY d3.id_demande DESC
) AS d ON d.contact_id = c.id_contact
INNER JOIN contact c2 ON c.email1 = c2.email1
INNER JOIN demande d2 ON d2.contact_id = c2.id_contact
LEFT JOIN commercial ON c.commercial_id = commercial.id_commercial
LEFT JOIN pays ON c.pays = pays.id_pays
LEFT JOIN site_wi ON d.site_source = site_wi.site_wi
LEFT JOIN statut_abonnement ON c.statut_abonnement_id = statut_abonnement.id_statut_abonnement
GROUP BY c.email1, d.id_demande
ORDER BY id_demande DESC

on aboutit à 8,4163 secondes.

si on omet en plus le order by, obtient donc :
http://dianamahe.free.fr/images/explain_fmrel_2.jpg
on avoisine le 9 secondes avec même des valeurs à 11 secondes !

Bonsoir Tavar,

Près de 50000 contacts à traiter sans clause LIMIT, c’est du batch ! Donc le temps de réponse est à la mesure. Cela dit :

1) Quel est le coût et le nombre de lignes récupérées pour le SELECT emboîté :

Code:

1
2
3
4
SELECT contact_id, id_demande, date_demande, num_dossier,
       commentaireWI, site_source,
       status, source_lien, type_prestation 
FROM demande d3 INNER JOIN v_contact_PMA c ON d3.contact_id = c.id_contact ;

Selon cette requête et vu le MCD, vous récupérez toutes les demandes, donc autant coder :

Code:

1
2
3
4
SELECT contact_id, id_demande, date_demande, num_dossier,
       commentaireWI, site_source,
       status, source_lien, type_prestation 
FROM demande ;

Ce qui n’interdit pas d’ajouter la clause LIMIT au besoin.

2) Coût + EXPLAIN et le nombre de lignes récupérées pour le SELECT :

Code:

1
2
3
4
5
6
7
8
9
10
SELECT d.id_demande, d.date_demande, c.nom, c.email1, 
       d.status,d.source_lien, 
       c.commercial_id AS trigramme, 
       d.num_dossier, d.type_prestation, c.category, c.cycle, 
       d.commentaireWI AS commentaireWI  
FROM v_contact_PMA c INNER JOIN (SELECT contact_id, id_demande, date_demande, num_dossier,
                                        commentaireWI, site_source, status,source_lien, type_prestation 
                                 FROM demande d3 INNER JOIN v_contact_PMA c ON d3.contact_id = c.id_contact 
                                ) AS d 
                           ON d.contact_id = c.id_contact ;

3) Coût + EXPLAIN et le nombre de lignes récupérées pour le SELECT (on évacue la jointure emboîtée inutile avec v_contact_PMA) :

Code:

1
2
3
4
5
6
SELECT d.id_demande, d.date_demande, c.nom, c.email1, 
       d.status,d.source_lien, 
       c.commercial_id AS trigramme, 
       d.num_dossier, d.type_prestation, c.category, c.cycle, 
       d.commentaireWI AS commentaireWI  
FROM v_contact_PMA c INNER JOIN demande d ON d.contact_id = c.id_contact ;

4) Même chose, tout en ajoutant la jointure sur email1 :

Code:

1
2
3
4
5
6
7
SELECT d.id_demande, d.date_demande, c.nom, c.email1, 
       d.status,d.source_lien, 
       c.commercial_id AS trigramme, 
       d.num_dossier, d.type_prestation, c.category, c.cycle, 
       d.commentaireWI AS commentaireWI  
FROM v_contact_PMA c INNER JOIN demande d ON d.contact_id = c.id_contact
                     INNER JOIN contact c2 ON c.email1 = c2.email1 ;

La dernière ligne pourrait être enrichie ainsi, histoire de gagner quelques lignes au résultat (y a-t-il un gain ?) :

Code:

1
2
3
4
5
6
7
SELECT d.id_demande, d.date_demande, c.nom, c.email1, 
       d.status,d.source_lien, 
       c.commercial_id AS trigramme, 
       d.num_dossier, d.type_prestation, c.category, c.cycle, 
       d.commentaireWI AS commentaireWI  
FROM v_contact_PMA c INNER JOIN demande d ON d.contact_id = c.id_contact
                     INNER JOIN contact c2 ON c.email1 = c2.email1 ;

A suivre...