[SQL][Performance]山不轉路轉 - 處理字串欄位相加的比對問題

[SQL][Performance]山不轉路轉 - 處理字串欄位相加的比對問題

以往架設的一個小知識庫這幾天要被清掉了,因此這幾天在整理幾個之前處理的案例,把這些移到點部落上面,雖然之前大鈞開玩笑說要關站,但我想短時間內他應該不會那麼殘忍吧 :)

 

言歸正傳,回到我們的主題。一般來說如果我們需要針對資料做起訖的查詢時,如果只有一個欄位,那麼直接使用 between 的指令就可以了;但有些時候我們需要兩個欄位( EX : 單別+單號, 部門代號+員工代號等 ) 的時候,那麼就很容易會有以下這樣的 SQL 語法出現 ( PS. 該範例因為 TC001 欄位是 char(4),所以資料之間會有放入一個空白 )

SELECT * FROM PURTC
  WHERE ( TC001+TC002 >= '330 1007010' ) AND ( TC001+TC002 <='330 1009055' )

 

這樣只雖然很容易看得懂,但是因為無法配合到資料庫的 Index,因此會造成 Table Scan or Index Scan,使得整體的效能不好,因此當初在協助查看問題的時候,就建議開發人員做一些調整,可以將 SQL 改成以下的方式:

SELECT * FROM PURTC
  WHERE (( TC001='330 ' AND TC002 >= '1007010' ) )
   AND  (( TC001='330 ' AND TC002 <= '1009055' ) )

或者是

SELECT * FROM PURTC
  WHERE TC001='330 ' AND TC002 BETWEEN  '1007010' AND '1009055' 

 

此時如果該 TABLE 有配合 TC001 和 TC002 去建立 Index 的環境下,則 SQL Server 就會改成利用 Index Seek 的方式去搜尋資料。這樣調整之後,比對一下兩者的效果,所查詢出來的資料數量和修改前是一樣的,但總執行時間上有顯著的差異。

效能比較

 

但開發人員看了之後,卻表示沒有辦法這樣的修改,因為雖然部分查詢的時候,起訖資料的第一個欄位多半都會相同,但如果第一個欄位不同的時候,則上述的修改方式可能會造成有部分資料會取不到,舉個例子來說,原本的 SQL 可能會類似以下的寫法:

SELECT * FROM PURTC
  WHERE ( TC001+TC002 >= '330 20081101001' ) AND ( TC001+TC002 <='339 20081205001' )

 

那如果改成以下的寫法 :

SELECT * FROM PURTC
  WHERE ( TC001 = '330 ' AND TC002 >= '20081101001' ) 
    AND ( TC001 = '339 ' AND TC002 <= '20081205001' )

 

就有可能會發生 TC001 落在 '330 ' & '339 ' 之間的資料取不到,因此這個時候就要再調整一下,變成如下的語法:

SELECT * FROM PURTC
  WHERE (( TC001='330 ' AND TC002 >= '20081101001' ) OR ( TC001>'330 ' ))
   AND  (( TC001='339 ' AND TC002 <= '20081205001' ) OR ( TC001<'339 ' ))

 

原本以為這樣就可以解決了,但程式開發人員因為要改的地方實在太多了,短時間內開發人員並沒有辦法一次修改完畢。因此開發單位主管希望找到一個暫時解法,能夠先做局部改善,再來慢慢修改程式,再跟相關人員討論完畢之後,於是建議他們可以先暫時用計算欄位 ( computed column ) 來作解決,因此會先針對原始 Table 做一些調整:

ALTER TABLE dbo.PURTC ADD
    X  AS TC001+TC002 PERSISTED 

 

透過上述的指令,我們產生一個計算欄位 X,並且指定是 PERSISTED 的,因此我們就可以針對這個欄位來建立 INDEX

CREATE NONCLUSTERED INDEX IX_PURTC 
  ON dbo.PURTC(X)  ON [PRIMARY]

 

這要即便我們沒有改 SQL 指令,仍然會透過 INDEX 去搜尋,只是相對地來說,原本的 TABLE 會占用比較多的空間去儲存資料,而也要另外多建立一組 INDEX 來儲存,這些都是我們在使用時需要考量的因素。除此之外,也不是所有狀況下都可以這樣使用的,必須要注意原本系統是否有採用 SELECT * 的方式去取資料,這樣可能會多出一些欄位,還有就是當使用 INSERT 指令的時候,也要指定欄位,且不能針對這個計算欄位做處理。

image