日常的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