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

轉載于:https://www.cnblogs.com/galaxyyao/archive/2009/04/23/1442417.html

總結

以上是生活随笔為你收集整理的SQL Server XML性能优化(Best Practices)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。