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        ) AGROUP 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 DPIVOT  (  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