requête sql multi tables

requête sql multi tables - SQL/NoSQL - Programmation

Marsh Posté le 22-08-2011 à 12:47:10    

Bonjour à tous,
 
ci dessous, le shéma utilisé :
http://www.chronophage.com/img/Org.PNG
 
et là, la requête que je tente d'executer :
 

Code :
  1. SELECT          debiteurs.deb_logics            AS  ReferenceLogICS,
  2.                 debiteurs.deb_nom               AS  Debiteur,
  3.                 min(dossiers.doss_dateentree)   AS  DateEntree,
  4.                 sum(factures.fac_montant)       AS  Creance,
  5.                 sum(paiements.pai_montant)      AS  Reglements,
  6.                 max(paiements.pai_date)         AS  DernierReglement,
  7.                 count(paiements.pai_id)         AS  NbReglements
  8. FROM            debiteurs
  9. LEFT JOIN       dossiers    ON  debiteurs.deb_id = dossiers.deb_id
  10. LEFT JOIN       factures    ON  dossiers.doss_id = factures.doss_id
  11. LEFT JOIN       paiements   ON  dossiers.doss_id = paiements.doss_id
  12. GROUP BY        ReferenceLogICS,
  13.                 Debiteur
  14. ;


 
Le résultat est faux sur le champs Creance qui est multiplié par le nbre de ligne retourné par le champs NbReglements.  
il me faut utiliser des requêtes imbriquées, mais ca dépasse de loin mes maigres compétences en sql ... si quelqu'un peut m'aider ?!?  
 
Merci de votre aide !  

Reply

Marsh Posté le 22-08-2011 à 12:47:10   

Reply

Marsh Posté le 22-08-2011 à 18:49:30    

Salut
 
Si j'ai bien capté il faut que tu "isoles" les factures et les règlements entre eux c'est ca?
Moi j'dis qu'il faut séparer ta requete en deux et faire la jointure sur les deux parties.

SELECT j1.ReferenceLogICS, j1.Debiteur, j1.DateEntree, j2.Creance, j1.Reglements, j1.DernierReglement, j1.NbReglements
from (
    SELECT          debiteurs.deb_logics            AS  ReferenceLogICS,
                    debiteurs.deb_nom               AS  Debiteur,
                    min(dossiers.doss_dateentree)   AS  DateEntree,
                    sum(paiements.pai_montant)      AS  Reglements,
                    max(paiements.pai_date)         AS  DernierReglement,
                    count(paiements.pai_id)         AS  NbReglements
    FROM            debiteurs
    LEFT JOIN       dossiers    ON  debiteurs.deb_id = dossiers.deb_id
    LEFT JOIN       paiements   ON  dossiers.doss_id = paiements.doss_id
    GROUP BY        ReferenceLogICS,
                    Debiteur
    ) j1
join (  
    SELECT          debiteurs.deb_logics            AS  ReferenceLogICS,
                    debiteurs.deb_nom               AS  Debiteur,
                    sum(factures.fac_montant)       AS  Creance,
    FROM            debiteurs
    LEFT JOIN       dossiers    ON  debiteurs.deb_id = dossiers.deb_id
    LEFT JOIN       factures    ON  dossiers.doss_id = factures.doss_id
    GROUP BY        ReferenceLogICS,
                    Debiteur
    ) j2 on j1.ReferenceLogICS = j2.ReferenceLogICS and j1.Debiteur = j2.Debiteur;


Un truc dans le genre...


---------------
C'était vraiment très intéressant.
Reply

Marsh Posté le 23-08-2011 à 12:04:42    

Merci beaucoup ! cela semble fonctionner .... je valide cela sur un echantillon représentatif dans la journée.

Reply

Marsh Posté le 23-08-2011 à 14:24:43    

Bon, j'ai testé sur 690.000 debiteurs, et c'est parfait ... me reste plus qu'à depiauter ta solution pour en comprendre toute les subtilités.
 
Merci beaucoup !

Reply

Marsh Posté le 24-08-2011 à 01:55:52    

T'es le bienvenu.

 

C'est pas super subtil, suffit que tu regardes ce que te ramène ta requete initiale sans faire le GROUP BY pour que tu piges ce qui cloche:

SELECT          debiteurs.deb_logics            AS  ReferenceLogICS,
                debiteurs.deb_nom               AS  Debiteur,
    dossiers.doss_id, factures.*, paiements.*
FROM            debiteurs
LEFT JOIN       dossiers    ON  debiteurs.deb_id = dossiers.deb_id
LEFT JOIN       factures    ON  dossiers.doss_id = factures.doss_id
LEFT JOIN       paiements   ON  dossiers.doss_id = paiements.doss_id;


D'après ce que tu dis, tu vas voir que comme tu joins tout en meme temps, pour un dossier donné t'auras autant de lignes que de paiements, et la facture sera répétée à chaque ligne -> donc si tu fais la somme des factures forcément ca fait beaucoup trop.

 

Donc faut que tu isoles la jointure avec les paiements (1ere sous-requete) de celle des factures (2eme sous-requete).

 

Ensuite tu traites juste tes sous requetes comme si c'était des tables, et tu fais une requete qui joint les deux.


Message édité par lasnoufle le 24-08-2011 à 02:00:15

---------------
C'était vraiment très intéressant.
Reply

Marsh Posté le 24-08-2011 à 10:18:38    

Effectivement, j'avais bien cerné le problème, compris son origine, mais mes capacités en sql restent bien trop limités pour y remédier. Cela étant je me soigne (...comme je peux)
 
A ce propos, en extrapolant à partir de ta solution, si je souhaitais, par exemple, compliquer un peu le "bouzin" et ajouter le nombre de relances effectuées ainsi que la date de la dernière date de relance, je devrais faire un truc du genre :
 

Code :
  1. SELECT  j1.ReferenceLogICS,j1.Debiteur,j1.DateEntree,j1.Reglements,j1.DernierReglement,j1.NbReglements, 
  2.         j2.Creance,
  3.         j3.DerniereRelance,j3.NbRelances
  4. FROM (
  5.     SELECT          debiteurs.deb_logics            AS  ReferenceLogICS,
  6.                     debiteurs.deb_nom               AS  Debiteur,
  7.                     MIN(dossiers.doss_dateentree)   AS  DateEntree,
  8.                     SUM(paiements.pai_montant)      AS  Reglements,
  9.                     MAX(paiements.pai_date)         AS  DernierReglement,
  10.                     COUNT(paiements.pai_id)         AS  NbReglements
  11.     FROM            debiteurs
  12.     LEFT JOIN       dossiers    ON  debiteurs.deb_id = dossiers.deb_id
  13.     LEFT JOIN       paiements   ON  dossiers.doss_id = paiements.doss_id
  14.     GROUP BY        ReferenceLogICS,
  15.                     Debiteur
  16.     ) j1
  17. JOIN ( 
  18.     SELECT          debiteurs.deb_logics            AS  ReferenceLogICS,
  19.                     SUM(factures.fac_montant)       AS  Creance
  20.     FROM            debiteurs
  21.     LEFT JOIN       dossiers    ON  debiteurs.deb_id = dossiers.deb_id
  22.     LEFT JOIN       factures    ON  dossiers.doss_id = factures.doss_id
  23.     GROUP BY        ReferenceLogICS
  24.     ) j2 ON j1.ReferenceLogICS = j2.ReferenceLogICS
  25. JOIN ( 
  26.     SELECT          debiteurs.deb_logics            AS  ReferenceLogICS,
  27.                     MIN(relance.rel_date)           AS  DerniereRelance,
  28.                     COUNT(relance.rel_id)           AS  NbRelances
  29.     FROM            debiteurs
  30.     LEFT JOIN       dossiers    ON  debiteurs.deb_id = dossiers.deb_id
  31.     LEFT JOIN       relance     ON  dossiers.doss_id = relance.doss_id
  32.     GROUP BY        ReferenceLogICS
  33.     ) j3 ON j1.ReferenceLogICS = j3.ReferenceLogICS 
  34. ;

 
 
Mais alors là, la requête envoyés sous workbench met ~2 secondes à s'effectuer et ~55 MINUTES (!!!) à 'fetcher' ... J'imagine que créer 3 tables temporaires puis faire les jointures devient un peu lourd, mais peut être y'a t'il moyen d'optimiser cela ?
 


Message édité par someoneelseishere le 24-08-2011 à 10:20:12
Reply

Marsh Posté le 26-08-2011 à 21:11:36    

J'pense que ta requete a l'air bonne, après le temps d'exécution/fetch ca va dépendre de ton SGBD et de comment ton schéma est configuré. Je suis pas sur que tu puisses optimiser grand-chose de plus au niveau SQL.
 
Les deux secondes d'exécution et 55 minutes de fetch ca me semble louche, à mon avis c'est plus 2 secondes avant de commencer à fetcher, mais ca continue à exécuter au fur et à mesure que ca fetche.
 
Si t'avais été sous Oracle j'aurais dit que c'était un problème d'index qui fait que un ou plusieurs de tes joins prennent du temps, mais vu la capture d'écran du premier post je suppose que t'es pas sous Oracle.
 
Ce que tu peux faire pour voir si c'est potentiellement ca, c'est lancer chaque sous-requete seule pour voir si l'une d'entre elle met beaucoup de temps. Si c'est le cas, penches toi sur les index (ou l'équivalent de ton SGBD des indexs Oracle) des tables de cette sous-requete.
 
Si tes trois sous-requetes petent le feu et que c'est vraiment le fait de joindre les trois qui pose probleme, j'peux proposer de rajouter une table à ta base qui contiendra tes stats pour les dossiers: tu la remplis une fois pour l'initialiser avec tout ce qui est déjà en base (ca prendra autour de 55 minutes d'après ce que tu dis), puis tu l'updates à chaque fois qu'un dossier "bouge" (update juste de ce dossier bien sur).
 
Un peu bourrin mais ca sera imbattable en terme de performances: juste une requete sur ta table et hop!
 
Cela dit les 55 minutes que tu as me semblent beaucoup, meme pour 700000 débiteurs. Evidemment ca dépend des specs de ta machine et du nombre de dossiers par débiteur aussi...


---------------
C'était vraiment très intéressant.
Reply

Marsh Posté le 26-08-2011 à 21:55:52    

Bonsoir, merci d'avoir pris le temps de me répondre avec autant de précision.
 
Le SGBD est mysql et la machine un serveur Dell un peu vieillissant ...
 
Je teste requête par requête dès que je peux pour voir si une des requêtes cloche (ou plus précisément si mon indexation cloche !!!) et te tiens au courant.
 
S'agissant de ta solution "de contournement", on peut faire un INSERT sous MySQL avec une telle requête ?
 
Bon week end
 
SEIH

Reply

Marsh Posté le 27-08-2011 à 02:46:08    

Pour la solution de contournement, j'ai rarement touché MySql mais ce type de requete est dans la norme SQL et une rapide recherche sur Google a l'air de dire que c'est supporté. Cherches "MySQL create table select" sur Google pour créer la table directement depuis la requete, ou alors "MySQL insert select" si tu veux créer la table "proprement" toi-meme puis insérer les données plus tard.
 
A+


---------------
C'était vraiment très intéressant.
Reply

Marsh Posté le 30-08-2011 à 08:37:51    

Bon ben c'est confirmé, c'est bien la jointure des 4 sous requêtes qui est en cause ...  
Executées séparément, elles ne dépassent pas les 3 secondes avant affichage.
 
Je vais donc tester la table "de travail".

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed