none
Saldo Acumulado muito lento! Ajuda em montagem de query. RRS feed

  • Pergunta

  • Fiz uma query para cálculo da Curva ABC que calcula o percentual relativo de cada item. Esta query é executada rapidamente, em apenas 2 segundos.
    Quando tento criar um campo complementar com o saldo acumulado do percentual, a query fica extremamente lenta, demorando minutos para terminar.
    O que estou fazendo de errado? Já tentei criar o saldo de 4 formas diferentes que encontrei no fórum, esta foi a mais eficiente:

    WITH Curva (Pos, [Descrição do Grupo], [Valor Vendido], [Total Vendido], Percentual) AS 
    (
    SELECT * FROM
    (
    SELECT ROW_NUMBER() OVER (ORDER BY Percentual DESC) AS Pos, * FROM
    (
    SELECT *, CAST(ROUND([Valor Vendido] / CAST([Total Vendido] As Decimal(12, 4)) * 100, 4) AS DECIMAL(12, 4)) As Percentual FROM
    (
    SELECT *, SUM([Valor Vendido]) OVER (PARTITION BY 1) AS [Total Vendido] FROM
    (
    SELECT
     [Descrição do Grupo],
     SUM([Vendas Produtos].[Valor Total]) AS [Valor Vendido]
    FROM 
     [Vendas Produtos] 
     INNER JOIN Produtos ON [Vendas Produtos].[Código do Produto] = Produtos.[Código do Produto] 
     INNER JOIN Grupos ON Produtos.[Código do Grupo] = Grupos.[Código do Grupo] 
    GROUP BY
     [Descrição do Grupo]
    ) AS Grupos
    ) AS Vendas
    ) AS Posição
    ) AS ABC 

    SELECT *, (SELECT SUM(Percentual) FROM Curva AS [Curva Anterior] WHERE [Curva Anterior].Pos <= Curva.Pos) AS Acumulado FROM Curva



    Acredito que o sql refaça desnecessariamente e recursivamente o cálculo da Curva, mas não consigo evitar isso!!
    A tabela [Vendas Produtos] tem +- 500.000 registros, mas não é o motivo da lentidão pois se eu terminar com um 'SELECT * FROM Curva' (que retorna apenas 235 registros) a query fica rápida.
    Tão rápida que se eu fizesse o cálculo do percentual acumulado num papel conseguiria terminar antes do sql.


    PHB
    • Movido Gustavo Maia Aguiar segunda-feira, 5 de outubro de 2009 12:46 (De:SQL Server - Desenvolvimento Geral)
    sexta-feira, 2 de outubro de 2009 20:19

Todas as Respostas

  • Pablo,

    Você já tentou analisar o plano de execução desta query?
    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    sábado, 3 de outubro de 2009 00:46
  • Boa Noite,

    É bem gratificante ver que um artigo meu produz resultados...

    Observe que a coluna de acumulado é baseada na coluna Pos que por sua vez é uma coluna derivada pela função Row_Number sobre a coluna Percentual que é uma coluna calculada com base em outras subqueries, etc.

    O fato é que o predicado [Curva Anterior].Pos <= [Curva].Pos irá produzir um acesso em nível de uma progressão aritmética. Para o primeiro registro será lido um registro, para o segundo registro será lido dois registros, para o terceiro registro será lido três registros. A fórmula da soma de uma progressão aritmérica é N * (N + 1) / 2 sendo que N será a quantidade de registros. Se você for ler por exemplo 235 registros, o cálculo da coluna Saldo irá representar a leitura de 27730 registros. Se você utilizar a mesma fórmula para 500.000 registros no cálculo do Saldo não é de se estranhar que demore tanto, pois, pela fórmula haverá uma quantidade absurda de registros.

    Essa é uma das raríssimas situações em que um cursor (só para o Saldo) certamente será mais eficiente. Eu já cheguei a essa conclusão em outras situações e futuramente detalharei esse exemplo em um artigo.

    Se for realmente imprescindível elaborar uma consulta dessa forma, você poderá construir uma View Indexada ou salvar os resultados de forma intermediária em uma tabela temporária e indexá-la. Isso certamente irá reduzir a quantidade de leituras em tempo de execução por conta da coluna derivada do Row_Number

    [ ]s,
     
    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    O princípio de Pareto, a curva ABC e consultas SQL
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!740.entry
    Classifique as respostas. O seu feedback é imprescindível
    sábado, 3 de outubro de 2009 01:52