bad performance of long complex sql
Posted by: liu jackle
Date: December 11, 2009 05:02AM
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
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
Subject
Views
Written By
Posted
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.