MySQL Forums
Forum List  »  Performance

Dead query
Posted by: Pablo Worcel
Date: December 21, 2012 08:00AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Dead query
3252
December 21, 2012 08:00AM
1095
December 22, 2012 03:27PM
1259
January 03, 2013 07:48AM


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.