再談T-SQL複合字串鍵值比對-借用PARSENAME()

以下是我實際遇到的情境,複合鍵在某些資料表拆成兩個或三個欄位,但在某些資料表則用"."或"-"串接存成單一欄位。(不要問我為什麼搞出這種不一致的設計,誰沒有過去?)

如以下的例子,在JStock資料表的Market及Symbol欄位,在JReport則使用一個FullSymbol欄位,存成"Symbol.Market"。

問題來了,如果這兩個Table要JOIN怎麼辦?過去用過一種鳥方法:
JReport R JOIN JStock S ON R.FullSymbol = S.Symbol + '.' + S.Market格式。

這方法挺管用,但可能會有效能問題。以上面的例子,假設JStock有兩萬筆,JReport查詢結果只有四筆,為了找出這四筆對應的資料,SQL Server得把整個JStock每一筆的Market跟Symbol拿出來先相加再比對,無法善用JStock的Index加速。

先前曾介紹過用SQLXML拆字串的技巧,但XML轉換演算法太曲折且效率不佳,前幾天無意發現一個T-SQL內建函式-PARSENAME()(SQL 2008 R2起支援),原本用來將SvrName.DbName.SchemaName.ObjectName拆解成四個部分,例如以下範例:

只要複合字串的分隔符號是"."(若不是,可用REPLACE置換),就能用PARSENAME擷取指定一段落(最多能只解析四段,應能滿足絕大部分場合),不用寫自訂函數,效能又比XML轉換好。

最後補充一點,以上做法雖善用JStock Symbol、Market Index提升效能,但使用PARSENAME解析JReport FullSymbol仍會消耗效能。如果允許變動Schema以空間換取時間,可考慮為JStock建立計算型欄位(Computed Column)-FullSymbol = Symbol + '.' + Market,設為IsPersisted還能建立Index,直接使用FullSymbol = FullSymbol比對,效能可再大幅提升。

歡迎推文分享:
Published 07 September 2016 10:19 PM 由 Jeffrey
Filed under:
Views: 4,546



意見

# oaww said on 07 September, 2016 09:26 PM

想請問一下如果用View會更單純嗎?因為View也可以建Index..

# Jeffrey said on 08 September, 2016 08:34 PM

to oaww, 感謝補充,Indexed View也是可行的做法。會不會更單純視個人偏好,另建View對我來說有兩項困擾,第一個是增加了性質極相似的資料庫物件,可能讓接手的人混淆,第二則是需面對開發者最頭痛的問題,這View的名字好難取,呵。

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<September 2016>
SunMonTueWedThuFriSat
28293031123
45678910
11121314151617
18192021222324
2526272829301
2345678
 
RSS
創用 CC 授權條款
【廣告】
twMVC
最新回應

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication