Início > Desafio, T-SQL, Tuning, Virtual PASS BR > Novo Desafio SQL Server – Performance 2

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 :-))

image

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]

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]

 

/*

  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.

About these ads
  1. 17 de outubro de 2012 às 16:19

    Vou tentar escrever algo aqui….Abs

  1. No trackbacks yet.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

Seguir

Obtenha todo post novo entregue na sua caixa de entrada.

Junte-se a 60 outros seguidores

%d blogueiros gostam disto: