SQL Server优化技巧之SQL Server中的"MapReduce"

日常的OLTP环境中,有时会涉及到一些统计方面的SQL语句,这些语句可能消耗巨大,进而影响整体运行环境,这里我为大家介绍如何利用SQL Server中的”类MapReduce”方式,在特定的统计情形中不牺牲响应速度的情形下减少资源消耗.

我们可能经常会利用开窗函数对巨大的数据集进行分组统计排序.比如下面的例子:

脚本环境

/*This script creates two new tables in AdventureWorks:dbo.bigProductdbo.bigTransactionHistory*/USE AdventureWorksGOSELECT    p.ProductID + (a.number * 1000) AS ProductID,    p.Name + CONVERT(VARCHAR, (a.number * 1000)) AS Name,    p.ProductNumber + '-' + CONVERT(VARCHAR, (a.number * 1000)) AS ProductNumber,    p.MakeFlag,    p.FinishedGoodsFlag,    p.Color,    p.SafetyStockLevel,    p.ReorderPoint,    p.StandardCost,    p.ListPrice,    p.Size,    p.SizeUnitMeasureCode,    p.WeightUnitMeasureCode,    p.Weight,    p.DaysToManufacture,    p.ProductLine,    p.Class,    p.Style,    p.ProductSubcategoryID,    p.ProductModelID,    p.SellStartDate,    p.SellEndDate,    p.DiscontinuedDateINTO bigProductFROM Production.Product AS pCROSS JOIN master..spt_values AS aWHERE    a.type = 'p'    AND a.number BETWEEN 1 AND 50GOALTER TABLE bigProductALTER COLUMN ProductId INT NOT NULL    GOALTER TABLE bigProductADD CONSTRAINT pk_bigProduct PRIMARY KEY (ProductId)GOSELECT     ROW_NUMBER() OVER     (        ORDER BY             x.TransactionDate,            (SELECT NEWID())    ) AS TransactionID,    p1.ProductID,    x.TransactionDate,    x.Quantity,    CONVERT(MONEY, p1.ListPrice * x.Quantity * RAND(CHECKSUM(NEWID())) * 2) AS ActualCostINTO bigTransactionHistoryFROM(    SELECT        p.ProductID,         p.ListPrice,        CASE            WHEN p.productid % 26 = 0 THEN 26            WHEN p.productid % 25 = 0 THEN 25            WHEN p.productid % 24 = 0 THEN 24            WHEN p.productid % 23 = 0 THEN 23            WHEN p.productid % 22 = 0 THEN 22            WHEN p.productid % 21 = 0 THEN 21            WHEN p.productid % 20 = 0 THEN 20            WHEN p.productid % 19 = 0 THEN 19            WHEN p.productid % 18 = 0 THEN 18            WHEN p.productid % 17 = 0 THEN 17            WHEN p.productid % 16 = 0 THEN 16            WHEN p.productid % 15 = 0 THEN 15            WHEN p.productid % 14 = 0 THEN 14            WHEN p.productid % 13 = 0 THEN 13            WHEN p.productid % 12 = 0 THEN 12            WHEN p.productid % 11 = 0 THEN 11            WHEN p.productid % 10 = 0 THEN 10            WHEN p.productid % 9 = 0 THEN 9            WHEN p.productid % 8 = 0 THEN 8            WHEN p.productid % 7 = 0 THEN 7            WHEN p.productid % 6 = 0 THEN 6            WHEN p.productid % 5 = 0 THEN 5            WHEN p.productid % 4 = 0 THEN 4            WHEN p.productid % 3 = 0 THEN 3            WHEN p.productid % 2 = 0 THEN 2            ELSE 1         END AS ProductGroup    FROM bigproduct p) AS p1CROSS APPLY(    SELECT        transactionDate,        CONVERT(INT, (RAND(CHECKSUM(NEWID())) * 100) + 1) AS Quantity    FROM    (        SELECT             DATEADD(dd, number, '20050101') AS transactionDate,            NTILE(p1.ProductGroup) OVER             (                ORDER BY number            ) AS groupRange        FROM master..spt_values        WHERE             type = 'p'    ) AS z    WHERE        z.groupRange % 2 = 1) AS xALTER TABLE bigTransactionHistoryALTER COLUMN TransactionID INT NOT NULLGOALTER TABLE bigTransactionHistoryADD CONSTRAINT pk_bigTransactionHistory PRIMARY KEY (TransactionID)GOCREATE NONCLUSTERED INDEX IX_ProductId_TransactionDateON bigTransactionHistory(    ProductId,    TransactionDate)INCLUDE (    Quantity,    ActualCost)GO

当我们针对bigProduct表的productid分组,并按照bigTransactionHistory的actualcost

及quantity分别排序取结果集语句如下:

code

Declare@p1 int,@p2 nvarchar(56),@p3 smallint,@p4 int,@p5 bigint,@p6 bigintselect @p1=p.productid,@p2=p.productnumber,@p3=p.reorderpoint,@p4=th.transactionid,@p5=rank()over (partition by p.productid                order by th.actualcost desc),@p6=rank()over (partition by p.productid                order by th.quantity desc)from bigproduct as pjoin bigtransactionhistory as th on th.productid=p.productidwhere p.productid between 1001 and 3001

执行此语句并输出实际执行计划如图1-1