MySQL

 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me



Go Back   Tutorialized ForumsDatabasesMySQL

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Tutorialized Forums Sponsor:
  #1  
Old December 21st, 2012, 08:46 AM
multiversista multiversista is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Dec 2012
Posts: 1 multiversista User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 m 43 sec
Reputation Power: 0
Unhappy 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

Reply With Quote
Reply

Viewing: Tutorialized ForumsDatabasesMySQL > Dead query


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap