再談T-SQL複合字串鍵值比對-借用PARSENAME()
2 |
以下是我實際遇到的情境,複合鍵在某些資料表拆成兩個或三個欄位,但在某些資料表則用"."或"-"串接存成單一欄位。(不要問我為什麼搞出這種不一致的設計,誰沒有過去?)
如以下的例子,在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比對,效能可再大幅提升。
Comments
# by oaww
想請問一下如果用View會更單純嗎?因為View也可以建Index..
# by Jeffrey
to oaww, 感謝補充,Indexed View也是可行的做法。會不會更單純視個人偏好,另建View對我來說有兩項困擾,第一個是增加了性質極相似的資料庫物件,可能讓接手的人混淆,第二則是需面對開發者最頭痛的問題,這View的名字好難取,呵。