GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Dead query

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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,''),I FNULL(DOM.dpto,''),IFNULL(DOM.adicional,''),IFNULL (DOM.codig o_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
    Hi,

    Can you provide the explain of the above query and show create table of every involved tables?

    Comment


    • #3
      explain doesn´t work. just freezes and retrns nothing

      Comment

      Working...
      X