Blog – Fabiano Neves Amorim

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

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

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

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)

       …

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

  …

       …

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

  …

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)

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

SELECT *,

       ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col2) AS rn

  FROM Tab1

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

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

CREATE INDEX ix1 ON Tab1 (Col1, Col2)

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

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

CREATE TABLE dbo.Tmp_OrdersBig

       (

       [OrderID] [int] IDENTITY(1,1) PRIMARY KEY NONCLUSTERED NOT NULL,

       [CustomerID] [int] NULL,

       [OrderDate] [date] NULL,