Novo Desafio SQL Server – Performance 2
|
E ai meu povo, estão prontos para mais um desafio? Espero que sim! Aqui vai mais um desafio relacionado a performance. Conforme os outros desafios, quem quiser participar é só me enviar sua solução por e-mail no “fabiano underline amorim arroba bol ponto com ponto br”. Como de praxe o ganhador leva uma cópia do meu livro impressa. (Alberto e Evandro não me xinguem, essa semana eu mando o livro de vocês :-)) |
 |
O desafio de hoje consiste em melhorar a performance da seguinte consulta:
CHECKPOINT
DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON
SELECT Pedidos.NumeroPedido,
Pedidos.DT_Pedido,
Pedidos.Valor,
SUM(ItensPed.Qtde) AS TotalItens
FROM Pedidos
INNER JOIN ItensPed
ON Pedidos.NumeroPedido = ItensPed.NumeroPedido
INNER JOIN Produtos
ON ItensPed.ID_Produto = Produtos.ID_Produto
WHERE Pedidos.DT_Pedido BETWEEN ‘20121010’ AND ‘20121020’
AND Produtos.Descricao like ‘%cicle%’
GROUP BY Pedidos.NumeroPedido,
Pedidos.DT_Pedido,
Pedidos.Valor
OPTION (RECOMPILE, MAXDOP 1)
SET STATISTICS IO OFF
Originalmente a consulta faz as seguintes leituras.
STATISTICS IO:
Table ‘ItensPed’. Scan count 0, logical reads 19840, physical reads 1, read-ahead reads 11900
Table ‘Pedidos’. Scan count 1, logical reads 230, physical reads 1, read-ahead reads 221
Table ‘Worktable’. Scan count 1, logical reads 3818, physical reads 0, read-ahead reads 0
Table ‘Produtos’. Scan count 1, logical reads 2283, physical reads 3, read-ahead reads 2270
Profiler:
![clip_image002[4]](https://mcflyamorim.files.wordpress.com/2012/10/clip_image0024.jpg "clip_image002[4]")
Vamos focar em melhorar o tempo, mas principalmente o número de leituras de páginas que são executadas nas tabelas.
Eu consegui fechar com os seguintes números:
STATISTICS IO:
Table ‘…’. Scan count 3, logical reads 9, physical reads 4, read-ahead reads 0
Table ‘…’. Scan count 1, logical reads 9, physical reads 1, read-ahead reads 7
Table ‘…’. Scan count 1, logical reads 3, physical reads 3, read-ahead reads 0
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0
Table ‘ItensPed’. Scan count 1, logical reads 652, physical reads 2, read-ahead reads 654
Table ‘Produtos’. Scan count 0, logical reads 6, physical reads 3, read-ahead reads 0
Table ‘Pedidos’. Scan count 1, logical reads 8, physical reads 1, read-ahead reads 6
Profiler:
![clip_image004[4]](https://mcflyamorim.files.wordpress.com/2012/10/clip_image0044.jpg "clip_image004[4]")
/*
Regras
* Não pode mudar índice cluster
* Não pode mudar schema das tabelas existentes (fks, datatype, nullable…)
* Pode criar quantos índices forem necessários (exceto cluster)
* Vale usar view indexadas
* Vale criar novos objetos (procedures, views, triggers, functions…)
* Vale reescrever a consulta
* Pelo menos 5 caracteres obrigatoriamente são utilizados para fazer o filtro pela descrição do produto
* Valores utilizados como filtro não são fixos… ou seja, tem que funcionar para qualquer valor que for solicitado
*/
Segue script para criar e popular as tabelas:
USE tempdb
GO
IF OBJECT_ID(‘Produtos’) IS NOT NULL
DROP TABLE Produtos
GO
CREATE TABLE Produtos (ID_Produto Int IDENTITY(1,1) PRIMARY KEY,
Descricao VarChar(400),
Col1 VarChar(400) DEFAULT NEWID())
GO
INSERT INTO Produtos (Descricao)
VALUES (‘Bicicleta’), (‘Carro’), (‘Motocicleta’), (‘Trator’)
GO
;WITH CTE_1
AS
(
SELECT TOP 200000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
FROM sysobjects a, sysobjects b, sysobjects c, sysobjects d
)
INSERT INTO Produtos (Descricao)
SELECT REPLACE(NEWID(), ‘-‘, ‘ ‘)
FROM CTE_1
GO
–SELECT * FROM Produtos
–GO
IF OBJECT_ID(‘ItensPed’) IS NOT NULL
DROP TABLE ItensPed
GO
IF OBJECT_ID(‘Pedidos’) IS NOT NULL
DROP TABLE Pedidos
GO
CREATE TABLE Pedidos (NumeroPedido VarChar(80) PRIMARY KEY,
DT_Pedido Date,
ID_Cliente Int,
Valor Float)
GO
;WITH CTE_1
AS
(
SELECT TOP 50000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
FROM sysobjects a, sysobjects b, sysobjects c, sysobjects d
)
INSERT INTO Pedidos(NumeroPedido, DT_Pedido, ID_Cliente, Valor)
SELECT ‘Ped-‘ + CONVERT(VarChar, rn), — Composto por "Ped + NumeroSequencial"
GetDate() – ABS(CheckSum(NEWID()) / 10000000),
ABS(CHECKSUM(NEWID())) / 1000000 AS ID_Cliente,
ABS(CHECKSUM(NEWID())) / 100000. AS Valor
FROM CTE_1
GO
–SELECT * FROM Pedidos
–GO
IF OBJECT_ID(‘ItensPed’) IS NOT NULL
DROP TABLE ItensPed
GO
CREATE TABLE ItensPed (NumeroPedido VarChar(80) FOREIGN KEY REFERENCES Pedidos(NumeroPedido),
DT_Entrega Date,
ID_Produto Int,
Qtde SmallInt,
PRIMARY KEY(NumeroPedido, ID_Produto) WITH(IGNORE_DUP_KEY=ON))
GO
INSERT INTO ItensPed(NumeroPedido, DT_Entrega, ID_Produto, Qtde)
SELECT NumeroPedido,
DATEADD(d, ABS(CheckSum(NEWID()) / 100000000), DT_Pedido),
ABS(CHECKSUM(NEWID())) / 1000000 + 1 AS ID_Produto,
ABS(CHECKSUM(NEWID())) / 1000000. AS Qtde
FROM Pedidos
GO 100
–SELECT * FROM ItensPed
–GO
Olhos atentos já viram que dei uma dica… Divirta-se e boa sorte!
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 
Vou tentar escrever algo aqui….Abs