sql-server - Pivoting 脚本的性能改进

Question

在我的数据生成过程中，我有一个包含大约 1 亿行的临时表。表的结构如下:

CREATE TABLE #table ( ProductId INT, CountryCodeID INT ,DataTypeID INT,
     Formula VARCHAR(1000) ,Yr INT, Letter VARCHAR(100) , Data FLOAT(53))

我们可以用一些虚拟数据(~10M)填充它:

INSERT INTO #table ( ProductId, CountryCodeID, DataTypeID, Formula, Yr, Letter, Data )
SELECT
     P.ProductID, C.CountryCodeID, D.DataTypeID, F.Formula, Y.Yr,
     L.Letter, RAND() AS Data
FROM (VALUES (
    1856),(1459),(1816),(238),(328),(444),(921),(1724),(155),(420),(795),
    (620),(1007),(153),(1659),(95),(952),(1476),(759),(1461),(1958),(1341),
    (116)) P(productID)
CROSS JOIN (VALUES (16),(302),(422),(36),(95),(744),(4),(285),(1849),(1402),
    (430),(835),(214),(1476),(711),(36),(142),(428),(768),(78),(510),(945),
    (1125)) C(CountryCodeID)
CROSS JOIN (VALUES (1120),(1121),(1122),(1123),(1124),(1125),(1126),(1127),
    (1128)) D(DataTypeID)
CROSS JOIN (VALUES ('A+B'),('A/B/(A+B+C+D+E+G)'),('A/B/(A+B+C+D+E)'),
    ('A/B/(A+B+C+D)'),('A/B/(A+B+C)'),('A/B/(A+B)'),
    ('A/B/(A+B+C+D+E+G+Z)')) F(Formula)
CROSS JOIN (VALUES (1977),(1978),(1979),(1980),(1981),(1982),(1983),(1984),
    (1985),(1986),(1987),(1988), (2000),(2001),(2002),(2003),        (2004),
    (2005),(2006),(2007),(2008),(2009),(2010),(2011),(2012),(2013)) Y(Yr)
CROSS JOIN (VALUES ('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('J '),
    ('K'),('L'),('M')) L(Letter) 
--10 398 024

在我的过程结束时，我需要将我的表转为另一个临时表:

SELECT
     ProductID,CountryCodeId,DataTypeID,Formula,
    Yr,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,X,Z,W
INTO #Final3
FROM #table
PIVOT ( MAX(Data) FOR Letter IN (
     A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,X,Z,W
    ) ) AS pvt;

而且最后一个查询非常慢。这实际上需要很长时间。

执行计划:

一开始有表扫描但只有2%表格插入到#Final 占用 83%

有趣的是，估计的行数比实际行数大得多。

所以我的问题是如何提高性能。我的一些想法:

• 索引？;
• 也许可以使用 NTILE 将 #table 分成 10 个较小的表，然后创建一个将执行 10 个数据透视表的循环？

老实说，我没有任何其他想法......如果您有相关经验 - 请提出建议。谢谢。

Answer 1

我已经尝试使用老式的 PIVOTing(使用 CASE)并在我的系统上获得了更好的结果(快 2~3 倍)。还创建了一个 CLUSTERED INDEX，因此插入数据会慢很多(对于此测试，SQL Server 提示键长度)。尝试一下，看看它是否能改善您的业务案例。

希望这对您有所帮助。如果没有，请使用我关于使用 Reporting Matrices 的建议或 OLAP Cubes .另请检查此链接:Pivot Transformation .如果您不能使用这些，甚至可能用您的编程语言自己编写 PIVOT 可能会表现更好。

测试数据创建(使用全局温度，因此我可以在 SSMS 的第二个选项卡中测试选择):

CREATE TABLE ##table ( ProductId INT, CountryCodeID INT ,DataTypeID INT, Formula VARCHAR(1000) ,Yr INT, Letter VARCHAR(100) , Data FLOAT(53));
CREATE CLUSTERED INDEX tt ON ##table(ProductId, CountryCodeID, DataTypeID,Formula,Yr);

INSERT INTO ##table ( ProductId, CountryCodeID, DataTypeID, Formula, Yr, Letter, Data )
SELECT P.ProductID, C.CountryCodeID, D.DataTypeID, F.Formula, Y.Yr, L.Letter, RAND() AS Data
FROM (VALUES (1856),(1459),(1816),(238),(328),(444),(921),(1724),(155),(420),(795),(620),(1007),(153),(1659),(95),(952),(1476),(759),(1461),(1958),(1341),(116)) P(productID)
CROSS JOIN (VALUES (16),(302),(422),(36),(95),(744),(4),(285),(1849),(1402),(430),(835),(214),(1476),(711),(36),(142),(428),(768),(78),(510),(945),(1125)) C(CountryCodeID)
CROSS JOIN (VALUES (1120),(1121),(1122),(1123),(1124),(1125),(1126),(1127),(1128)) D(DataTypeID)
CROSS JOIN (VALUES ('A+B'),('A/B/(A+B+C+D+E+G)'),('A/B/(A+B+C+D+E)'),('A/B/(A+B+C+D)'),('A/B/(A+B+C)'),('A/B/(A+B)'),('A/B/(A+B+C+D+E+G+Z)')) F(Formula)
CROSS JOIN (VALUES (1977),(1978),(1979),(1980),(1981),(1982),(1983),(1984),(1985),(1986),(1987),(1988), (2000),(2001),(2002),(2003),(2004),(2005),(2006),(2007),(2008),(2009),(2010),(2011),(2012),(2013)) Y(Yr)
CROSS JOIN (VALUES ('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('J '),('K'),('L'),('M')) L(Letter) ;

生成#final3中的数据:

SELECT 
    ProductID,
    CountryCodeId,
    DataTypeID,
    Formula,
    Yr,
    MAX(CASE WHEN Letter='A' THEN Data END) AS A,
    MAX(CASE WHEN Letter='B' THEN Data END) AS B,
    MAX(CASE WHEN Letter='C' THEN Data END) AS C,
    MAX(CASE WHEN Letter='D' THEN Data END) AS D,
    MAX(CASE WHEN Letter='E' THEN Data END) AS E,
    MAX(CASE WHEN Letter='F' THEN Data END) AS F,
    MAX(CASE WHEN Letter='G' THEN Data END) AS G,
    MAX(CASE WHEN Letter='H' THEN Data END) AS H,
    MAX(CASE WHEN Letter='J' THEN Data END) AS J,
    MAX(CASE WHEN Letter='K' THEN Data END) AS K,
    MAX(CASE WHEN Letter='L' THEN Data END) AS L,
    MAX(CASE WHEN Letter='M' THEN Data END) AS M
INTO 
    #Final3
FROM 
    ##table
GROUP BY
    ProductID,
    CountryCodeId,
    DataTypeID,
    Formula,
    Yr
ORDER BY 
    ProductID,
    CountryCodeID,
    Yr;