MySQL Forums
Forum List  »  Knowledge Base

bad performance of long complex sql
Posted by: liu jackle
Date: December 11, 2009 05:02AM

Recently having came accross a difficult problem. It is a long complex sql, when this sql is executed on access, it is very fast, about not more than 1s. but when migrating to mysql, speed becomes unbearable, about 10s. the table structure are the same from access to mysql.

I have removed group by and order by, max,min, sum function, speed becomes very fast.
then I add one of group by and order by, speed becomes slow again.
then I tried to keep only one field in group by or order by, speed does not improve either.
I would be very appreciated if anyone have some ideas.
below is the long sql.


SELECT PROGRAMMATION_DETAILS.START_DATE, PROGRAMMATION_DETAILS.END_DATE, PROGRAMMATION_DETAILS.ID_NOEUD_ACTIVITE, INDICATEURS.NOM_INDICATEUR, INDICATEURS.ID_INDICATEUR, queryActivites.SActiviteCode,

queryActivites.SActiviteId, queryActivites.SActiviteNom,queryActivites.ActiviteID,

queryActivites.ActiviteCode,queryActivites.ActiviteNom, queryActivites.ResultatID,

queryActivites.ResultatCode, queryActivites.ResultatNom,queryActivites.ObjectifId,

queryActivites.ObjectifCode, queryActivites.ObjectifNom,

Sum(PRG_DET_IND.VALEUR)

AS qte_programmee,

Sum(ALL_SUIVI_REALISATION_INDICATEURS.qte_realisee_max) AS qte_realisee_max,

Sum(ALL_SUIVI_REALISATION_INDICATEURS.qte_realisee) AS qte_realisee,

Min(ALL_SUIVI_REALISATION_INDICATEURS.monitoring_date) AS monitoring_date,

queryActivites.ObjectifTri,queryActivites.ActiviteTri, queryActivites.ResultatTri,

queryActivites.SActiviteTri,queryActivites.IndicateurTri,INDICATEURS.NOM_ABRG_INDICATEUR,

queryGeoAll.TypeGeoId, queryGeoAll.NOM_PARENT,queryGeoAll.ID_PARENT,queryGeoAll.CLE_PARENT,

queryGeoAll.TRI_PARENT , PROGRAMMATION_DETAILS.ID_MOD_EXE,MOD_EXE.ID_MOD_EXE,MOD_EXE.NOM_MOD_EXE,MOD_EXE.TRI ,

PROGRAMMATION_DETAILS.ID_SOURCE_FINANCEMENT, SOURCES_FINANCEMENT.ID_SOURCE_FINANCEMENT, SOURCES_FINANCEMENT.NOM_SOURCE_FINANCEMENT,

SOURCES_FINANCEMENT.TRI FROM SOURCES_FINANCEMENT INNER JOIN


(

( MOD_EXE INNER JOIN ((

SELECT queryTypesGeo.TypeGeoId, PARENT_GEO.NOM_PARENT, PARENT_GEO.ID_PARENT ,PARENT_GEO.CLE_PARENT,

PARENT_GEO.TRI_PARENT, queryTypesGeo.ID FROM

( SELECT HIER_GEO_TYPES.ID AS TypeGeoId, HIER_GEO.ID, HIER_GEO.CLE

FROM HIER_GEO_TYPES INNER JOIN HIER_GEO ON HIER_GEO_TYPES.ID = HIER_GEO.TYPE_GEO

WHERE HIER_GEO_TYPES.CLE Like '%.3243.%' ) queryTypesGeo,

(SELECT HIER_GEO.ID AS ID_PARENT, HIER_GEO.NOM AS NOM_PARENT, HIER_GEO.TYPE_GEO, HIER_GEO.CLE AS CLE_PARENT, HIER_GEO.TRI

AS TRI_PARENT FROM HIER_GEO WHERE (((HIER_GEO.TYPE_GEO)=3243) AND ( HIER_GEO.ID_PROJET =1)) )

PARENT_GEO WHERE queryTypesGeo.CLE Like CONCAT('%.',PARENT_GEO.ID_PARENT,'.%')

) queryGeoAll

INNER JOIN

(

PROGRAMMATION_DETAILS INNER JOIN(SELECT HIER_ACTIVITE.ID AS SActiviteId,

HIER_ACTIVITE.CODE AS SActiviteCode, HIER_ACTIVITE.NOM AS SActiviteNom,

HIER_ACTIVITE_1.ID AS ActiviteID,HIER_ACTIVITE_1.CODE AS ActiviteCode,

HIER_ACTIVITE_1.NOM AS ActiviteNom, HIER_ACTIVITE_2.ID AS ResultatID,HIER_ACTIVITE_2.CODE AS ResultatCode,

HIER_ACTIVITE_2.NOM AS ResultatNom,HIER_ACTIVITE_3.ID AS ObjectifId, HIER_ACTIVITE_3.CODE AS

ObjectifCode,HIER_ACTIVITE_3.NOM AS ObjectifNom, HIER_ACTIVITE.TRI AS

SActiviteTri,HIER_ACTIVITE_1.TRI AS ActiviteTri, HIER_ACTIVITE_2.TRI AS ResultatTri,HIER_ACTIVITE_3.TRI AS ObjectifTri,

IND_REFERENCE.TYPE_IND,IND_REFERENCE.TRI AS IndicateurTri,IND_REFERENCE.ID_INDICATEUR

FROM( ( (HIER_ACTIVITE INNER JOIN IND_REFERENCE ON HIER_ACTIVITE.ID =IND_REFERENCE.ID_NOEUD_ACTIVITE)

INNER JOIN HIER_ACTIVITE AS HIER_ACTIVITE_1 ON HIER_ACTIVITE.ID_PARENT = HIER_ACTIVITE_1.ID )

INNER JOIN HIER_ACTIVITE AS HIER_ACTIVITE_2 ON HIER_ACTIVITE_1.ID_PARENT =HIER_ACTIVITE_2.ID)

INNER JOIN HIER_ACTIVITE AS HIER_ACTIVITE_3 ON HIER_ACTIVITE_2.ID_PARENT = HIER_ACTIVITE_3.ID

where HIER_ACTIVITE.id_projet = 1 AND HIER_ACTIVITE_1.id_projet = 1 AND HIER_ACTIVITE_2.id_projet = 1 AND

HIER_ACTIVITE_3.id_projet = 1 ) queryActivites ON PROGRAMMATION_DETAILS.ID_NOEUD_ACTIVITE = queryActivites.SActiviteId

) ON

queryGeoAll.ID = PROGRAMMATION_DETAILS.ID_NOEUD_GEO) ON MOD_EXE.ID_MOD_EXE = PROGRAMMATION_DETAILS.ID_MOD_EXE

)

INNER JOIN(


(INDICATEURS INNER JOIN PRG_DET_IND ON INDICATEURS.ID_INDICATEUR = PRG_DET_IND.ID_INDICATEUR)

LEFT JOIN ( SELECT Sum(SUIVI_REALISATION_INDICATEURS.VALEUR_OBSERVE) AS qte_realisee, MAX(SUIVI_REALISATION_INDICATEURS.VALEUR_OBSERVE)

AS qte_realisee_max, MAX(SUIVI_REALISATION_INDICATEURS.DATE_OBSERVATION) AS

monitoring_date, SUIVI_REALISATION_INDICATEURS.ID_INDICATEUR, SUIVI_REALISATION_INDICATEURS.ID_DETAIL_PROGRAMMATION

FROM SUIVI_REALISATION_INDICATEURS GROUP BY SUIVI_REALISATION_INDICATEURS.ID_INDICATEUR , SUIVI_REALISATION_INDICATEURS.ID_DETAIL_PROGRAMMATION )

ALL_SUIVI_REALISATION_INDICATEURS ON(PRG_DET_IND.ID_INDICATEUR = ALL_SUIVI_REALISATION_INDICATEURS.ID_INDICATEUR)

AND(PRG_DET_IND.ID_DETAIL_PROGRAMMATION = ALL_SUIVI_REALISATION_INDICATEURS.ID_DETAIL_PROGRAMMATION)

) ON

(queryActivites.ID_INDICATEUR =PRG_DET_IND.ID_INDICATEUR) AND (PROGRAMMATION_DETAILS.ID_DETAIL_PROGRAMMATION = PRG_DET_IND.ID_DETAIL_PROGRAMMATION) )

ON SOURCES_FINANCEMENT.ID_SOURCE_FINANCEMENT =PROGRAMMATION_DETAILS.ID_SOURCE_FINANCEMENT where INDICATEURS.id_projet = 1 AND

queryActivites.TYPE_IND=1 GROUP BY PROGRAMMATION_DETAILS.ID_NOEUD_ACTIVITE,PROGRAMMATION_DETAILS.START_DATE,PROGRAMMATION_DETAILS.END_DATE,

INDICATEURS.NOM_INDICATEUR, INDICATEURS.ID_INDICATEUR, queryActivites.SActiviteCode, queryActivites.SActiviteId, queryActivites.SActiviteNom,

queryActivites.ActiviteID, queryActivites.ActiviteCode,queryActivites.ActiviteNom, queryActivites.ResultatID, queryActivites.ResultatCode,

queryActivites.ResultatNom, queryActivites.ObjectifId, queryActivites.ObjectifCode, queryActivites.ObjectifNom,

queryActivites.ObjectifTri, queryActivites.ActiviteTri, queryActivites.ResultatTri, queryActivites.SActiviteTri,

queryActivites.IndicateurTri, INDICATEURS.NOM_ABRG_INDICATEUR, queryGeoAll.TypeGeoId,PROGRAMMATION_DETAILS.ID_PROGRAMMATION ,

queryGeoAll.NOM_PARENT,queryGeoAll.ID_PARENT,queryGeoAll.CLE_PARENT,queryGeoAll.TRI_PARENT ,

PROGRAMMATION_DETAILS.ID_MOD_EXE ,MOD_EXE.ID_MOD_EXE,MOD_EXE.NOM_MOD_EXE,MOD_EXE.TRI ,

PROGRAMMATION_DETAILS.ID_SOURCE_FINANCEMENT, SOURCES_FINANCEMENT.ID_SOURCE_FINANCEMENT,

SOURCES_FINANCEMENT.NOM_SOURCE_FINANCEMENT, SOURCES_FINANCEMENT.TRI HAVING

( PROGRAMMATION_DETAILS.ID_PROGRAMMATION='3252' and ( ( PROGRAMMATION_DETAILS.START_DATE between DateValue('2009-01-01') and

DateValue('2009-12-04')) or ( PROGRAMMATION_DETAILS.END_DATE between DateValue('2009-01-01') and DateValue('2009-12-04')) or

( PROGRAMMATION_DETAILS.START_DATE <= DateValue('2009-01-01') and PROGRAMMATION_DETAILS.END_DATE >= DateValue('2009-12-04')) ) AND

(UPPER(queryActivites.SActiviteNom) like '%%' OR UPPER(queryActivites.SActiviteCode) like '%%') ) ORDER BY

queryActivites.ObjectifTri, queryActivites.ResultatTri,queryActivites.ActiviteTri, queryActivites.SActiviteTri, NOM_PARENT,

TRI_PARENT ,TypeGeoId , SOURCES_FINANCEMENT.TRI , MOD_EXE.TRI , queryActivites.IndicateurTri, INDICATEURS.NOM_ABRG_INDICATEUR

Options: ReplyQuote


Subject
Views
Written By
Posted
bad performance of long complex sql
2471
December 11, 2009 05:02AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.