SQL语句练习实例之十——SQL SERVER 行转列的性能测试

在数据库设计时，有时候为了实现数据规范化的目的，会将属于同一个人的属性记录值改用多条记录的方式来存储，显示时又希望将多个属性数据合并成一行来显示，这就是行转列。

例如：下图的成绩记录表。

 

那么行转列的效率又将如何呢？我试了以下三种方式进行行转列的测试。测试数据表中的记录数量为120万条，字段为10个，测试下来感觉性能还不错。

一、第一种方式(SQL 2000以后的版本)

 

--总计120万记录

SELECT wbook_no     

       , MAX(CASE WHEN [COP_G_NO] ='60174257' THEN AR END) "60174257"

       , MAX(CASE WHEN [COP_G_NO] ='50165814' THEN AR END) "50165814"

       , MAX(CASE WHEN [COP_G_NO] ='10221553' THEN AR END) "10221553"

FROM

    (

     SELECT [COP_G_NO]

           , wbook_no

           , SUM(G_QTY * decl_Price) AR

     FROM WBK_PDE_LIST

     WHERE [COP_G_NO] in('60174257','50165814','10221553')

     GROUP BY [COP_G_NO]

               , wbook_no  

     ) A

GROUP BY wbook_no

 

二、第二种方式(SQL 2000以后的版本)

SELECT wbook_no

, SUM(

     CASE WHEN [COP_G_NO] ='60174257' THEN G_QTY * decl_Price END) "60174257"

, SUM(

     CASE WHEN [COP_G_NO] ='50165814' THEN  G_QTY * decl_Price  END) "50165814"

, SUM(

      CASE WHEN [COP_G_NO] ='10221553' THEN  G_QTY * decl_Price  END) "10221553"

FROM WBK_PDE_LIST

WHERE 1=1

      and [COP_G_NO] in('60174257','50165814','10221553')

GROUP BY wbook_no

 

三、第三种方式：使用PIVOT命令来实现（SQL 2005以后的版本才提供以命令）

SELECT WBOOK_NO

       , "60174257"

       , "50165814"

       , "10221553"

FROM

  (

  SELECT [COP_G_NO]

        , WBOOK_NO

        , G_QTY * decl_Price AR

  FROM WBK_PDE_LIST

  WHERE 1=1

        AND [COP_G_NO] in('60174257','50165814','10221553')

  )AS D

PIVOT

  (

  SUM(AR)

  FOR [COP_G_NO] in([60174257],[50165814],"10221553")

  ) AS P

 

 

四、以上三种方式的查询结果都如下：

 

五、最后我们来比对一下各自性能损耗。从比对结果表来看三者的差别不大，总的来说都在2-3秒之间。

	IO			CPU			逻辑读取	物理读取	预读	CPU 时间	占用时间
	表扫描	计算标题	排序	表扫描	计算标题	排序	次	次	次	ms	ms
第一种方式	17.652	0	0.0112513	1.33851	0.121668	0.0131525	23827	370	23827	635	2216
第二种方式	17.652	0	0.0112513	1.33851	0.121668	0.0131525	23827	374	23827	618	2171
第三种方式	17.652	0	0.0112513	1.33851	0.121668	0.0131525	23827	370	23827	563	1960