Usuário com melhor resposta
Performance... Consulta para CURVA ABC muitoo LENTO

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
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.comComo 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- Marcado como Resposta Thiago Batista Ferreira quarta-feira, 8 de setembro de 2010 12:26
Todas as 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.comComo 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- Marcado como Resposta Thiago Batista Ferreira quarta-feira, 8 de setembro de 2010 12:26
-
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 ?