Results 1 to 2 of 2

Thread: Dead query

  1. #1
    Join Date
    Dec 2012
    Posts
    1

    Unhappy Unanswered: 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
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    You are joining 39 tables. That will be where your problem lies. There are 2 optimizer parameters that limits the amount of time is spent on working out the best plan. These are found here MySQL :: MySQL 5.0 Reference Manual :: 8.4 Controlling the Query Optimizer. However, I would really look at reducing the number of tables that you are accessing. If possible if this can be broken down into 2 or more queries then this would be much better.

    As for the correct results being returned we cannot help with this as we do not know what you are trying to achieve. I would first try to break the query down and look at the results.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •