Reposta–Desafio Performance
Galera vamos lá, resposta para o desafio de performance que postei a alguns dias atrás.
Primeiramente eu quero agradecer a todos que participaram e me enviaram sugestões de códigos com melhorias para a consulta.
· Alberto Lima – Microsoft Brasil
· Rodrigo Souza (twitter) – Microsoft Brasil
· Omid Afzalalghom – Itaú BBA
· Thiago Alencar (twitter|blog) – DBA Saraiva
· Alex Rosa (blog) – IBM
· Alan Victor – CNP
· Evandro Camara – Sight Business Intelligence
Se eu me esqueci de você é só me xingar aqui com um comentário que atualizo o post J… Valeu mesmo por terem participado. Se você tentou, mas não me mandou sua solução… mande, mesmo que só para dizer, ei, eu fiz assim… o que acha?
Como divulgado no post original, o Evandro conseguiu uma solução sensacional usando uma view indexada para evitar o acesso à tabela com milhões de linhas. O código da view é o seguinte:
USE tempdb
GO
IF OBJECT_ID(‘vw_OrdersBig_TestPerf’) IS NOT NULL
DROP VIEW vw_OrdersBig_TestPerf
GO
CREATE VIEW vw_OrdersBig_TestPerf
WITH SCHEMABINDING
AS
SELECT CustomerID,
CountCol,
OrderDate,
SUM_Value = SUM(Value),
COUNT_BIG = COUNT_BIG(*)
FROM dbo.OrdersBig
GROUP BY CustomerID,
CountCol,
OrderDate
GO
CREATE UNIQUE CLUSTERED INDEX PK_vw_OrdersBig_TestPerf ON dbo.vw_OrdersBig_TestPerf (CustomerID, CountCol, OrderDate)
GO
Existem duas cosias bem interessantes na solução do Evandro, primeiro, eu já falei várias vezes que views indexadas são espetaculares para melhoria de performance de consultas, principalmente para consultas com agregações, quem já fez treinamento comigo sabe disso. Sim ela tem um custo, e muitas vezes, alto, mas dependendo do cenário, ela pode sim ser utilizada. Outro fato interessante é que o otimizador de consultas trabalha muito bem para identificar que existe uma view indexada que contém os dados desejados pela consulta… como assim?… Olha só, mesmo rodando a query original, o SQL Server gera o seguinte plano de execução:
Como podemos observar no plano acima, mesmo não fazendo select na view o SQL Server utiliza o índice da view indexada para evitar acessar a tabela com 5 milhões de linhas, com isso ele lê bem menos dados, sendo mais preciso ele lê uma “tabela” (view indexada) com apenas 975 páginas contra 35921 páginas da tabela OrdersBig. Só com isso já temos um ganho muito grande.
Novamente, Parabéns Evandro.
O ganhador da solução sem utilizar view indexada, foi o Alberto Lima, mas antes de falar sobre a solução dele, vamos analisar a minha solução e ver alguns pontos importantes sobre a consulta original:
SELECT a.CustomerID,
a.CountCol,
CASE a.CountCol
WHEN ‘Count’ THEN COUNT(1)
WHEN ‘CountDistinct’ THEN COUNT(DISTINCT a.OrderDate)
WHEN ‘CountDistinct_1’ THEN COUNT(DISTINCT 1)
ELSE NULL
END AS Cnt,
CASE (SELECT AVG(b.Value)
FROM OrdersBig b
WHERE b.CustomerID = a.CustomerID)
WHEN 1000 THEN ‘Média = 1 mil’
WHEN 2000 THEN ‘Média = 2 mil’
WHEN 3000 THEN ‘Média = 3 mil’
WHEN 4000 THEN ‘Média = 4 mil’
WHEN 5000 THEN ‘Média = 5 mil’
ELSE ‘Não é número exato’
END AS Sts
FROM OrdersBig AS a
GROUP BY a.CustomerID, a.CountCol
ORDER BY a.CustomerID
OPTION (MAXDOP 1)
Existem 3 problemas na consulta acima.
1. A clausula COUNT(DISTINCT 1) não faz nenhum sentido
2. O CASE com a subquery faz com que o SQL execute a subquery para cada valor analisado no case.
3. A clausula COUNT(DISTINCT a.OrderDate) é o grande problema de performance da consulta
O plano pode ser dividido em duas partes, primeiro para calcular o COUNT + COUNT(DISTINCT)
E depois a parte de CASE +SubQuery:
Vamos resolver os problemas por partes, primeiro eliminando um passo do plano trocando o “COUNT(DISTINCT 1)” por “1”. Concordam que “COUNT(DISTINCT 1)” é sempre igual a “1”? O mais irritante é que o otimizador de consultas não identifica isso sozinho.
Outra alteração que podemos fazer é em relação ao CASE + SubQuery, uma forma muito simples de resolver este problema é não usar a subquery como expressão para o CASE, ou seja, trocamos isso:
…
END AS Cnt,
CASE (SELECT AVG(b.Value)
FROM OrdersBig b
WHERE b.CustomerID = a.CustomerID)
WHEN 1000 THEN ‘Média = 1 mil’
WHEN 2000 THEN ‘Média = 2 mil’
WHEN 3000 THEN ‘Média = 3 mil’
WHEN 4000 THEN ‘Média = 4 mil’
WHEN 5000 THEN ‘Média = 5 mil’
ELSE ‘Não é número exato’
END AS Sts
FROM OrdersBig AS a
…
Por isso:
…
END AS Cnt,
(SELECT CASE AVG(b.Value)
WHEN 1000 THEN ‘Média = 1 mil’
WHEN 2000 THEN ‘Média = 2 mil’
WHEN 3000 THEN ‘Média = 3 mil’
WHEN 4000 THEN ‘Média = 4 mil’
WHEN 5000 THEN ‘Média = 5 mil’
ELSE ‘Não é número exato’
END AS Sts
FROM OrdersBig b
WHERE b.CustomerID = a.CustomerID) AS Sts
FROM OrdersBig AS a
…
Utilizando o AVG(b.Value) como expressão para o CASE evitamos o problema de execução da subquery para cada valor na lista do CASE.
Após efetuar estas duas alterações temos o seguinte plano de execução:
SELECT a.CustomerID,
a.CountCol,
CASE a.CountCol
WHEN ‘Count’ THEN COUNT(1)
WHEN ‘CountDistinct’ THEN COUNT(DISTINCT a.OrderDate)
WHEN ‘CountDistinct_1’ THEN 1
ELSE NULL
END AS Cnt,
(SELECT CASE AVG(b.Value)
WHEN 1000 THEN ‘Média = 1 mil’
WHEN 2000 THEN ‘Média = 2 mil’
WHEN 3000 THEN ‘Média = 3 mil’
WHEN 4000 THEN ‘Média = 4 mil’
WHEN 5000 THEN ‘Média = 5 mil’
ELSE ‘Não é número exato’
END AS Sts
FROM OrdersBig b
WHERE b.CustomerID = a.CustomerID) AS Sts
FROM OrdersBig a
GROUP BY a.CustomerID, a.CountCol
ORDER BY a.CustomerID
OPTION (MAXDOP 1)
Uau, já ficou MUITO mais simples não é? O problema agora é que estou acessando fazendo um scan na tabela OrdersBig 3 vezes.
Outro problema que nos resta, é o “COUNT (DISTINCT OrderDate)”, para resolver este problema eu mudei um pouco a forma de solicitar esta informação, ou invés de usar o COUNT DISTINCT eu usei a ROW_NUMBER particionando a janela por CustomerID e CountCol e depois contei a quantidade de valores igual a 1.
Vamos criar um cenário mais simples para entender o conceito:
IF OBJECT_ID(‘Tab1’) IS NOT NULL
DROP TABLE Tab1
GO
CREATE TABLE Tab1 (Col1 Int, Col2 Int)
GO
INSERT INTO Tab1 VALUES(1, 1), (1, 1), (1, 1), (2, 1), (2, 1), (3, 1), (3, 1)
GO
SELECT Col1,
COUNT(Col2) AS "Count", — Palavra reservada
COUNT(DISTINCT Col2) AS CountDistict
FROM Tab1
GROUP BY Col1
GO
Vamos analisar o conceito que mencionei acima passo a passo, primeiro vamos gerar o ROW_NUMBER particionando por Col1 com base na ordem de Col2.
SELECT *,
ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col2) AS rn
FROM Tab1
Agora se eu contar a quantidade de ocorrências de “1” agrupando por Col1 terei o resultado esperado concordam? … Para fazer isso vamos colocar a consulta em uma CTE e fazer um CASE para retornar apenas os valores igual a 1.
WITH CTE_1
AS
(
SELECT *,
CASE
WHEN ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col2) = 1 THEN 1
ELSE NULL
END AS rn
FROM Tab1
)
SELECT *
FROM CTE_1
Agora podemos simplesmente fazer um COUNT na coluna RN que os valores NULL serão ignorados e teremos o mesmo resultado que o COUNT DISTINCT. Vejamos:
WITH CTE_1
AS
(
SELECT *,
CASE
WHEN ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col2) = 1 THEN 1
ELSE NULL
END AS rn
FROM Tab1
)
SELECT Col1,
COUNT(Col2) AS "Count", — Palavra reservada
COUNT(rn) AS CountDistict
FROM CTE_1
GROUP BY Col1
Agora conseguimos resolver a consulta com apenas um scan na tabela, porém agora temos um novo problema, o SORT por Col1 (Clausula PARTITION BY) + Col2 (Clausula ORDER BY), mas esse é fácil de resolver certo? … Basta criar um índice por Col1 e Col2. Bora fazer isso.
CREATE INDEX ix1 ON Tab1 (Col1, Col2)
Agora temos o seguinte plano:
Nice and clean!
Voltando para nosso cenário, a consulta ficaria assim:
— Criando o índice para evitar o Sort e para cobrir a SubQuery
CREATE INDEX ix1 ON OrdersBig (CustomerID, CountCol, OrderDate) INCLUDE(Value) WITH(DATA_COMPRESSION=PAGE)
GO
;WITH CTE_1
AS
(
SELECT CustomerID,
CountCol,
OrderDate,
CASE
WHEN ROW_NUMBER() OVER(PARTITION BY CustomerID, CountCol, OrderDate ORDER BY OrderDate) = 1 THEN 1
ELSE NULL
END AS DistinctCnt
FROM OrdersBig
)
SELECT CustomerID,
CountCol,
CASE CountCol
WHEN ‘Count’ THEN COUNT(1)
WHEN ‘CountDistinct’ THEN COUNT(DistinctCnt)
WHEN ‘CountDistinct_1’ THEN 1
ELSE NULL
END AS Cnt,
(SELECT CASE AVG(b.Value)
WHEN 1000 THEN ‘Média = 1 mil’
WHEN 2000 THEN ‘Média = 2 mil’
WHEN 3000 THEN ‘Média = 3 mil’
WHEN 4000 THEN ‘Média = 4 mil’
WHEN 5000 THEN ‘Média = 5 mil’
ELSE ‘Não é número exato’
END AS Sts
FROM OrdersBig b
WHERE b.CustomerID = CTE_1.CustomerID) AS Sts
FROM CTE_1
GROUP BY CustomerID, CountCol
ORDER BY CustomerID
OPTION (MAXDOP 1)
Temos o seguinte plano:
Olhos atentos devem ter reparado que eu criei o índice utilizando a clausula “DATA_COMPRESSION = PAGE”, isso faz muito diferença na leitura do índice, já que terei que varrer a tabela ;-).
Outro ponto importantíssimo em relação a performance desta consulta é que o mesmo índice esta sendo utilizado duas vezes, primeiro um Index Scan é realizado já que esta é a primeira vez que os dados estão sendo lidos essa será uma leitura física, quando o Index Seek for realizado os dados já estarão em Cache gerando leituras lógicas. Isso significa que ainda que eu crie outro índice menor (por CustomerID com INCLUDE de Value), a performance da consulta será pior, pois o seek neste novo índice geraria leituras físicas.
Na minha máquina a consulta acima faz o seguinte uso de recursos:
Pra deixar o desafio mais interessante, lanço uma pergunta. Será que da pra fazer isso tudo, com apenas UMA leitura na tabela OrdersBig?
Dá, mas o SQL Server infelizmente ainda não é bem esperto na criação deste plano… Eu poderia evitar a SubQuery do AVG e escrever a consulta assim:
;WITH CTE_1
AS
(
SELECT CustomerID,
CountCol,
OrderDate,
AVG(Value) OVER(PARTITION BY CustomerID) AS Media,
CASE
WHEN ROW_NUMBER() OVER(PARTITION BY CustomerID, CountCol, OrderDate ORDER BY OrderDate) = 1 THEN 1
ELSE NULL
END AS DistinctCnt
FROM OrdersBig
)
SELECT CustomerID,
CountCol,
CASE CountCol
WHEN ‘Count’ THEN COUNT(1)
WHEN ‘CountDistinct’ THEN COUNT(DistinctCnt)
WHEN ‘CountDistinct_1’ THEN 1
ELSE NULL
END AS Cnt,
CASE Media
WHEN 1000 THEN ‘Média = 1 mil’
WHEN 2000 THEN ‘Média = 2 mil’
WHEN 3000 THEN ‘Média = 3 mil’
WHEN 4000 THEN ‘Média = 4 mil’
WHEN 5000 THEN ‘Média = 5 mil’
ELSE ‘Não é número exato’
END AS Sts
FROM CTE_1
GROUP BY CustomerID, CountCol, Media
ORDER BY CustomerID
OPTION (MAXDOP 1)
Infelizmente a operação de SORT é totalmente desnecessária, mas o SQL Server continua gerando o SORT… isso é um BUG que eu já reclamei, e que foi fechado pela Micosoft como “By Design”… anyway, não vou entrar no mérito aqui se isso é bug ou não é… o que espero é que em novas versões do produto a MS de mais atenção para esse tipo de funcionalidade.
A solução ganhadora (do Alberto Lima), é bem interessante porque usa índices filtrados, segue o script completo:
CREATE TABLE dbo.Tmp_OrdersBig
(
[OrderID] [int] IDENTITY(1,1) PRIMARY KEY NONCLUSTERED NOT NULL,
[CustomerID] [int] NULL,
[OrderDate] [date] NULL,
[Value] [numeric](18, 2) NOT NULL,
[CountCol] [varchar](20) NULL,
) ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_OrdersBig SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT TMP_ORDERSBIG ON
go
IF EXISTS(SELECT * FROM dbo.OrdersBig)
EXEC(‘INSERT INTO dbo.Tmp_OrdersBig (OrderID, CustomerID, OrderDate, Value, CountCol)
SELECT OrderID, CustomerID, OrderDate, Value, CONVERT(char(15), CountCol) FROM dbo.OrdersBig WITH (HOLDLOCK TABLOCKX)’)
GO
SET IDENTITY_INSERT TMP_ORDERSBIG OFF
GO
DROP TABLE dbo.OrdersBig
GO
EXECUTE sp_rename N’dbo.Tmp_OrdersBig’, N’OrdersBig’, ‘OBJECT’
GO
CREATE CLUSTERED INDEX IX_OrdersBigClustered ON dbo.OrdersBig
(
CustomerID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ix_orders_big_CountCol_CountCol_customerid_CountDistinct] ON [dbo].[OrdersBig]
(
[CountCol] ASC,
[CustomerID] ASC,
[OrderDate] ASC
)
WHERE ([CountCol]=‘CountDistinct’)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_orders_big_Countcol_Customerid_Value] ON [dbo].[OrdersBig]
(
[CountCol] ASC,
[CustomerID] ASC,
[Value] ASC
)WITH (DATA_COMPRESSION=PAGE, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
USE [tempdb]
GO
CREATE NONCLUSTERED INDEX [IX_orders_big_CountCol_Customerid_Count] ON [dbo].[OrdersBig]
(
[CountCol] ASC,
[CustomerID] ASC
)
WHERE ([CountCol]=‘Count’)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Script da consulta:
WITH CTE_Customers ( CountCust, CustomerID, CountCol, Value )
AS ( SELECT CountCust = ( CASE WHEN CountCol = ‘Count’
THEN ( SELECT COUNT(1)
FROM OrdersBig e
WHERE CountCol = ‘Count’ and
e.CustomerID = a.CustomerID
)
WHEN CountCol = ‘CountDistinct’
THEN ( SELECT COUNT(DISTINCT d.OrderDate)
FROM OrdersBig d
WHERE CountCol = ‘CountDistinct’ and
d.CustomerID = a.CustomerID
)
WHEN CountCol = ‘CountDistinct_1’
THEN ( SELECT COUNT(DISTINCT 1)
FROM OrdersBig C
WHERE CountCol = ‘CountDistinct_1’ and
c.CustomerID = a.CustomerID
)
ELSE NULL
END ),
CustomerID,
CountCol,
AVG(VALUE) AS Value
FROM OrdersBig AS A
GROUP BY Customerid,
CountCol
)
SELECT a.CustomerID,
a.CountCol,
CountCust as Cnt,
( CASE VALUE
WHEN 1000 THEN ‘Média = 1 mil’
WHEN 2000 THEN ‘Média = 2 mil’
WHEN 3000 THEN ‘Média = 3 mil’
WHEN 4000 THEN ‘Média = 4 mil’
WHEN 5000 THEN ‘Média = 5 mil’
ELSE ‘Não é número exato’
END )
FROM CTE_Customers a
ORDER BY a.CustomerID
OPTION ( MAXDOP 1 );
O plano é o seguinte:
A consulta roda em 4 segundos, e utiliza os seguintes recursos:
Apesar da consulta do Alberto fazer mais leituras de páginas que a minha solução, o tempo foi menor e isso é o que importa.
Eu gostaria muito de saber qual o tempo da minha consulta e da consulta do Alberto na sua máquina, pode testar e postar o resultado em um comentário aqui no blog?
É isso ai galera, espero que tenham gostado… e fiquem de olho que já tenho outro desafio de performance pronto para ser publicado 🙂
Abs.
Deixe um comentário
Fabiano Amorim
SQL Server MVP
Meu livro, Complete ShowPlan Operators
Livro, SQL Server além do conceito
Livro, Diálogos ao vento
Blog - Fabiano Neves Amorim 
Quero meu livro!! hahahah
Vlw Fabiano!
ficou bastante legal o esquema de desafios e soluções. Estou aguardando o próximo….
Parabéns
Abs
Oi Fabiano!
Este foi o primeiro post que leio do seu Blog, e achei muito bom!
Parabéns!
Abçs,
Lílian Barroso
Legal Lilian, espero que goste dos outros posts 🙂
Abs.
Olá Fabiano, Tudo bem?
Em primeiro quero agradecer o desafio e dizer fiquei muito feliz por ganhar.
Ja enviei meus dados por email para o envio do livro.
Entretanto tenho um comentário a fazer com relação ao assunto “BUG”.
A operação de Sort gerada no plano de execução da Query na qual voce tentou gerar em uma linha, não me parece um BUG.
O operador de Sort pra mim faz um “certo” sentido na operação,
uma vez que o Optimizer não tem como garantir que depois das operações de ordenação “diferentes” nas colunas do CTE e
que os dados estejam na mesma ordem depois de gerar os Table Spools.
A parte que me refiro a ordenaçao se encontram abaixo:
AVG(Value) OVER(PARTITION BY CustomerID)
ROW_NUMBER() OVER(PARTITION BY CustomerID, CountCol, OrderDate ORDER BY OrderDate)
A questão de eliminar o “Sort” ou não, me parece mais uma questão de custo/beneficio para garantir (de alguma forma) a ordenação dos dados retornados dos Spools do que um BUG..
Mas o custo/benefico de tal implementação nem eu ou voce vai conseguir discutir ou saber, isso é para o pessoal de DEV do SQL Server Query Optimizer.
Mas isso é uma questão de melhoria da ferramenta, não de BUG.
E concordo com Andrew Richardson quando disse pode ser retirado reescrevendo a query de uma maneira diferente e obter um resultado melhor.
A prova disso é que depois que retirei o AVG da geração do CTE e atribui ao select do CTE, o plano execução passou a ser feito em uma linha, como voce esperava e sem o Sort.
Um abraço,
Alberto Antonio Lima
Segue abaixo o código gerando o plano de execução em uma linha.
WITH CTE_1
AS
(
SELECT CustomerID,
CountCol,
OrderDate,
value,
–AVG(Value) OVER(PARTITION BY CustomerID) AS Media,
CASE
WHEN ROW_NUMBER() OVER(PARTITION BY CustomerID, CountCol, OrderDate ORDER BY OrderDate) = 1 THEN 1
ELSE NULL
END AS DistinctCnt
FROM OrdersBig
)
SELECT CustomerID,
CountCol,
CASE CountCol
WHEN ‘Count’ THEN COUNT(1)
WHEN ‘CountDistinct’ THEN COUNT(DistinctCnt)
WHEN ‘CountDistinct_1’ THEN 1
ELSE NULL
END AS Cnt,
CASE AVG(Value)
WHEN 1000 THEN ‘Média = 1 mil’
WHEN 2000 THEN ‘Média = 2 mil’
WHEN 3000 THEN ‘Média = 3 mil’
WHEN 4000 THEN ‘Média = 4 mil’
WHEN 5000 THEN ‘Média = 5 mil’
ELSE ‘Não é número exato’
END AS Sts
FROM CTE_1
GROUP BY CustomerID, CountCol
ORDER BY CustomerID
OPTION (MAXDOP 1)
Obrigado pelo comentario Alberto… eu estou de ferias por isso vou ser breve, sem entrar no merito do bug ou melhoria … mas vamos voltar a falar sobre isso com certeza
Sobre a sua query… vc nao pode colocar o avg na query pq o resultado nao sera o mesmo… o avg deve ser por customerid e nao por customerid + countcol …
concorda?…
Abs
Fabiano, Concordo Plenamente, me desculpe, falta de atenção total na alteração!
Para tirar o Sorts só se não usar o OVER no CTE a query fica igual tá mesmo, com duas linhas.
Um abraço
Boas Férias e até breve
Boa tarde!
Pelo que vi, a solução do desafio é possível apenas se a edição do SQL Server for a Enterprise, pois o recurso de
compressão por pagina (DATA_COMPRESSION=PAGE) só rola na Enterprise Edition.
Msg 7738, Level 16, State 2, Line 2
Cannot enable compression for object ‘OrdersBig’. Only SQL Server Enterprise Edition supports compression.
Muito boa a solução, apesar de fica limitada à edição: Enterprise do SQL Server.
Valeu…
Abraço!
Oi Alex, realmente compressão de dados só funciona no SQL Server Enterprise… 😦
Obrigado pelo comentário.
Abs.
A versão developer também permite simular esse trem.