SQL Server 数据透视与多重连接

2022-01-22 00:00:00 pivot join performance sql-server-2005 sql-server

在 SQL Server 2005 中使用什么更有效:PIVOT 或 MULTIPLE JOIN?

What is more efficient to use in SQL Server 2005: PIVOT or MULTIPLE JOIN?

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

For example, I got this query using two joins:

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 做同样的事情:

I can do the same using 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

哪个效率更高?

推荐答案

答案当然是视情况而定"，但基于对此目的的测试......

The answer will of course be "it depends" but based on testing this end...

假设

  1. 100 万种产品
  2. product 在 product_id
    3. 上有一个聚集索引
  3. 大多数(如果不是全部)产品在 product_code 表中都有相应的信息
  4. product_code 上存在两个查询的理想索引.
  1. 1 million products
  2. product has a clustered index on product_id
  3. Most (if not all) products have corresponding information in the product_code table
  4. Ideal indexes present on product_code for both queries.

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

The PIVOT version ideally needs an index product_code(product_id, type) INCLUDE (code) whereas the JOIN version ideally needs an index product_code(type,product_id) INCLUDE (code)

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

If these are in place giving the plans below

那么 JOIN 版本效率更高.

then the JOIN version is more efficient.

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

In the case that type 1 and type 2 are the only types in the table then the PIVOT version slightly has the edge in terms of number of reads as it doesn't have to seek into product_code twice but that is more than outweighed by the additional overhead of the stream aggregate operator

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 行上.

If there are additional type records other than 1 and 2 the JOIN version will increase its advantage as it just does merge joins on the relevant sections of the type,product_id index whereas the PIVOT plan uses product_id, type and so would have to scan over the additional type rows that are intermingled with the 1 and 2 rows.

相关文章