# 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```

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