Arquivos

Archive for the ‘T-SQL’ Category

Video com 6 horas de treinamento tuning gratuito…

27 de fevereiro de 2014 14 comentários

Fala galera, depois de um tempo sumido, cá estou…

No ano passado eu ministrei um treinamento in-company e o cliente fez a gentileza de gravar e disponibilizar os vídeos do treinamento, inclusive para o público J.

Acabei de subir pro youtube, são 16 vídeos com aproximadamente 6 horas de treinamento onde falei sobre os seguinte assuntos:

  • Porque pensar set-based é tão difícil?
  • – Query tuning
  • Overview plano de execução
  • Profiler, STATISTICS IO/CPU
  • Triggers
  • Dicas sobre scalar functions
  • Common table expressions
  • Cursores e windows functions
  • TOP e CROSS APPLY

Espero que vocês gostem e que seja útil. Seguem os links para acessar os vídeos:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Abs.

Fabiano Amorim

TSQL Expert– SP, RJ e BSB

2 de julho de 2013 4 comentários

Galera, acabei de colocar no site da Sr.Nimbus o calendário para nosso treinamento de T-SQL expert.

Seguem mais detalhes:

·         Rio de Janeiro

o   SQL11 T-SQL Expert

o   Carga horária: 24 horas

o   Treinamento no período integral aos sábados das 09:00 as 18:00 nos dias 12, 19 e 26 de Outubro de 2013.

o   Local: A definir

o   Valor: R$1.550,00 reais parcelado em até 12x via pagseguro, ou R$1.450,00 a vista via depósito bancário.

o   Link para inscrição: http://www.srnimbus.com.br/calendario/sql11-rj-outubro-2013/

·         São Paulo

o   SQL11 T-SQL Expert

o   Carga horária: 24 horas

o   Treinamento no período integral aos sábados das 09:00 as 18:00 nos dias 3, 10 e 17 de Agosto de 2013.

o   Local: Proximidades da avenida paulista

o   Valor: R$1.450,00 reais parcelado em até 12x via pagseguro, ou R$1.350,00 a vista via depósito bancário.

o   Link para inscrição: http://www.srnimbus.com.br/calendario/sql11-sp-agosto-2013/

·         Brasília

o   SQL11 T-SQL Expert

o   Carga horária: 24 horas

o   Treinamento no período integral aos sábados das 09:00 as 18:00 nos dias 7, 14 e 21 de Setembro de 2013.

o   Local: Proximidades da avenida paulista

o   Valor: R$1.650,00 reais parcelado em até 12x via pagseguro, ou R$1.550,00 a vista via depósito bancário.

o   Link para inscrição: http://www.srnimbus.com.br/calendario/sql11-bsb-setembro-2013/

T-SQL Expert em SP aos sábados(11,18 e 25 de Maio)

15 de abril de 2013 5 comentários

Fala meu povo.

Para nossa alegria, estou fechando um treinamento de T-SQL Expert aos sábados nos dias 11, 18, 25 de Maio (exato, são apenas 3 sábados). Você pode ler mais detalhes em relação a este treinamento aqui no meu blog, ou pela ementa no site da Sr.Nimbus.

Mais detalhes:

Resumo:

http://www.srnimbus.com.br/calendario/sql11-sp-maio-2013/

  • Ementa: T-SQL Expert, SQL11 (sem o módulo sobre unit tests)
  • Carga horária: 24 horas
  • Horário das aulas: 09:00 a 18:00
  • Datas: 11, 18, 25 de Maio
  • Valor: R$ 1.500,00 em até 12 vezes via PagSeguro e R$1.400,00 a vista
  • Aluno precisa levar notebook
  • Local: Região da Paulista, local definitivo a confirmar.

Caso tenha interesse, basta utilizar o botão de reserva de vaga no link acima.

Abs.

T-SQL Expert online em Março-2013

18 de fevereiro de 2013 1 comentário

T-SQL Expert online,

Pessoal acabei de abrir uma turma para nosso treinamento de T-SQL Expert. Você pode ler mais detalhes em relação a este treinamento aqui no meu blog, ou pela ementa no site da Sr.Nimbus.

Para se inscrever no treinamento utilize o seguinte link: http://www.srnimbus.com.br/calendario/online-sql11-t-sql-expert/

Resumo:

  • Ementa: T-SQL Expert, SQL11 (módulo sobre unit tests será removido do treinamento online)
  • Carga horária: 24 horas
  • Horário das aulas entre 18:30 e 21:30 (3 horas por dia) seguindo o seguinte cronograma
  • 18, 19, 20 e 21 de março (segunda a quinta-feira)
  • 25, 26, 27 e 28 de março (segunda a quinta-feira)
  • Valor: R$ 720,00 em até 12 vezes via PagSeguro e R$680,00 a vista

Abs.

CategoriasT-SQL, Treinamento

Video PASS24 hours – Escrevento códigos “like a boss”

11 de janeiro de 2013 3 comentários

Update: A animação do PPT não ficou legal no vídeo… baixe o PPT para ver melhor!

Galera, o vídeo da minha sessão sobre T-SQL no PASS24 horas está disponível.

http://www.sqlpass.org/LearningCenter/SessionRecordings/24HoursPortuguese2012.aspx

Você pode baixar o material aqui:

http://www.srnimbus.com.br/wp-content/uploads/2012/11/T-SQL-Expert-Escrevendo-c%C3%B3digos-like-a-boss.zip

Abaixo alguns prints da sessão:

imageimage

 

Espero que gostem!

Abs.

Novo Desafio SQL Server – Performance 2

17 de outubro de 2012 1 comentário

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.

Reposta–Desafio Performance

1 de outubro de 2012 11 comentários

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:

image

                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)

clip_image004

E depois a parte de CASE +SubQuery:

clip_image006

                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)

 

clip_image008

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

 

clip_image010

clip_image011

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

 

clip_image012

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

 

clip_image013

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

 

clip_image015

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:

clip_image017

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:

clip_image019

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:

clip_image021

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)

 

clip_image023

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:

clip_image025

A consulta roda em 4 segundos, e utiliza os seguintes recursos:

clip_image027

                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.

Seguir

Obtenha todo post novo entregue na sua caixa de entrada.

Junte-se a 58 outros seguidores