sql-server - SQL Server 数据透视与多重联接

Question

在 SQL Server 2005 中使用哪个更有效:PIVOT 或 MULTIPLE JOIN？

例如，我使用两个连接得到了这个查询:

SELECT p.name, pc1.code as code1, pc2.code as code2
FROM product p
    INNER JOIN product_code pc1
    ON p.product_id=pc1.product_id AND pc1.type=1
    INNER JOIN product_code pc2
    ON p.product_id=pc2.product_id AND pc2.type=2

我可以使用 PIVOT 执行相同的操作:

SELECT name, [1] as code1, [2] as code2
FROM (
    SELECT p.name, pc.type, pc.code
    FROM product p
        INNER JOIN product_code pc
        ON p.product_id=pc.product_id
    WHERE pc.type IN (1,2)) prods1
PIVOT(
    MAX(code) FOR type IN ([1], [2])) prods2

哪一个效率更高？

Answer 1

答案当然是“这取决于”，但基于此测试......

假设

1. 100 万种产品
2. product 在 product_id 上有聚集索引
3. 大多数(如果不是全部)产品在 product_code 表中都有相应的信息
4. 两个查询的 product_code 上都存在理想的索引。

PIVOT 版本理想情况下需要一个索引 product_code(product_id, type) INCLUDE (code) 而 JOIN 版本理想情况下需要一个索引 product_code(type,product_id) INCLUDE(代码)

如果这些都到位，请给出以下计划

那么JOIN版本效率更高。

如果类型 1 和 类型 2 是表中唯一的类型，则 PIVOT版本在读取次数方面略有优势，因为它不必两次查找 product_code ，但这远远超过了流聚合运算符的额外开销

数据透视

Table 'product_code'. Scan count 1, logical reads 10467
Table 'product'. Scan count 1, logical reads 4750
   CPU time = 3297 ms,  elapsed time = 3260 ms.

加入

Table 'product_code'. Scan count 2, logical reads 10471
Table 'product'. Scan count 1, logical reads 4750
   CPU time = 1906 ms,  elapsed time = 1866 ms.

如果除了 1 和 2 之外还有其他 type 记录，JOIN 版本将增加其优势:它只是对 type,product_id 索引的相关部分进行合并联接，而 PIVOT 计划使用 product_id, type，因此必须扫描覆盖与 1 和 2 行混合的其他 type 行。