Performance... Consulta para CURVA ABC muitoo LENTO

Question

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

Answer 1

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

Answer 2

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

Answer 3

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 ?