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

9/1/2015来源:SQL技巧人气:4125

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
View Code

当我们针对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

QQCjG5RdZWcTsiJiYYtgmAlhN4DgBwBvVdHln4YAAAAbBZUeg8EwToQeg8AcgT0np7mi8RkKQK9BwAAUBqg90CwzoTeA4AcYfr9Pa1EqXbpTt6Y+Ff78hJ73GMw8Rb5xCySr/TDAAAAYLPA6b3hqQWCYLWE3gOAgkDpvah4o4Wcec4yJV8Rek/RliVmgN4DAACoLTC/B4J1JvQeAOQIpd4LZVs4a0cIubbqPU6zSfWeSgRmkXylHwYAAACbBeg9EKwzofcAIEdo9B73l6NW+4kaLJBh0ectg+1ouvQnooZozmhZMVuiOqV6T3xuM9gWn/CE3gMAAKgtoPdAsM6E3gOAHJFM7011U3nayT1Rkk3jc3F0okrCSYsbNiStU6X3xO2o0uMkH/QeAABAPUHovRI+SEB/EYEZfztBW38JF+XStlJbDlZL1X6ks3EphqXoAwN6DwByRJ56z2hZlyTSLlRiRAZpSvY6tYJNqvGg9wAAABqBXPReRjFDFw9/TddKaaq1IPvB8sntKWJbKgsTldIeFdB7AJAj9HqPk3kqvWf42h4xwyYVZlrxllTvGdYpFWxSaQe9BwAA0Dho9Z6JRClB76WeIoPeA1NQq/fCfcr9ROs9sZT4r8wSAADygen6nOG/Uo1nvkaLud4z2U4q58zrlGq2RHpPFHv9476hAiz9MAAAANgsqPSeSmKJ6XSKdDZDWidxvctVJa1f26KqI1qbiVJEu3kZYGIVmDtVu5LbJlLEn6SlVG3FfwUAIB+Yfn+PW66TW6/FZB2XqWI5FlrIWfGFWCxhXRaiZmlD5nVKZVsYe7SJ0HsAAAC1RSK9F/6rSqdzmhdXWUJUReslE0u0miq7/URVWvvBMimOObf7tBtM2PXSPNK2hF8BAMgHGr0n0Wz1+NxCmUzxWKaqCPQeAABAHWCi97TaQ0ynpVFqvSRaYq6XxMSkyoruVKJSWqukI5BavYBJKe4CMYP0+JHuKTqP9qir2kkAQHuQTO9pZ/BAQgGa68bSDwMAAIDNAq33mEKlqDIbFi9CL1kCknZEq6kKtZ8WDCbmgTnSMtNj0qNI3BbPDroS4VcAAPJB4vk9sASWfhgAAABsFqR6L6o6pPJJ1B5iIqG+CKgs0davyqk12BLkIn3xbdJN2kiTqgzrB4sgcQhJjyJVCn1U0Hmimat2EgDQHkDv1ZGlHwYAAACbBXxvHQTrTOg9AMgR0HtZ6RaA0g8DAACAzQL0HgjWmdB7AJAjoPeysvRdBgAAAGQF9B4I1pnQewCQI6D3oPcAAAA2DpzeG55aIAhWS+g9ACgIGr1nviBnOUt3WgYf3+N+gt4DAAAAOGB+DwTrTOg9AMgRlN7jvqhOiz3DnHmpvnylXZaype8yAAAAICug90CwzoTeA4AcodR7oWwLZ+0MhRz0HgAAAFBzQO+BYJ0JvQcAOUKj97i/HEWlZ/Tk5+rxy0BlRf81fFDTRO+JZbkWRwpsqb7SdxkAAACQFYTeK+GDb+m+XJeo5jKNL+5befj+XluJ7+8BQJlIpvemBo9uap78jAuw6Eb0ryqnud4jRKC2fug9AACAdiMXvZdRiqiKh+np6i9UIOVicyILoffax+gOVe1c6D0AyBFl6z1OdBF6j5t/y0vvabeh9wAAAFoPrd4z0RiF6r3UU1vQe2DNCb0HACVDr/c4mZdd76nklqj3pHNx0HsAAABARqj0nkpoiel0ClcDUSdtQ1RE0YnmXdBaq7KZyG/YKJFT9VMiC2kjwZqQ27/qPAAA5APT9TnDf6V6L8FKnlU/z2mo9/rH/b6ZcC19lwEAAABZkUjvhf+q0umc5sVFG4hKLAFJbSBEUTqbs3RcmzlpOlhbcvtLnQcAgHxg+v09brlOcWkWw+/vWcLiKFZkGZXov1zm6K/SGrSJqnal/0LvAQAAtBgmek/ULdLM0hSpCEmknVQ2cBsmOZP2S9uRpPlNKslSsyXbC6qugZWT24/qPAAA5AON3pMItuI/t1ATQu8BAAC0FbTeYzKxRGQ2LJ5FO4mJloCkXSBEUUE2pzAmqSXmGcCqyO1HdR4AAPJBMr1nOInXdJpP7kHvAQAANBFSvRcVTlIRJUoIMZHQYARUxemftG1JTbUEuai64DbpGpFuUoSwUNVlIj9tDFgfaneQBb0HAPkh8fweCL0HAADQdOB76yBYZ0LvAUCOgN6D3gMAANg4QO+BYJ0JvQcAOaK1es8tC6XvMgAAACAroPdAsM6E3gOAHNFavVf6SAIAAACNAaf3hqcWCILVEnoPAAoC9B4AAACwccD8HgjWmdB7AJAj