SQL Server数据透视与多重联接 [英] SQL Server pivot vs. multiple join
问题描述
在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...
假设
- 一百万种产品
-
product
在product_id
上具有聚集索引
- 大多数(如果不是全部)产品在
product_code
表中具有相应的信息 -
product_code
上存在两个查询的理想索引.
- 1 million products
product
has a clustered index onproduct_id
- Most (if not all) products have corresponding information in the
product_code
table - 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
版本更有效.
如果type 1
和type 2
是表中唯一的types
,则PIVOT
版本在读取次数方面略有优势,因为它不必查找
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.
这篇关于SQL Server数据透视与多重联接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!