none
Performance... Consulta para CURVA ABC muitoo LENTO RRS feed

  • Pergunta

  • Pessoal Boa Tarde, 

    Analisem esta Query e me falem onde eu estou pecando.

    Ela esta demorando em torno de 10 minutos para retornar os registros. a quantidade de registro é em torno de "3 mil registros"

    create table ##abcClientes 
    (
    	Posicao int, 
    	CardCode varchar (max),
    	CardName varchar (max),            
    	ano int,
    	Total float, 
    	TotalGeral float, 
    	Perc float, 
    	PercAcum float 
    )
    
    
    Declare 
    @ano int,                                          
    @a int,
    @b int
    
    set @a = :CurvaA;
    set @b = :CurvaB;  
    Set @ano = :ANO;
    
    ;WITH Res(
    Posicao, 
    CardCode, 
    CardName, 
    ano, 
    Total, 
    TotalGeral ) AS ( 
    
    SELECT
    ROW_NUMBER() OVER (ORDER BY Total DESC) AS Posicao,
    CardCode,
    CardName,
    ano,
    Total, 
    TotalGeral
    
    FROM ( 
    
    SELECT 
    CardCode,
    CardName,
    ano,
    
    (SELECT Sum(U_TipoProd * ((price) / (1 - DiscPrcntInv1 / 100) * Quantity)) -
    (Sum(U_TipoProd *(LineTotal *  DiscPrcntOinv /100)) +
    (Sum((price) / (1 - DiscPrcntInv1 / 100) * Quantity) - sum(linetotal)))          
    FROM dbo.HV_GER
    WHERE DATEPART ( Year , DocDate) = ''+@ano+''
    And CardCode = lcm.CardCode    
    AND CANCELED = 'N' 
    AND TaxOnly = 'N'      
    AND NOT EXISTS (SELECT 1 AS STATUS 
    FROM RIN1 R 
    INNER JOIN ORIN O WITH(NOLOCK) ON (R.DocEntry = O.DocEntry) 
    WHERE  R.BaseEntry = dbo.HV_GER.docNum 
    AND O.SeqCode = 1) 
    ) Total,
    
    (Select sum(U_TipoProd * ((price) / (1 - DiscPrcntInv1 / 100) * Quantity)) -
    (Sum(U_TipoProd *(LineTotal *  DiscPrcntOinv /100)) + 
    (sum((price) / (1 - DiscPrcntInv1 / 100) * Quantity) - sum(linetotal)))          
    From dbo.HV_GER
    WHERE DATEPART ( Year , DocDate) = ''+@ano+''  
    AND CANCELED ='N' 
    AND TaxOnly = 'N'    
    AND NOT EXISTS (SELECT 1 AS STATUS 
    FROM RIN1 R 
    INNER JOIN ORIN O WITH(NOLOCK) ON (R.DocEntry = O.DocEntry) 
    WHERE  R.BaseEntry = dbo.HV_GER.docNum 
    AND O.SeqCode = 1)     
    ) TotalGeral
    
     FROM (
    
    SELECT 
    CardCode,
    CardName,
    DATEPART ( YEAR , DocDate ) as ano
    
    FROM dbo.HV_GER
    
    WHERE DATEPART ( Year , DocDate) = ''+@ano+'' 
    AND CANCELED = 'N' 
    AND TaxOnly = 'N'    
    AND NOT EXISTS (SELECT 1 AS STATUS 
    FROM RIN1 R 
    INNER JOIN ORIN O WITH(NOLOCK) ON (R.DocEntry = O.DocEntry) 
    WHERE  R.BaseEntry = dbo.HV_GER.docNum 
    AND O.SeqCode = 1)   
    					
    GROUP BY 
    CardCode,
    CardName,
    DATEPART (YEAR , DocDate) )lcm )lcm2  
     
    ),
    
    QPerc 
    (
    Posicao,
    CardCode,
    CardName,
    ano,
    Total, 
    TotalGeral,
    Perc 
    ) 
    As (
    
    SELECT
    Posicao,
    CardCode,
    CardName,
    ano, 
    Total, 
    TotalGeral,
    ROUND(Total / CAST(TotalGeral As Decimal(12,2)),4) As Perc
    				
    FROM Res )
    
    INSERT INTO ##abcClientes 
    
    SELECT
    Posicao,
    CardCode,
    CardName,
    ano, 
    Total,
    TotalGeral,
    Perc,
    ROUND((SELECT SUM(TInt.Perc) FROM QPerc As TInt WHERE TInt.Posicao <= TOut.Posicao),4) PercAcum FROM QPerc As TOut 
    
    
    ;WITH Curva (Valor) As (
    SELECT (cast(''+@a+'' as float))/(cast(100 as float)) UNION ALL
    SELECT (cast(''+@b+'' as float))/(cast(100 as float)) UNION ALL
    SELECT 1.00)
    
    SELECT
    Posicao,
    CardCode,
    CardName,
    ano,
    Total,
    TotalGeral,    
    Perc,
    PercAcum,
    MIN(Valor) As Valor, CASE
            WHEN MIN(Valor) = (cast(''+@a+'' as float))/(cast(100 as float)) THEN 'A'
            WHEN MIN(Valor) = (cast(''+@b+'' as float))/(cast(100 as float)) THEN 'B'
            ELSE 'C' END As Curva
    
    FROM ##abcClientes As C
    
    INNER JOIN Curva As I ON C.PercAcum <= I.Valor
    
    GROUP BY   
    Posicao,
    CardCode,
    CardName,
    ano,
    Total,
    TotalGeral,    
    Perc,
    PercAcum
    
    drop table ##abcClientes



    quarta-feira, 10 de março de 2010 18:32

Respostas

  • Boa Tarde,

    Acho que sua query está um pouco "poluída" e talvez não fosse necessário tantos passos para montar a curva ABC. Tenho um artigo sobre esse assunto com um código mais "enxuto".

    O princípio de Pareto, a curva ABC e consultas SQL
    http://gustavomaiaaguiar.spaces.live.com/Blog/cns!F4F5C630410B9865!740.entry

    Algumas partes do seu código se assemelham ao que propûs no artigo. De fato o código retorna, mas há um pequeno "detalhe" que pode tornar o desempenho lento. Pretendo trabalhar esse "detalhe" em um artigo posterior, mas ultimamente ando sem tempo para explorá-lo. O fato é que o uso de subqueries é exponencialmente prejudicial ao desempenho e utilizar três mil produtos pode pesar bastante (talvez você devesse categorizá-los, pois, não é comum expor um relatório ABC com tantos produtos).

    Um ponto de partida (Workaround) seria você otimizar as consultas iniciais (e não propriamente o ABC). Suas consultas não estão performáticas, pois, há predicados que utilizam funções. Ex: DATEPART (Year, DocDate)

    Isso é extremamente prejudicial, pois, ao invés de utilizar um índice (caso exista) para pesquisa será necessário um SCAN na tabela ou no índice. Sugiro rever essa consulta por outra mais eficiente. EX: DocDate BETWEEN 'Ano-01-01' AND 'Ano+1-01-01'. Veja também que funções como YEAR retornam um INT e você está comparando com um texto (mais conversões implícitas aí)

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    Como descobrir a data do último acesso a uma tabela ?
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!964.entry


    Classifique as respostas. O seu feedback é imprescindível
    quarta-feira, 10 de março de 2010 21:02

Todas as Respostas

  • Thiago,

    Você poderia explicar um pouco mais sobre sua query?
    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    quarta-feira, 10 de março de 2010 19:53
  • Boa Tarde,

    Acho que sua query está um pouco "poluída" e talvez não fosse necessário tantos passos para montar a curva ABC. Tenho um artigo sobre esse assunto com um código mais "enxuto".

    O princípio de Pareto, a curva ABC e consultas SQL
    http://gustavomaiaaguiar.spaces.live.com/Blog/cns!F4F5C630410B9865!740.entry

    Algumas partes do seu código se assemelham ao que propûs no artigo. De fato o código retorna, mas há um pequeno "detalhe" que pode tornar o desempenho lento. Pretendo trabalhar esse "detalhe" em um artigo posterior, mas ultimamente ando sem tempo para explorá-lo. O fato é que o uso de subqueries é exponencialmente prejudicial ao desempenho e utilizar três mil produtos pode pesar bastante (talvez você devesse categorizá-los, pois, não é comum expor um relatório ABC com tantos produtos).

    Um ponto de partida (Workaround) seria você otimizar as consultas iniciais (e não propriamente o ABC). Suas consultas não estão performáticas, pois, há predicados que utilizam funções. Ex: DATEPART (Year, DocDate)

    Isso é extremamente prejudicial, pois, ao invés de utilizar um índice (caso exista) para pesquisa será necessário um SCAN na tabela ou no índice. Sugiro rever essa consulta por outra mais eficiente. EX: DocDate BETWEEN 'Ano-01-01' AND 'Ano+1-01-01'. Veja também que funções como YEAR retornam um INT e você está comparando com um texto (mais conversões implícitas aí)

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    Como descobrir a data do último acesso a uma tabela ?
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!964.entry


    Classifique as respostas. O seu feedback é imprescindível
    quarta-feira, 10 de março de 2010 21:02
  • Pessoal, eu descobri o que esta causando essa lentidão na minha consulta...

     

     

    Declare 
    @ano int,                     
    @a int,
    @b int
    
    set @a = '20';
    set @b = '40'; 
    Set @ano = '2009';
    
    ;WITH Res(
    Posicao, 
    CardCode, 
    CardName, 
    ano, 
    Total, 
    TotalGeral ) AS ( 
    
    SELECT
    ROW_NUMBER() OVER (ORDER BY Total DESC) AS Posicao,
    CardCode,
    CardName,
    ano,
    Total, 
    TotalGeral
    
    FROM ( 
    
    SELECT 
    CardCode,
    CardName,
    ano,
    
    (SELECT Sum(U_TipoProd * ((price) / (1 - DiscPrcntInv1 / 100) * Quantity)) -
    (Sum(U_TipoProd *(LineTotal * DiscPrcntOinv /100)) +
    (Sum((price) / (1 - DiscPrcntInv1 / 100) * Quantity) - sum(linetotal)))     
    FROM dbo.HV_GER
    WHERE DATEPART ( Year , DocDate) = ''+@ano+''
    And CardCode = lcm.CardCode  
    AND CANCELED = 'N' 
    AND TaxOnly = 'N'   
    AND NOT EXISTS (SELECT 1 AS STATUS 
    FROM RIN1 R 
    INNER JOIN ORIN O WITH(NOLOCK) ON (R.DocEntry = O.DocEntry) 
    WHERE R.BaseEntry = dbo.HV_GER.docNum 
    AND O.SeqCode = 1) 
    ) Total,
    
    (Select sum(U_TipoProd * ((price) / (1 - DiscPrcntInv1 / 100) * Quantity)) -
    (Sum(U_TipoProd *(LineTotal * DiscPrcntOinv /100)) + 
    (sum((price) / (1 - DiscPrcntInv1 / 100) * Quantity) - sum(linetotal)))     
    From dbo.HV_GER
    WHERE DATEPART ( Year , DocDate) = ''+@ano+'' 
    AND CANCELED ='N' 
    AND TaxOnly = 'N'  
    AND NOT EXISTS (SELECT 1 AS STATUS 
    FROM RIN1 R 
    INNER JOIN ORIN O WITH(NOLOCK) ON (R.DocEntry = O.DocEntry) 
    WHERE R.BaseEntry = dbo.HV_GER.docNum 
    AND O.SeqCode = 1)   
    ) TotalGeral
    
     FROM (
    
    SELECT 
    CardCode,
    CardName,
    DATEPART ( YEAR , DocDate ) as ano
    
    FROM dbo.HV_GER
    
    WHERE DATEPART ( Year , DocDate) = ''+@ano+'' 
    AND CANCELED = 'N' 
    AND TaxOnly = 'N'  
    AND NOT EXISTS (SELECT 1 AS STATUS 
    FROM RIN1 R 
    INNER JOIN ORIN O WITH(NOLOCK) ON (R.DocEntry = O.DocEntry) 
    WHERE R.BaseEntry = dbo.HV_GER.docNum 
    AND O.SeqCode = 1)  
    					
    GROUP BY 
    CardCode,
    CardName,
    DATEPART (YEAR , DocDate) )lcm )lcm2 
     
    ),
    
    QPerc 
    (
    Posicao,
    CardCode,
    CardName,
    ano,
    Total, 
    TotalGeral,
    Perc 
    ) 
    As (
    
    SELECT
    Posicao,
    CardCode,
    CardName,
    ano, 
    Total, 
    TotalGeral,
    ROUND(Total / CAST(TotalGeral As Decimal(12,4)),4) As Perc
    				
    FROM Res )
    
    --INSERT INTO ##abcClientes 
    
    SELECT
    Posicao,
    CardCode,
    CardName,
    ano, 
    Total,
    TotalGeral,
    Perc,
    (ROUND((SELECT SUM(TInt.Perc) FROM QPerc As TInt WHERE TInt.Posicao <= TOut.Posicao),4) * 100 ) AS PercAcum -- esta nessa linha
    
    FROM QPerc As TOut -- esta nessa linha... 
    Pra mim fazer a consulta de ABC eu preciso do Percentual Acumulado, teria outra meneira mais rapida de fazer o Percentual Acumulado ?


     

    sexta-feira, 23 de abril de 2010 20:04