SQL Server XML效能最佳化(Best Practices)

1. XML資料模型
XML儲存與處理查詢的效能取決於資料庫的設計與XML資料的結構與粒度。是否要使用XML資料模型，看你

是不是有半結構化的資料，需要保留文檔結構與階層的標記語言資料，或可變的結構。

1.1 標記 Markups
同樣的資料可以以不同的方式來標記，視內容（element值）與中繼資料資訊（attribute值）。越具體的

element名越易於閱讀，且對產生有效查詢計劃有協助。冗長的標記會增加儲存的成本。例如：
a.
<item type="book"><title>Writing Secure Code</title></item>
<item type="DVD"><title>The Godfather</title></item>
如果要查詢book，需要寫為：/item[@type = "book"]
b.
<book><title>Writing Secure Code</title></book>
<DVD><title>The Godfather</title></DVD>

如果要查詢book，只需要寫：/book ，這樣效率更高也看起來更簡單，而且減少了primary XML index（

主XML索引）的層數（少了item層）。這就是具體標記（Specific Markups）相對一般標記（Generic

Markups）的優點。

對於被類型化的的XML（typed XML），還可以縮減到兩層：
<book title="Writing Secure Code"/>
<DVD title="The Godfather"/>

像<DVD><title>The Godfather</title></DVD>這種格式，稱為element-centric markup，查詢格式

為/DVD[title = "The Godfather"]
像<DVD title="The Godfather"/>這種格式，attribute-centric markup，查詢格式為/DVD[@title =

"The Godfather"] ，可以減少一個JOIN。

1.2 Typed 和 Untyped XML（類型化與非類型化的XML）
非類型化的XML（untyped XML，沒有以XML schema來表示）在SQL Server內部以Unicode字串的形式存

儲。對他們的操作需要資料轉換到相應的類型。例如 (/book/price)[1] > 19.99，會有<price>轉換為

decimal的過程。大量類似的比較就會非常耗資源，這就引出了XML schema中類型資訊的重要性。
類型資訊在以下幾個方面起作用：
a. 插入更新的XML資料先被驗證是否符合schema，然後以二進位形式儲存，方便更快地轉換
b. 類型化的值被儲存在XML索引中
c. 類型化的資料還可以減少範圍掃描，例如(/book/price)[1]中的序數[1]在XML schema規定了<price>

為單值時就是不必要的。

1.3 屬性
可以使用UDF（使用者自訂函數）來獲得計算資料行（computed column）。可以在計算資料行上添加索引。由於計算資料行是預計算好的，查詢速度更快。
範例：
書籍都有ISBN號，把ISBN單獨作為計算資料行的過程如下
a. 定義擷取ISBN號的函數
CREATE FUNCTION udf_get_book_ISBN (@xData xml) RETURNS varchar(20)
WITH SCHEMABINDING
BEGIN
   RETURN @xData.value('(/book/@ISBN)[1]', 'varchar(20)')
END
b. 添加計算資料行
CREATE TABLE docs (id int PRIMARY KEY, xCol XML)
ALTER TABLE docs ADD ISBN AS dbo.udf_get_book_ISBN(xCol)
c. 添加非聚類索引
CREATE INDEX COMPUTED_IDX ON docs (ISBN)
d. 寫查詢
如果不使用ISBN列，查詢如下：
SELECT xCol
FROM   docs
WHERE  xCol.exist ('/book/@ISBN[. = "0-2016-3361-2"]') = 1
如果使用ISBN列
SELECT xCol
FROM   docs
WHERE  ISBN = '0-2016-3361-2'

還可以建立屬性工作表（Property Table）

2. 批量載入XML資料
--可以使用BCP IN，BULK INSERT和OPENROWSET方法，因為和這次的case無關暫且略過。

3. XML索引
3.1 普通索引
推薦建立初級XML索引（primary XML index），實際上是建立了一個B+樹。還可以對於PATH，PROPERTY，VALUE建立次級XML索引（secondary XML indexes）。
a. PATH索引
適用於有類似/book[@ISBN = "0-2016-3361-2"]的路徑運算式，路徑越長越有效
b. PROPERTY索引
適用於XML中的多properties查詢
c. VALUE索引
適用於存在子軸（//操作符）和萬用字元（類似(/book[@* = "novel"])）的查詢
維護索引的開銷也是需要整體考慮的。

--關於XML部分更新和這次的case無關暫且略過。
--關於XML全文索引和這次的case無關暫且略過。

4. 查詢最佳化
4.1 使用exist()方法來檢測是否存在
儘可能使用exist()來代替value()
比如如下的查詢：
SELECT *
FROM   docs
WHERE    xCol.exist('(/book/title/text())[.="Writing Secure Code"]') = 1
比
SELECT *
FROM   docs
WHERE  xCol.value('(/book/title)[1]', 'varchar(50)') = 'Writing Secure Code'

4.2 最佳化XML blobs（二進位XML）
更多的tempDB檔案可以提供更好的可擴充性。
減少額外的XML資料類型轉換

4.3 指定Singleton Elements（單件元素）
對於類型化的XML，預設是singleton的
非類型化的XML，需要加[1]，xCol.query ('/book/title')會被自動轉換為xCol.query ('(/book/title)[1]')。

4.4 對於非類型化的XML的text彙總
(/book/title[.="Writing Secure Code"])[1]需要彙總<title>下所有的text節點
如果<title> element只有一個text節點，那麼可以使用(/book/title/text())[1][. = "Writing Secure Code"]更有效。

4.5 將XQuery和XML DML運算式參數化
XQuery和XML DML不會自動參數化，最好使用sql:column() 或sql:variable() 。

4.6 序數和預計的最佳化
減少語句的分支，如/book[@ISBN = "1-8610-0157-6"]/author/first-name，最好使用/book[author/first-name = "Davis"]這樣的格式。
把序數移到路徑的最後，/book[1]/title[1]等同於 (/book/title)[1]
使用Context Node（上下文節點）
如下的範例：
SELECT *
FROM   docs
WHERE  xCol.exist ('/book[@subject = "security"]') =  1
分別對/book和/book/@subject進行了評估，並檢測了後者是否包含了值“security”
如果使用如下表達：
SELECT *
FROM   docs
WHERE  xCol.exist ('/book/@subject[. = "security"]') =  1
只會對/book/@subject 進行評估

動態查詢與本次的case無關暫且略過。

具體案例等我做完這個case再詳細闡述。

參考資料：
Performance Optimizations for the XML Data Type in SQL Server 2005
http://technet.microsoft.com/en-us/library/ms345118.aspx
XML Best Practices for Microsoft SQL Server 2005
http://technet.microsoft.com/en-us/library/ms345115.aspx