Appel aux dieux du SQL, à vos claviers ! ( optimisation requête... )

Appel aux dieux du SQL, à vos claviers ! ( optimisation requête... ) - SQL/NoSQL - Programmation

Marsh Posté le 15-05-2012 à 16:19:22    

Bonjour à tous,
 
Je vous solicite, dieux du SQL, ou pas, pour m'aider à optimiser, une méga requête XXL... J'ai essayé mais sans grand résultats....
Toute aide est la bien venu bien entendus... JE sèche vraiment la dessus, et en production ça commence à geuler car c'est trop long ( 1h30min d'attente pour exporter un rapport .csv ) et je les comprend... Le petit problème c'est que je suis arrivé il y a 6 mois... et que le SQL je le connais a peine aussi depuis 6 mois... et je dois bosser sur cette requette qui met trop de temps à être executé...  
Pour un Petit projet, la Base met 6 secondes à me sortir 29lignes... c'est déja beaucoup trop...
 
HELPPPPP  :(  Immense merci d'avance !

Code :
  1. SELECT     
  2.                                                    out_miss_numero,
  3.    out_miss_id,
  4.    out_miss_proj,
  5.    out_proj_numero,
  6.    out_sous_projet_id,
  7.                                                    out_sous_projet_name,
  8.    out_cand_consult,
  9.    out_cand_bu,
  10.    bcg_civ_lib,
  11.    out_cand_id,
  12.    out_cand_nom,
  13.    out_cand_prenom,
  14.    out_cand_imat,
  15.    out_cand_typo,
  16.    out_cand_dte_naiss,
  17.    cp_codpos,
  18.    cp_ville,
  19.    out_ref_offre_stat_lib,
  20.    OUT_EXP_PRO.epr_titre AS epr_titre1,
  21.    ROME1.bcg_rome_code AS bcg_rome_code1,
  22.    ROME1.bcg_rome_lib AS bcg_rome_lib1,
  23.    cand_educ_lib,
  24.    cand_educ_comment,
  25.    cand_educ_date,
  26.    out_cand_key_words,
  27.    out_ref_situ_lib,
  28.    out_cand_cong_deb,
  29.    out_cand_cong_fin,
  30.    out_cand_dte_in,
  31.    out_cand_dte_activ,
  32.    out_cand_dte_parm,
  33.    out_cand_dte_anc,
  34.    out_cand_dte_out,
  35.    out_cand_dte_out_prev,
  36.    out_ref_type_proj_lib,
  37.    out_cand_prod_site_id,
  38.    out_cand_bureau,
  39.    out_cand_num_bureau,
  40.    out_cand_poste_actu,
  41.    out_cand_date_benef,
  42.    out_cand_club_com,
  43.                                                    out_gpec_intitule,
  44.    out_gpec_dte_deb,
  45.    out_gpec_dte_fin,
  46.    out_recl_type_lib,
  47.    out_recl_date,
  48.    out_ref_offre_contrat_lib,
  49.    out_recl_date_fin,
  50.    out_recl_contrat_nb_mois,
  51.    out_recl_soc,
  52.    out_recl_soc_ville,
  53.    out_recl_soc_zip,
  54.    ROME2.bcg_rome_code AS bcg_rome_code2,
  55.    ROME2.bcg_rome_lib AS bcg_rome_lib2,
  56.    ROME3.bcg_rome_code AS bcg_rome_code3,
  57.    ROME3.bcg_rome_lib AS bcg_rome_lib3,
  58.    out_activ_date,
  59.    OUT_VOE_PRO.epr_titre AS epr_titre2,
  60.    CLIENT.client_nom,
  61.    CLIENT.client_adr3,
  62.    out_typo_libelle,
  63.    out_ref_activ_r_lib,
  64.    out_activ_com,
  65.                 (SELECT out_activ_date FROM OUT_ACTIV WHERE out_activ_cand_id = out_cand_id AND out_activ_type='204 ' AND out_activ_result='2' ORDER BY out_activ_date DESC LIMIT 1) AS date_eeo,
  66.                 (SELECT out_activ_date FROM OUT_ACTIV WHERE out_activ_cand_id = out_cand_id AND out_activ_type='37 ' AND out_activ_result='2' ORDER BY out_activ_date DESC LIMIT 1) AS date_valid,
  67.                 (SELECT out_activ_date FROM OUT_ACTIV WHERE out_activ_cand_id = out_cand_id AND out_activ_result='2' ORDER BY out_activ_date ASC LIMIT 1) AS premier_entret,
  68.                 (SELECT out_activ_date FROM OUT_ACTIV WHERE out_activ_cand_id = out_cand_id AND out_activ_result='2' ORDER BY out_activ_date DESC LIMIT 1) AS dernier_entret,
  69.                 (SELECT COUNT(out_activ_id) FROM OUT_ACTIV WHERE out_activ_cand_id = out_cand_id AND (out_activ_type IN(1,3,5) OR out_activ_type BETWEEN '20' AND '37' OR out_activ_type BETWEEN '200' AND '299')AND out_activ_result='2') AS nb_entret,
  70.    (SELECT COUNT(out_activ_id) FROM OUT_ACTIV WHERE out_activ_cand_id = out_cand_id AND (out_activ_type IN(1,2,4,38) OR out_activ_type BETWEEN '6' AND '16' OR out_activ_type BETWEEN '80' AND '100')AND out_activ_result='2') AS nb_atel,
  71.    (SELECT COUNT(out_ove_id) FROM OUT_OVE WHERE out_ove_cand = out_cand_id) AS nb_pos,
  72.    (SELECT COUNT(out_res_id) FROM OUT_RES_NEW WHERE out_res_cand_id = out_cand_id) AS nb_pos_res,
  73.    (SELECT COUNT(out_ove_histo_id) FROM OUT_OVE_HISTO, OUT_OVE, OUT_OVE_TYPE WHERE out_ove_cand = out_cand_id AND out_ove_histo_ove_id = out_ove_id AND out_ove_histo_type = out_ove_type_id AND out_ove_histo_type='4') AS nb_entret_recrut,
  74.    (SELECT COUNT(out_ove_histo_id) FROM OUT_OVE_HISTO, OUT_OVE WHERE out_ove_cand = out_cand_id AND out_ove_histo_ove_id = out_ove_id AND out_ove_histo_valid='1') AS nb_ove
  75.                
  76.   FROM OUT_CAND
  77.   LEFT JOIN OUT_MISS ON out_cand_miss_id = out_miss_id
  78.   LEFT JOIN BCG_CIV ON out_cand_civ = bcg_civ_id
  79.   LEFT JOIN BCG_POST ON out_cand_city_id = bcg_post_id
  80.   LEFT JOIN OUT_REF_OFFRE_STATUT ON out_cand_college = out_ref_offre_stat_id
  81.   LEFT JOIN OUT_EXP_PRO ON out_cand_id = OUT_EXP_PRO.epr_cand_id AND OUT_EXP_PRO.epr_id = (SELECT OUT_EXP_PRO.epr_id FROM OUT_EXP_PRO WHERE OUT_EXP_PRO.epr_cand_id = out_cand_id ORDER BY OUT_EXP_PRO.epr_to DESC LIMIT 1)
  82.   LEFT JOIN BCG_ROME ROME1 ON OUT_EXP_PRO.epr_rome = ROME1.bcg_rome_id
  83.   LEFT JOIN OUT_CAND_EDUC ON out_cand_id = cand_educ_cand_id AND cand_educ_id = (SELECT cand_educ_id FROM OUT_CAND_EDUC WHERE cand_educ_cand_id = out_cand_id ORDER BY cand_educ_date DESC LIMIT 1)
  84.   LEFT JOIN OUT_REF_SITU ON out_cand_situ_id = out_ref_situ_id
  85.   LEFT JOIN OUT_REF_TYPE_PROJ ON out_cand_perso = out_ref_type_proj_id
  86.   LEFT JOIN OUT_GPEC ON out_cand_id = out_gpec_cand_id AND out_gpec_id = (SELECT out_gpec_id FROM OUT_GPEC WHERE out_cand_id = out_gpec_cand_id ORDER BY out_gpec_dte_deb DESC LIMIT 1)
  87.   LEFT JOIN OUT_RECL ON out_cand_id = out_recl_cand_id AND out_recl_id = (SELECT out_recl_id FROM OUT_RECL WHERE out_recl_cand_id = out_cand_id ORDER BY out_recl_date DESC LIMIT 1)
  88.   LEFT JOIN OUT_RECL_TYPE ON OUT_RECL.out_recl_type_id = OUT_RECL_TYPE.out_recl_type_id
  89.   LEFT JOIN OUT_REF_OFFRE_CONTRAT ON out_recl_contrat_id = out_ref_offre_contrat_id
  90.   LEFT JOIN BCG_ROME ROME2 ON out_recl_rome_id = ROME2.bcg_rome_id
  91.   LEFT JOIN OUT_ACTIV ON out_cand_id = out_activ_cand_id AND out_activ_type = '306' AND out_activ_id = (SELECT out_activ_id FROM OUT_ACTIV WHERE out_activ_type = '306' AND out_cand_id = out_activ_cand_id ORDER BY out_activ_date DESC LIMIT 1)
  92.             LEFT JOIN OUT_PROJ ON out_proj_id = out_miss_proj
  93.   LEFT JOIN OUT_SOUS_PROJET ON out_sous_projet_projet_id = out_proj_id
  94.   LEFT JOIN OUT_VOE_PRO ON out_cand_id = OUT_VOE_PRO.epr_cand_id AND OUT_VOE_PRO.epr_id = (SELECT OUT_VOE_PRO.epr_id FROM OUT_VOE_PRO WHERE OUT_VOE_PRO.epr_cand_id = out_cand_id ORDER BY OUT_VOE_PRO.epr_scope ASC LIMIT 1)
  95.   LEFT JOIN BCG_ROME ROME3 ON OUT_VOE_PRO.epr_rome = ROME3.bcg_rome_id
  96.         LEFT JOIN CLIENT ON OUT_CAND.out_cand_prod_site_id = CLIENT.client_id
  97.         LEFT JOIN OUT_TYPO ON OUT_CAND.out_cand_typo = out_typo_num AND out_cand_miss_id = out_typo_miss_id
  98.         LEFT JOIN OUT_REF_ACTIV_RESULT ON out_ref_activ_r_id = out_activ_result
  100.   WHERE out_miss_id = out_miss_id
  101.       AND OUT_MISS.out_miss_proj = '609'
  102.   AND OUT_MISS.out_miss_sous_projet_id = OUT_SOUS_PROJET.out_sous_projet_id
  103.   ORDER BY out_cand_nom, out_cand_prenom


Message édité par Profil supprimé le 15-05-2012 à 16:20:37
Reply

Marsh Posté le 15-05-2012 à 16:19:22   

Reply

Marsh Posté le 15-05-2012 à 17:50:04    

Vue la syntaxe, je dirais que t'es sous Mysql.
 
Si c'est le cas, avant de chercher à modifier la requête (faudrait déjà nous dire ce qu'elle fait), ce qui pourrait introduire des bugs, t'as déjà plusieurs leviers :
1) utiliser EXPLAIN pour voir quels index sont utilisés par le sgbd (et donc aussi voir ceux qui sont pas utilisés). Comme ça, en réindexant les bons champs, ça pourrait grandement améliorer les perfs
 
2) tuner le fichier de conf de Mysql. Pour t'aider, tu peux employer le script perl http://mysqltuner.pl/mysqltuner.pl
Faut le passer sur un mysql qui est en prod depuis qq temps. Ca va te dire un peu les mariables qu'il faudrait modifier. Si tu as PhpMyAdmin, tu pourras aussi regarder les valeurs qui ne sont pas bonnes (en rouge), depuis la page d'accueil de phpmyadmin et tu vas dans "afficher l'état du serveur". Y'a fort à parier que les buffers et caches soient trop petit.
 
Perso, sur un serveur de prod, rien qu'en jouant avec ces 2 actions, j'ai divisé par 5 le temps d'exécution d'une requête, sans la réécrire. ;) Mais ça ne veut pas dire que ce sera pareil pour toi, les perfs, ça varie beaucoup d'une machine à une autre et du type de requête qu'on fait + comment la BD est bien modélisée ou pas pour effectuer certains traitements... :/
 
Là, ta requête, y'a quand même vachement de LEFT JOIN et de sous-requêtes... Vue qu'il y a pas mal de LIMIT 1, t'aurais peut-être intérêt à stocker dans une table temporaire la valeur que tu remontes pour chacune des sous-requête où y'a un limit 1. Ca permettrait élaguer la taille des données sur lesquelles tu travailles en même temps.


---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Planeta Calandreta : https://framalibre.org/content/planeta-calandreta
Reply

Marsh Posté le 16-05-2012 à 11:53:34    

Merci beaucoup pour ta réponse... Enfaite je suis en dev dans l'entreprise, je n'ai pas accès au Serveur en lui meme, donc pas pouvoir le tuner... Je peux juste acceder à phpMyAdmin enfaite, et optimiser la requette, je regarderai ce que tu m'as dis, je te remercie beaucoup pour ton aide :) Je vais y travailler et on verra :)

Reply

Marsh Posté le 16-05-2012 à 13:38:18    

Si t'as pas accès, toi, au serveur, tu peux sans doute demander à faire modifier certaines valeurs dans le fichier de conf de mysql au service informatique qui administre le serveur. Moi, c'est comme ça que ça marche à mon boulot. Je donne les valeurs des variables qui m"intéressent, ils modifient.


---------------
Astres, outil de help-desk GPL : http://sourceforge.net/projects/astres, ICARE, gestion de conf : http://sourceforge.net/projects/icare, Outil Planeta Calandreta : https://framalibre.org/content/planeta-calandreta
Reply

Marsh Posté le 21-05-2012 à 09:46:12    

Oki merci beaucoup, on a trouvé, une table n'était pas indexé... On a divisé par 30 le temps d'execution :) Merci encore de ton aide précieuse !

Reply

Sujets relatifs:

Leave a Replay

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