none
Timie Out RRS feed

  • Pregunta

  • Chicos tengo esta consulta y me da time out

    SELECT O.CODIGO as CODIGO, 
    		                                MAX(O.SEMANA1) as SEMANA1, 
    		                                MAX(O.SEMANA2) as SEMANA2, 
    		                                MAX(O.SEMANA3) as SEMANA3,
    		                                MAX(O.SEMANA4) as SEMANA4, 
    		                                MAX(O.SEMANA5) as SEMANA5,
    		                                MAX(O.SEMANA6) as SEMANA6, 
    		                                MAX(O.SEMANA7) as SEMANA7, 
    		                                MAX(O.SEMANA8) as SEMANA8,
    		                                MAX(O.SEMANA9) as SEMANA9,
    		                                MAX(O.SEMANA10) as SEMANA10,
    						MAX(O.SEMANA11) as SEMANA11,
    						MAX(O.SEMANA12) as SEMANA12,
    						MAX(O.SEMANA13) as SEMANA13,
    						MAX(O.SEMANA14) as SEMANA14,
    						MAX(O.SEMANA15) as SEMANA15,
    						MAX(O.SEMANA16) as SEMANA16,
    						MAX(O.SEMANA17) as SEMANA17,
    						MAX(O.SEMANA18) as SEMANA18,
    						MAX(O.SEMANA19) as SEMANA19,
    						MAX(O.SEMANA20) as SEMANA20,
    						MAX(O.SEMANA21) as SEMANA21,
    						MAX(O.SEMANA22) as SEMANA22,
    						MAX(O.SEMANA23) as SEMANA23,
    						MAX(O.SEMANA24) as SEMANA24,
    						MAX(O.SEMANA25) as SEMANA25,
    						MAX(O.SEMANA26) as SEMANA26,
    		                                MAX(O.SEMANA27) as SEMANA27, 
    		                                MAX(O.SEMANA28) as SEMANA28, 
    		                                MAX(O.SEMANA29) as SEMANA29,
    		                                MAX(O.SEMANA30) as SEMANA30, 
    		                                MAX(O.SEMANA31) as SEMANA31,
    		                                MAX(O.SEMANA32) as SEMANA32, 
    		                                MAX(O.SEMANA33) as SEMANA33, 
    		                                MAX(O.SEMANA34) as SEMANA34,
    		                                MAX(O.SEMANA35) as SEMANA35,
    		                                MAX(O.SEMANA36) as SEMANA36,
    						MAX(O.SEMANA37) as SEMANA37,
    						MAX(O.SEMANA38) as SEMANA38,
    						MAX(O.SEMANA39) as SEMANA39,
    						MAX(O.SEMANA40) as SEMANA40,
    						MAX(O.SEMANA41) as SEMANA41,
    						MAX(O.SEMANA42) as SEMANA42,
    						MAX(O.SEMANA43) as SEMANA43,
    						MAX(O.SEMANA44) as SEMANA44,
    						MAX(O.SEMANA45) as SEMANA45,
    						MAX(O.SEMANA46) as SEMANA46,
    						MAX(O.SEMANA47) as SEMANA47,
    						MAX(O.SEMANA48) as SEMANA48,
    						MAX(O.SEMANA49) as SEMANA49,
    						MAX(O.SEMANA50) as SEMANA50,
    						MAX(O.SEMANA51) as SEMANA51,
    						MAX(O.SEMANA52) as SEMANA52
                                    FROM (
                                    SELECT D.CODIGO, 
    		                                round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=0 THEN D.zongshuliang END ),0),0) AS SEMANA1,
    		                                round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=1 THEN D.zongshuliang END ),0),0) AS SEMANA2,
    		                                round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=2 THEN D.zongshuliang END ),0),0) AS SEMANA3,
    		                                round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=3 THEN D.zongshuliang END ),0),0) AS SEMANA4,
    		                                round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=4 THEN D.zongshuliang END ),0),0) AS SEMANA5,
    		                                round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=5 THEN D.zongshuliang END ),0),0) AS SEMANA6,
    		                                round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=6 THEN D.zongshuliang END ),0),0) AS SEMANA7,
    		                                round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=7 THEN D.zongshuliang END ),0),0) AS SEMANA8,
    		                                round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=8 THEN D.zongshuliang END ),0),0) AS SEMANA9,
    		                                round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=9 THEN D.zongshuliang END ),0),0) AS SEMANA10,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=10 THEN D.zongshuliang END ),0),0) AS SEMANA11,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=11 THEN D.zongshuliang END ),0),0) AS SEMANA12,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=12 THEN D.zongshuliang END ),0),0) AS SEMANA13,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=13 THEN D.zongshuliang END ),0),0) AS SEMANA14,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=14 THEN D.zongshuliang END ),0),0) AS SEMANA15,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=15 THEN D.zongshuliang END ),0),0) AS SEMANA16,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=16 THEN D.zongshuliang END ),0),0) AS SEMANA17,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=17 THEN D.zongshuliang END ),0),0) AS SEMANA18,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=18 THEN D.zongshuliang END ),0),0) AS SEMANA19,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=19 THEN D.zongshuliang END ),0),0) AS SEMANA20,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=20 THEN D.zongshuliang END ),0),0) AS SEMANA21,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=21 THEN D.zongshuliang END ),0),0) AS SEMANA22,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=22 THEN D.zongshuliang END ),0),0) AS SEMANA23,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=23 THEN D.zongshuliang END ),0),0) AS SEMANA24,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=24 THEN D.zongshuliang END ),0),0) AS SEMANA25,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=25 THEN D.zongshuliang END ),0),0) AS SEMANA26,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=26 THEN D.zongshuliang END ),0),0) AS SEMANA27,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=27 THEN D.zongshuliang END ),0),0) AS SEMANA28,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=28 THEN D.zongshuliang END ),0),0) AS SEMANA29,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=29 THEN D.zongshuliang END ),0),0) AS SEMANA30,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=30 THEN D.zongshuliang END ),0),0) AS SEMANA31,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=31 THEN D.zongshuliang END ),0),0) AS SEMANA32,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=32 THEN D.zongshuliang END ),0),0) AS SEMANA33,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=33 THEN D.zongshuliang END ),0),0) AS SEMANA34,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=34 THEN D.zongshuliang END ),0),0) AS SEMANA35,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=36 THEN D.zongshuliang END ),0),0) AS SEMANA36,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=36 THEN D.zongshuliang END ),0),0) AS SEMANA37,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=37 THEN D.zongshuliang END ),0),0) AS SEMANA38,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=38 THEN D.zongshuliang END ),0),0) AS SEMANA39,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=39 THEN D.zongshuliang END ),0),0) AS SEMANA40,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=40 THEN D.zongshuliang END ),0),0) AS SEMANA41,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=41 THEN D.zongshuliang END ),0),0) AS SEMANA42,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=42 THEN D.zongshuliang END ),0),0) AS SEMANA43,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=43 THEN D.zongshuliang END ),0),0) AS SEMANA44,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=44 THEN D.zongshuliang END ),0),0) AS SEMANA45,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=45 THEN D.zongshuliang END ),0),0) AS SEMANA46,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=46 THEN D.zongshuliang END ),0),0) AS SEMANA47,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=47 THEN D.zongshuliang END ),0),0) AS SEMANA48,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=48 THEN D.zongshuliang END ),0),0) AS SEMANA49,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=49 THEN D.zongshuliang END ),0),0) AS SEMANA50,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=50 THEN D.zongshuliang END ),0),0) AS SEMANA51,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=51 THEN D.zongshuliang END ),0),0) AS SEMANA52,
    						round(IFNULL(SUM(CASE WHEN  WEEK(FECHAENVIO)=52 THEN D.zongshuliang END ),0),0) AS SEMANA53
    
    
    
                                    FROM 
                                       navmp.POEE P
                                    INNER JOIN 
                                       txgl.PEDIDOMOV D ON P.PEDIDOKEY = D.PEDIDOKEY
                                     WHERE
                                       FECHAENVIO >= '20180101' AND FECHAENVIO < '20180801'
                                    GROUP BY D.CODIGO, WEEK(FECHAENVIO)
                                    ) AS O
                                    group by o.codigo
                                    order by O.codigo

    Como puedo mejorar?

    se puede crear tabla temporal por fechas o algo asi?

    saludos


    ruben

    viernes, 22 de junio de 2018 10:24

Todas las respuestas

  • Hola ruben.

    La fecha de corte de la clausula where dice mayor o igual que el 1 de enero de 2018 y menor que el 1 de enero. Eso no puede funcionar. Nunca se cumplira.

    Un saludo

    viernes, 22 de junio de 2018 12:36
  • Hola Javi no es menor a 1 de agosto


    ruben

    viernes, 22 de junio de 2018 13:47
  • ¿Cual es el tiempo de timeout? Si lo ejecutas desde ADO. la conexion tiene un time out

    Te lo digo porque si te interesa mucho puedes ampliarlo. NO recuerdo, pero diría que en esa capa timeout es cambiable. con.Timetout = intervalo en segundos.

    Si para tus datos, y para lo que pides no te resuelve correctamente, yo diría que puedes empezar a plantearte, que en vez de hacer el informe en horizontal, puedes hacerlo en vertical, Así, la ejecución de la consulta será infinitamente más rápida, dado que el trasnponer, y demás funciones aplicadas, tienen su coste.

    Un saludo

    viernes, 22 de junio de 2018 14:19
  • Javi y como es hacerlo en vertical?

    saludos


    ruben

    viernes, 22 de junio de 2018 15:57
  • Con la consulta mucho más simple

    SELECT @rownum:=@rownum+1 as fila, D.CODIGO, SUM(D.RONGSHULIANG) AS suma , WEEK(FECHAENVIO)+1 as semana FROM (SELECT @rownum:=0) r, POEE P INNER JOIN PEDIDOMOV D ON P.PEDIDOKEY = D.PEDIDOKEY WHERE FECHAENVIO >= '20180101' AND FECHAENVIO < '20180201' GROUP BY D.CODIGO, WEEK(FECHAENVIO)

    order by d.CODIGO

    Tienes un numero de fila. el codigo, y lo que ha pedido para la semana.

    En la capa de Vb. Creas una coleccion que tiene 52 filas y lo que te trae la consulta por usuario, se lo pegas.

    La consulta es mucho más simple, y para Visual Basic es recorrer una coleccion y anexar unos valores.

    Un saludo

    viernes, 22 de junio de 2018 16:51