sql-server - SQL Server Xml 列最佳实践

Question

使用 Sql server Xml 列以确保快速性能并简化报告的最佳实践是什么？

如何设置栏目？您是否将其保留为未键入？或者将其与架构相关联？

将 xml 列与架构相关联是否会提高查询性能？

我们对xml列的使用如下:

A.> 在每个客户的基础上，我们可以定义他们数据的灵活存储，而无需彻底检查我们的数据库。

B.> 我们需要为每个返回数据的客户构建报告 View ，就好像它是一个简单的表格(用于 Crystal 报告或 Sql Server Reporting Services)。

我们目前用来查询的语法如下:

SELECT 
Id, 
doc.value('@associatedId','nvarchar(40)') as AssocId,
doc.value('@name1', 'nvarchar(255)') as Name1,
doc.value('@name2', 'nvarchar(255)') as Name2,
doc.value('@name3', 'nvarchar(255)') as Name3,
doc.value('@number', 'nvarchar(255)') as Number
From OrderDetails
CROSS APPLY OrderDetails.XmlData.nodes('//root/reviewers/reviewer') as XmlTable(doc)

有没有更快的方法来做到这一点？这个查询在一个有 100 万条记录的表中运行缓慢，但目前只有 800 条有 xml 数据!

谢谢

皮特

Answer 1

来自 XML Best Practices for Microsoft SQL Server 2005 :

使用类型化或非类型化的 XML？

Use untyped XML data type under the following conditions:

• You do not have a schema for your XML data.
• You have schemas but you do not want the server to validate the data.

This is sometimes the case when an application performs client-side validation before storing the data at the server, or temporarily stores XML data invalid according to the schema, or uses XML schema features not supported at the server (for example, key/keyref).

Use typed XML data type under the following conditions:

• You have schemas for your XML data and you want the server to validate your XML data according on the XML schemas.
• You want to take advantage of storage and query optimizations based on type information.
• You want to take better advantage of type information during compilation of your queries such as static type errors.

Typed XML columns, parameters and variables can store XML documents or content, which you have to specify as a flag (DOCUMENT or CONTENT, respectively) at the time of declaration. Furthermore, you have to provide one or more XML schemas. Specify DOCUMENT if each XML instance has exactly one top-level element; otherwise, use CONTENT. The query compiler uses DOCUMENT flag in type checks during query compilation to infer singleton top-level elements.

将 xml 列与架构相关联是否会提高查询性能？参见上一点:如果您想利用基于类型信息的查询优化，请使用typed XML。

还有关于 XML 索引的好处的冗长讨论:

Your application may benefit from an XML index under the following conditions:

• Queries on XML columns are common in your workload. XML index maintenance cost during data modification must be taken into account.
• Your XML values are relatively large and the retrieved parts are relatively small. Building the index avoids parsing the whole data at runtime and benefits index lookups for efficient query processing.

最重要的是，适合您使用的辅助 XML 索引类型:

• If your workload uses path expressions heavily on XML columns, the PATH secondary XML index is likely to speed up your workload. The most common case is the use of exist() method on XML columns in WHERE clause of Transact-SQL.
• If your workload retrieves multiple values from individual XML instances using path expressions, clustering paths within each XML instance in the PROPERTY index may be helpful. This scenario typically occurs in a property bag scenario when properties of an object are fetched and its relational primary key value is known.
• If your workload involves querying for values within XML instances without knowing the element or attribute names that contain those values, you may want to create the VALUE index. This typically occurs with descendant axes lookups, such as //author[last-name="Howard"], where <author> elements can occur at any level of the hierarchy and the search value ("Howard") is more selective than the path. It also occurs in "wildcard" queries, such as /book [@* = "novel"], where the query looks for <book> elements with some attribute having the value "novel".