SQL Server Pivot和老式方法性能对此

set statistics time on

select StudentId,
(select top 1 Answer from AnswerSheet where StudentId=a.StudentId and QuestionId=1) as [What's your age],
(select top 1 Answer from AnswerSheet where StudentId=a.StudentId and QuestionId=2) as [What's your gender],
(select top 1 Answer from AnswerSheet where StudentId=a.StudentId and QuestionId=3) as [When do you go home],
(select top 1 Answer from AnswerSheet where StudentId=a.StudentId and QuestionId=4) as [How often do you go to our store]
from AnswerSheet a
group by StudentId

set statistics time off

SQL Server Execution Times:
CPU time = 469 ms, elapsed time = 229 ms.

SQL Server Execution Times:
CPU time = 717 ms, elapsed time = 168 ms.

SQL Server Execution Times:
CPU time = 625 ms, elapsed time = 226 ms.

CPU time大于elapsed time,因为CPU是多核的。

SET statistics TIME ON

SELECT *
FROM (SELECT StudentID
,Col = B.Text
,VALUE = A.Answer
FROM AnswerSheet A
JOIN Question B ON A.QuestionID=B.ID) AS t
pivot (
MAX(t.VALUE) FOR t.Col IN ([What's your age],[What's your gender],[WHEN do you GO home],[How often do you GO TO our store])
) AS pt

SET statistics TIME off

SQL Server Execution Times:
CPU TIME = 94 ms, elapsed TIME = 146 ms.

CPU TIME = 62 ms, elapsed TIME = 180 ms.

CPU TIME = 62 ms, elapsed TIME = 122 ms.

说明pivot方法并行度低,但是总花费时间较少。

发表评论

电子邮件地址不会被公开。

ea dz dy dx dw dv du dt ds dr dq dp dn dm dl dk dj di dh dg df de dd dc da cz cy cx cw cv cu ct cs cr cq cp co cn cm cl ck cj ci ch cg cf ce cd cc cb ca bz bx bw bv bu bt bs br bq bp bo bn bm bl bk bj bi bh bg bf bd bc bb ba az ay ax aw av au ar aq ap ao am al ak aj ai ah ag af ae ad ac ab /跳跳 /抓狂 /快哭了/ /微笑/ /奋斗 /呲牙/ /吐