none
SQL que da problemas. RRS feed

  • Pregunta

  • Muy buenas; tengo un problema que parece del todo ilógico. Estoy haciendo una consulta contra una base de datos en la cual hay varias SUMAS sobre una misma tabla la cual tarda muchísimo; aparentemente parece un problema de índices o de volumen de datos. No obstante modificando ligeramente la consulta los resultados son totalmente diferentes.

    ¿Alguien me podría explicar que está sucediendo en el motor de búsqueda?.

    Muchas gracias.

    A) Consulta original con problemas ( tiempos superiores a los 30 minutos):

    SELECT C1.CODIGO, C1.NOMBRE, C1.NIF, C1.SEGURO_IMPORTE_REAL,      
           C1.IMPORTE_ASEGURADO AS IMPORTE_CONCEDIDO,      
           SUM(FV1.TOTAL) AS TOTAL , 
        (SELECT SUM(FV2.TOTAL)
              FROM   CLIENTES C2
              INNER JOIN FACTURAS_VENTA FV2 ON C2.CODIGO = FV2.CODIGO_CLIENTE
        INNER JOIN FORMAS_PAGO ON FV2.CODIGO_FORMA_PAGO = FORMAS_PAGO.CODIGO 
      WHERE  FV2.FECHA >= '20171231 00:00:00' AND
             FV2.FECHA <= '20171231 23:59:59' AND
          C2.ESTADO_SEGURO = 1 AND  ((FORMAS_PAGO.CREDITO_CONTADO = 1) OR (FORMAS_PAGO.CODIGO = 17)) AND
          (FV2.CODIGO_CLIENTE = C1.CODIGO)) AS TOTAL_CONTADO,
         (SELECT SUM(FV2.TOTAL)        FROM   CLIENTES C2
         LEFT JOIN FACTURAS_VENTA FV2 ON C2.CODIGO = FV2.CODIGO_CLIENTE
         LEFT JOIN FORMAS_PAGO ON FV2.CODIGO_FORMA_PAGO = FORMAS_PAGO.CODIGO 
       WHERE  FV2.FECHA >= '20171231 00:00:00' AND
              FV2.FECHA <= '20171231 23:59:59' AND
        C2.ESTADO_SEGURO = 1       AND
        (FORMAS_PAGO.CREDITO_CONTADO = 0) AND  
        (FORMAS_PAGO.CODIGO <> 17)        AND  
        (FV2.CODIGO_CLIENTE = C1.CODIGO)) AS TOTAL_CREDITO,     
       (SELECT TOP 1 CAST(RATING AS NVARCHAR) + ' (' +CAST(ANYO_BALANCE AS NVARCHAR) + ')'       
          FROM   CLIENTES_INFORMA_DETALLE       
         WHERE (NIF = C1.NIF)       
         ORDER BY ANYO_BALANCE DESC) AS RATING
    FROM   CLIENTES C1
    INNER JOIN FACTURAS_VENTA FV1 ON C1.CODIGO = FV1.CODIGO_CLIENTE 
    WHERE  FV1.FECHA >= '20171231 00:00:00' AND
           FV1.FECHA <= '20171231 23:59:59' AND
        C1.ESTADO_SEGURO = 1
    GROUP BY C1.CODIGO, C1.NOMBRE, C1.NIF, C1.SEGURO_IMPORTE_REAL, C1.IMPORTE_ASEGURADO 
    ORDER BY C1.NOMBRE;

    B) Consulta sin problemas y muy rápida ( menos de 4 segundos ):

    SELECT C1.CODIGO, C1.NOMBRE, C1.NIF, C1.SEGURO_IMPORTE_REAL, C1.IMPORTE_ASEGURADO AS IMPORTE_CONCEDIDO ,
          (SELECT SUM(FV1.TOTAL)                                           /* Cambio de ubicación del sumatorio */
             FROM FACTURAS_VENTA FV1                                    /* Cambio de ubicación del sumatorio */
            WHERE (C1.CODIGO = FV1.CODIGO_CLIENTE) AND    /* Cambio de ubicación del sumatorio */
                  (FV1.FECHA >= '20170101 00:00:00' AND FV1.FECHA <= '20171231 23:59:59') ) AS TOTAL, 
          (SELECT SUM(FV2.TOTAL)       
              FROM   CLIENTES C2 INNER JOIN FACTURAS_VENTA FV2 ON C2.CODIGO = FV2.CODIGO_CLIENTE
                     INNER JOIN FORMAS_PAGO ON FV2.CODIGO_FORMA_PAGO = FORMAS_PAGO.CODIGO 
              WHERE  FV2.FECHA >= '20170101 00:00:00' AND FV2.FECHA <= '20171231 23:59:59'
              AND    C2.ESTADO_SEGURO = 6
              AND  ((FORMAS_PAGO.CREDITO_CONTADO = 1) OR (FORMAS_PAGO.CODIGO = 17))        
              AND   (FV2.CODIGO_CLIENTE = C1.CODIGO)) AS TOTAL_CONTADO,     
          (SELECT SUM(FV2.TOTAL)       
              FROM   CLIENTES C2 INNER JOIN FACTURAS_VENTA FV2 ON C2.CODIGO = FV2.CODIGO_CLIENTE
                     INNER JOIN FORMAS_PAGO ON FV2.CODIGO_FORMA_PAGO = FORMAS_PAGO.CODIGO 
           WHERE  FV2.FECHA >= '20170101 00:00:00' AND FV2.FECHA <= '20171231 23:59:59'
              AND    C2.ESTADO_SEGURO = 6      
              AND   (FORMAS_PAGO.CREDITO_CONTADO = 0)
              AND   (FORMAS_PAGO.CODIGO <> 17)       
              AND   (FV2.CODIGO_CLIENTE = C1.CODIGO)) AS TOTAL_CREDITO,     
          (SELECT TOP 1 CAST(RATING AS NVARCHAR) + ' (' +CAST(ANYO_BALANCE AS NVARCHAR) + ')'       
             FROM   CLIENTES_INFORMA_DETALLE       
            WHERE (NIF = C1.NIF)       
           ORDER BY ANYO_BALANCE DESC) AS RATING
    FROM   CLIENTES C1   /* Se ha eliminado la tabla FACTURAS_VENTA FV1 */

    WHERE  C1.ESTADO_SEGURO = 6
    GROUP BY C1.CODIGO, C1.NOMBRE, C1.NIF, C1.SEGURO_IMPORTE_REAL, C1.IMPORTE_ASEGURADO 

    lunes, 22 de enero de 2018 17:09

Respuestas

  • revisa ambos planes de ejecución  de todas formas parece que el c1.estadosegro=6 filtra muchos datos y probablemente por ahí venta la diferencia, en la segunda query casi seguro que filtra primero por ahí. De todas formas, decirlo así es muy atrevido porque SQL ser basa en las estadísticas de distribución de los datos, y eso no es fácil de inferir por humanos. 

    En resumen, mira el plan de ejecución y siempre  sigue la máxima de primero filtra cuanto mas mejor y luego suma. 

    En la segunda, revista también que algunos subselects usas clientes otra vez(como c2) cuando en realidad no parece que sea necesario en absoluto 


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    lunes, 22 de enero de 2018 17:28
    Moderador

Todas las respuestas

  • revisa ambos planes de ejecución  de todas formas parece que el c1.estadosegro=6 filtra muchos datos y probablemente por ahí venta la diferencia, en la segunda query casi seguro que filtra primero por ahí. De todas formas, decirlo así es muy atrevido porque SQL ser basa en las estadísticas de distribución de los datos, y eso no es fácil de inferir por humanos. 

    En resumen, mira el plan de ejecución y siempre  sigue la máxima de primero filtra cuanto mas mejor y luego suma. 

    En la segunda, revista también que algunos subselects usas clientes otra vez(como c2) cuando en realidad no parece que sea necesario en absoluto 


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    lunes, 22 de enero de 2018 17:28
    Moderador
  • He detectado el problema; el problema está en el "INNER JOIN FACTURAS_VENTA FV1 ON C1.CODIGO = FV1.CODIGO_CLIENTE" de la consulta que filtra. Al parecer relentiza las operaciones SUM(XXXX) que se hacen en las columnas de la "SELECT".

    He revisado el plan de manera gráfica y he visto que la primera operativa que se hace es la parte de filtrado junto con el INNER JOIN, aquí también se hace una de las sumas, después para cada resultado obtenido o registro se hacen el resto de las  SUM(xxxx).

    Parece ser que al motor de búsqueda no le gusta mezclar SUMAS realizadas en columnas con SUMAS realizadas en la consulta primaria. ¿Es esto un error del motor de búsqueda de la base de datos?.

    Como nota te adjunto  la solución al problema; sin embargo quisiera saber el porque del comportamiento anterior, gracias:

    DECLARE @FACTURAS_VENTA_TMP TABLE( NUMERO NVARCHAR(15) PRIMARY KEY,
                                       CODIGO bigint not NULL,                                   
                                       CODIGO_FORMA_PAGO int,
                                       FECHA DateTime,
                                       FECHA_PAGO_VTO1 DateTime,                                  
                                       BI float,                                  
                                       IVA float,
                                       TOTAL float,                                  
                                       NOMBRE Varchar(200),
                                       NIF NVARCHAR(15),
                                       SEGURO_IMPORTE_REAL FLOAT,
                                       IMPORTE_ASEGURADO FLOAT);    
                                                                                
    INSERT INTO @FACTURAS_VENTA_TMP
         SELECT FV.NUMERO, FV.CODIGO_CLIENTE, FV.CODIGO_FORMA_PAGO,
                FV.FECHA, FV.FECHA_PAGO_VTO1, FV.BI, FV.IVA, FV.TOTAL,
                C1.NOMBRE, C1.NIF,C1.SEGURO_IMPORTE_REAL, IMPORTE_ASEGURADO
         FROM   FACTURAS_VENTA FV
         INNER  JOIN CLIENTES C1 ON (C1.CODIGO = FV.CODIGO_CLIENTE)
         WHERE  (C1.ESTADO_SEGURO = 6) AND      
                (FV.FECHA BETWEEN '20171201 00:00:00' AND '20171231 23:59:59');
    DECLARE @FACTURAS_TOTAL_TMP TABLE( CODIGO_CLIENTE bigint not NULL PRIMARY KEY,
                                       TOTAL_CONTADO float,
                                       TOTAL_CREDITO float);        
      
    INSERT INTO @FACTURAS_TOTAL_TMP
        SELECT FV2.CODIGO,
               SUM(CASE WHEN ((FORMAS_PAGO.CREDITO_CONTADO = 1) OR (FORMAS_PAGO.CODIGO = 17)) THEN
                        FV2.TOTAL ELSE NULL END) AS TOTAL_CONTADO,
         SUM(CASE WHEN ((FORMAS_PAGO.CREDITO_CONTADO = 0) AND (FORMAS_PAGO.CODIGO <> 17)) THEN
                        FV2.TOTAL ELSE NULL END) AS TOTAL_CREDITO
           FROM  @FACTURAS_VENTA_TMP FV2
        INNER JOIN FORMAS_PAGO ON FV2.CODIGO_FORMA_PAGO = FORMAS_PAGO.CODIGO
        GROUP BY FV2.CODIGO; 
    SELECT FV.NOMBRE + CAST(FV.CODIGO AS NVARCHAR) AS ORDEN, FV.CODIGO, FV.NOMBRE, FV.NIF, FV.SEGURO_IMPORTE_REAL,      
           FV.IMPORTE_ASEGURADO AS IMPORTE_CONCEDIDO, FV.NUMERO, FV.FECHA, FV.FECHA_PAGO_VTO1 ,FV.BI, FV.IVA, FV.TOTAL,
           FT.TOTAL_CONTADO , FT.TOTAL_CREDITO,     
           (SELECT TOP 1 CAST(RATING AS NVARCHAR) + ' (' +CAST(ANYO_BALANCE AS NVARCHAR)+ ')'       
             FROM CLIENTES_INFORMA_DETALLE       
             WHERE (NIF = FV.NIF)       
             ORDER BY ANYO_BALANCE DESC) AS RATING
    FROM  @FACTURAS_VENTA_TMP FV
    LEFT  JOIN @FACTURAS_TOTAL_TMP FT ON (FT.CODIGO_CLIENTE = FV.CODIGO)
    ORDER BY FV.NOMBRE+CAST(FV.CODIGO AS NVARCHAR), FV.FECHA DESC

     
    • Editado Sergio_GPZ miércoles, 24 de enero de 2018 11:30
    miércoles, 24 de enero de 2018 11:23