Thread: Dead query

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    2
    Rep Power
    0

    Angry Dead query


    Hi: I spent a lot of time creating a query, and it doesn´t return an error, but I run it and doesn´t start. I left it for 23 hours (more than enough, I think) and it didn´t get one single row. I checked with other program (Pentaho spoon, that lets you follow the operation).
    The query has a lot of joins, but I wrote some with more, with no problems.
    I´d really appreciate any advise on which part is slowing it down
    here goes the query:

    SELECT
    P.apellido
    , P.nombre
    , CONCAT(P.apellido,", ",P.nombre) AS apellido_nombre
    , TDOC.tipo_documento
    , P.numero_documento
    , P.sexo
    , P.cuil
    , CONCAT(DOM.calle,IFNULL(DOM.numero,''),IFNULL(DOM.piso,''),IFNULL(DOM.dpto,''),IFNULL(DOM.adicional, ''),IFNULL(DOM.codigo_postal,'')) AS domicilio_personal
    , LOCP.localidad AS localidad_particular
    , PARTP.partido AS partido_particular
    , PROVP.provincia AS provincia_particular
    , PAP.pais AS pais_particular
    , CASE
    WHEN (MONTH(P.fecha_nacimiento) < MONTH(CURRENT_DATE)) THEN YEAR(CURRENT_DATE) - YEAR(P.fecha_nacimiento)
    WHEN (MONTH(P.fecha_nacimiento) = MONTH(CURRENT_DATE)) AND (DAY(P.fecha_nacimiento) <= DAY(CURRENT_DATE)) THEN YEAR(CURRENT_DATE) - YEAR(P.fecha_nacimiento)
    ELSE (YEAR(CURRENT_DATE) - YEAR(P.fecha_nacimiento)) - 1
    END AS edad
    ,P.fecha_nacimiento

    ,CO.denominacion
    , C.comision
    , 'BECAS' as tipo_comision
    ,PUNT.orden_merito
    , CA_D.recomendacion AS ca_d_recomendacion
    -- ,TD.tipo_decision AS Directorio
    ,IF(LIME.tope_edad < YEAR( FROM_DAYS(TO_DAYS(CO.fecha_desde)-TO_DAYS(P.fecha_nacimiento))), 'CON EXCEPCION' , NULL) AS excepcion_por_edad

    ,P.telefono_personal AS telefono_part
    ,CONCAT(IFNULL(LT.telefono_pais,''),"-",IFNULL(LT.telefono_area,''),"-", IFNULL(LT.telefono_caracteristica,''),"-",LT.telefono_numero," int ",IFNULL(LT.telefono_interno,'')) AS telefono_Lab
    ,P.email_personal AS email_personal
    ,LT.email AS 'email-lab'
    /* ,PCE.dato AS PCEDATO
    ,PCT.dato AS PCTTDATO*/

    , GA.gran_area AS GRAN_AREA
    , DIS.disciplina AS DIS_PRI
    , DIS2.disciplina AS DIS_SEC
    , DISDES.disciplina_desagregada AS DIS_DESAGREGADA
    , DISDES.codigo AS DIS_DESAGREGADA_COD
    , DISDES2.disciplina_desagregada AS DIS_DESAGREGADA_SEC
    , DISDES2.codigo AS DIS_DESAGREGADA_SEC_COD

    , org.unidad AS Lugar_trabajo
    , UO1.unidad AS nivel_1
    , UO2.unidad AS nivel_2
    , UO3.unidad AS nivel_3
    , UO4.unidad AS nivel_4
    , LOC.localidad AS LOCALIDAD_LT
    , PART.partido AS PARTIDO_LT
    , PROV.provincia AS PROVINCIA_LT
    , PROV.codigo AS COD_PROVINCIA_LT
    , PA. pais AS PAIS_LT
    , PA. codigo_pais AS COD_PAIS_LT

    , E.estado AS estado_tramite


    FROM PERSONA P
    INNER JOIN TIPO_DOCUMENTO TDOC ON (P.tipo_documento_tk=TDOC.tk)
    LEFT JOIN DOMICILIO DOM ON (DOM.persona_tk=P.tk)
    LEFT JOIN PAIS PAP ON (PAP.tk=DOM.pais_tk)
    LEFT JOIN LOCALIDAD LOCP ON (LOCP.tk=DOM.localidad_tk)
    LEFT JOIN PARTIDO PARTP ON (LOCP.partido_tk=PARTP.tk)
    LEFT JOIN PROVINCIA PROVP ON (PARTP.provincia_tk=PROVP.tk)

    INNER JOIN TRAMITE T ON (P.propietario_tk=T.propietario_tk)
    LEFT JOIN ESTADO E ON (E.tk = T.estado_tk)
    INNER JOIN LUGAR_TRABAJO_TRAMITE LTT ON (T.tk = LTT.tramite_tk)
    INNER JOIN LUGAR_TRABAJO LT ON (LTT.lugar_trabajo_tk = LT.tk)
    INNER JOIN CONVOCATORIA CO ON (CO.tk = T.convocatoria_tk)
    INNER JOIN COMISION_TRAMITE CT ON (T.tk = CT.tramite_tk)
    INNER JOIN COMISION C ON (CT.comision_tk = C.tk AND C.tipo_comision_tk=4)
    LEFT JOIN OBJETO_EVALUACION OBJE ON CO.objeto_evaluacion_tk=OBJE.tk
    LEFT JOIN LIMITE_EDAD LIME ON LIME.objeto_evaluacion_tk=OBJE.tk

    LEFT JOIN ORDEN_MERITO_TRAMITE PUNT ON (PUNT.tramite_tk = T.tk)

    LEFT JOIN
    (SELECT
    CT.tramite_tk
    , CD.tk AS comision_dictamen_tk
    , CD.tipo_dictamen_tk
    ,CASE
    WHEN CD.tipo_recomendacion_tk = 14
    THEN 'RECO.'
    WHEN CD.tipo_recomendacion_tk = 13
    THEN 'NO RECO.'
    ELSE ''
    END AS recomendacion ,
    CD.tipo_recomendacion_tk
    FROM TRAMITE T
    INNER JOIN COMISION_TRAMITE CT ON (T.tk = CT.tramite_tk)
    INNER JOIN COMISION C ON (CT.comision_tk = C.tk)
    LEFT JOIN COMISION_DICTAMEN CD ON (CT.tk = CD.comision_tramite_tk)
    WHERE (T.tk = CT.tramite_tk)
    AND (CD.tipo_dictamen_tk = 1)
    AND (C.tipo_comision_tk <> 2)
    AND ( CD.estado_comision_dictamen_tk IN (4,6))) CA_D ON (CA_D.tramite_tk = CT.tramite_tk)

    INNER JOIN DATO_ACADEMICO_TRAMITE DAT ON (T.tk = DAT.tramite_tk)
    LEFT JOIN DISCIPLINA DIS ON (DIS.tk = DAT.disciplina_tk)
    LEFT JOIN DISCIPLINA_DESAGREGADA DISDES ON (DISDES.tk = DAT.disciplina_desagregada_tk)
    LEFT JOIN DISCIPLINA DIS2 ON (DIS2.tk = DAT.disciplina_sec_tk)
    LEFT JOIN DISCIPLINA_DESAGREGADA DISDES2 ON (DISDES2.tk = DAT.disciplina_desagregada_sec_tk)
    LEFT JOIN GRAN_AREA AS GA ON (DIS.gran_area_tk=GA.tk)

    LEFT JOIN DIRECTOR_TRAMITE_PRESENTADO DT ON (DT.tramite_tk = T.tk )
    LEFT JOIN DIRECTOR_PRESENTADO DIR ON (DIR.tk = DT.director_tk AND DIR.tipo_director_tk = 1)
    LEFT JOIN UNIDAD_ORGANIZATIVA org ON (org.tk = LT.unidad_organizativa_tk)
    LEFT JOIN UNIDAD_ORGANIZATIVA UO1 ON (UO1.tk = SUBSTR(org.unidad, 1, 7))
    LEFT JOIN UNIDAD_ORGANIZATIVA UO2 ON (UO2.tk = SUBSTR(org.unidad, 9, 7))
    LEFT JOIN UNIDAD_ORGANIZATIVA UO3 ON (UO3.tk = SUBSTR(org.unidad, 17, 7))
    LEFT JOIN UNIDAD_ORGANIZATIVA UO4 ON (UO4.tk = SUBSTR(org.unidad, 25, 7))
    LEFT JOIN LOCALIDAD LOC ON (org.localidad_tk=LOC.tk)
    LEFT JOIN PARTIDO PART ON (LOC.partido_tk=PART.tk)
    LEFT JOIN PROVINCIA PROV ON (PART.provincia_tk=PROV.tk)
    LEFT JOIN PAIS PA ON (PROV.pais_tk=PA.tk)

    WHERE C.tipo_comision_tk <> 2
    AND LIME.objeto_evaluacion_tk IS NOT NULL
    AND CO.tk IN (104201102,103201102,105201102,104201101,103201101,105201101)
    ORDER BY C.comision,CO.tk,PUNT.orden_merito;

    Thank you
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Place an EXPLAIN in front of your query and execute it and post the result here within a CODE /CODE block (the # button above).

    We need to see what your MySQL instance is doing when it is executing your query.

    But some suggestions are:
    1. one of the tables in the query is missing an index on the join column so it has to perform a table scan to check if there are any matching records, if there are a lot of rows then this will become very costly.

    2. join conditions with calculations in them like:
    UO1.tk = SUBSTR(org.unidad, 1, 7)
    are also a performance killer since they can't use an index on org.unidad properly.

    3. In one of the joins the two columns joined are not of the exact same type also causing a table scan.

    But without the execution plan from the EXPLAIN above we don't know where to look.
    /Stefan
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    2
    Rep Power
    0
    Thanks for the help. Items 1 and 2 where what was wrong.
    2 tables had no indexes, and I split the query in 4. now they finish in 60 seconds (very large tables)
    cheers

    El Multiversista

IMN logo majestic logo threadwatch logo seochat tools logo