SQL Server SQL效能最佳化之--pivot行列轉換減少掃描計數最佳化查詢語句

標籤：

　　

 

先看常用的一種表結構設計方式：

 

那麼可能會遇到一種典型的查詢方式，主子表關聯，查詢子表中的某些（或者全部）Key點對應的Value，橫向顯示（也即以行的方式顯示）

 

這種查詢方式很明顯的一個卻顯示多次對字表查詢（暫時拋開索引）

 

相比這種查詢方式很多人都遇到過，如果子表是配置資訊之類的小表的話，問題不大，如果字表資料量較大，可能就會有影響了。

這個查詢目的是將”縱表”儲存的結果“橫向”顯示，相當於橫列轉換的感覺了。
可以將子表的結果一次性將縱表的結果轉換成橫標，再跟主表串連，
然後得到一個最終一樣的查詢結果（格式），就能夠減少子表的查詢次數
這裡將子表的結果“一次性將縱表的結果轉換成橫標”，是典型的行列轉換操作

 

首先先看一下這裡所說的一次轉換成橫標的這一步驟，需要藉助pivot，一步一步來

 

然後看跟主表join之後，兩種查詢方式的整體查詢結果

 

那麼看一下後一種查詢方式也即通過行業轉換之後做join的執行計畫，可以看到只對字表進行了一次尋找（這裡是index seek，但是暫拋開索引）

 

觀察一下兩條SQL的IO資訊，可以發現，前者的Scan count是5，邏輯讀是65，後者的Scan count是1，邏輯讀是13，65=13*5。可見後者是一次性將表中的幾個Key值讀取出來的，而前者每個Key值讀取一次表。

 

總結：

　　改寫SQL是實現最佳化的思路之一，當然改寫SQL技巧有很多種，本文僅對某一類典型查詢提供一個改寫思路，避免對一個表進行多次讀取的方式來實現的查詢。

　　通過改寫一個常用的查詢寫法，從而實現一個等價的邏輯來減少對基表的讀取次數來達到SQL最佳化的目的。

　　當然實際情況可能更加複雜，採用該思路改寫的時候要注意針對SQL語句測實驗證。

 

附上本文的測試指令碼

create table HeaderTable(    HeaderId int ,    OtherColumn varchar(50))create table DetailTable(    HeaderId int,    DetailId int identity(1,1),    DetailKey varchar(50),    DetailValues int)declare @i int = 0while @i<1000000begin    insert into HeaderTable values (@i,NEWID())    insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0001‘,RAND()*10000)    insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0002‘,RAND()*10000)    insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0003‘,RAND()*10000)    insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0004‘,RAND()*10000)    insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0005‘,RAND()*10000)    insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0006‘,RAND()*10000)    insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0007‘,RAND()*10000)    insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0008‘,RAND()*10000)    insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0009‘,RAND()*10000)    insert into DetailTable (HeaderId,DetailKey,DetailValues)values(@i,‘A0010‘,RAND()*10000)    set @i=@i+1endcreate index idx_HeaderId on HeaderTable(HeaderId)create index idx_HeaderId on DetailTable(HeaderId)create index idx_DetailKey on DetailTable(DetailKey)select *,(select DetailValues from DetailTable t where t.HeaderId = a.HeaderId and  t.DetailKey = ‘A0001‘) as ‘Key1的值‘,(select DetailValues from DetailTable t where t.HeaderId = a.HeaderId and  t.DetailKey = ‘A0002‘) as ‘Key2的值‘,(select DetailValues from DetailTable t where t.HeaderId = a.HeaderId and  t.DetailKey = ‘A0003‘) as ‘Key3的值‘,(select DetailValues from DetailTable t where t.HeaderId = a.HeaderId and  t.DetailKey = ‘A0004‘) as ‘Key4的值‘,(select DetailValues from DetailTable t where t.HeaderId = a.HeaderId and  t.DetailKey = ‘A0005‘) as ‘Key5的值‘from HeaderTable a where a.HeaderId = 10000SELECT a.*,t.A0001 as  ‘Key1的值‘,t.A0002 as  ‘Key2的值‘,t.A0003 as  ‘Key3的值‘,t.A0004 as  ‘Key4的值‘,t.A0005 as  ‘Key5的值‘from HeaderTable a inner join(select HeaderId ,DetailKey ,DetailValues from DetailTable)t pivot( MAX(DetailValues) FOR DetailKey IN (A0001,A0002,A0003,A0004,A0005))t  on t.HeaderId = a.HeaderIdwhere a.HeaderId = 10000

 

　　

SQL Server SQL效能最佳化之--pivot行列轉換減少掃描計數最佳化查詢語句