当前位置：文江博客话题详情

Sql Server Xml 列最佳实践

发布于 2024-09-15 04:01:26 字数 793 浏览 9 评论 0原文

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

专栏如何设置？你把它保留为无类型吗？或将其与模式相关联？

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

我们对xml列的使用如下：

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

B．>我们需要为每个客户构建报告视图，将其数据返回为一个简单的表格（对于水晶报告或 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 数据！

谢谢皮特

原文

What are the best practices for working with Sql server Xml columns to ensure quick performance and also ease of reporting?

How do you set up the column?
do you leave it as untyped?
or associate it with a schema?

Does associating the xml column with a schema improve query performance?

Our use of xml columns is as follows:

A.> On a PER customer basis we can define flexible storage of their data without overhauling our db.

B.> We need to build reporting views for each customer which returns their data as if it was a simple table (for crystal reports or Sql Server Reporting Services).

The syntax we currently use to query is as follows:

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)

Is there a quicker way to do this? this query runs slowly for us in a table with 1million records, but only 800 currently have xml data!

Thanks

Pete

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

淡莣 2024-09-22 04:01:26

来自Microsoft SQL Server 2005 的 XML 最佳实践：

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

使用非类型化 XML 数据类型
以下条件：
您没有 XML 数据的架构。
您有架构，但您不希望服务器验证数据。
有时会出现这种情况
应用程序执行客户端
将数据存储在之前进行验证
服务器，或临时存储XML
根据模式数据无效，
或不使用 XML 模式功能
服务器支持（例如，
key/keyref）。
使用类型化 XML 数据类型
以下条件：
您有 XML 数据的架构，并且您希望服务器
根据以下内容验证您的 XML 数据
XML 架构。
您希望利用基于存储和查询优化的
关于类型信息。
您希望在以下过程中更好地利用类型信息：
您的查询的汇编，例如
静态类型错误。
类型化 XML 列、参数和
变量可以存储 XML 文档或
内容，您必须将其指定为
标志（文档或内容，
分别）在当时
宣言。此外，您还必须
提供一个或多个 XML 模式。
如果每个 XML 实例都指定 DOCUMENT
仅有一个顶级元素；
否则，请使用内容。查询
编译器在类型中使用 DOCUMENT 标志
在查询编译期间检查
推断单例顶级元素。

将 xml 列与架构关联是否可以提高查询性能？请参阅上面的观点：如果您想利用基于类型信息的查询优化，请使用类型化 XML。

对于 XML 索引的优点也有很长的讨论：

在以下条件下，您的应用程序可能会受益于 XML 索引：
对 XML 列的查询在您的工作负载中很常见。必须考虑数据修改期间的XML索引维护成本。
您的 XML 值相对较大，而检索到的部分相对较小。构建索引可以避免在运行时解析整个数据，并有利于索引查找以实现高效的查询处理。

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

如果您的工作负载在 XML 列上大量使用路径表达式，则 PATH 辅助 XML 索引可能会加快您的工作负载的速度。最常见的情况是在 Transact-SQL 的 WHERE 子句中对 XML 列使用 exist() 方法。
如果您的工作负载使用路径表达式从各个 XML 实例检索多个值，则 PROPERTY 索引中每个 XML 实例内的路径聚类可能会有所帮助。这种场景通常发生在属性包场景中，当获取对象的属性并且其关系主键值已知时。
如果您的工作负载涉及查询 XML 实例中的值，但不知道包含这些值的元素或属性名称，您可能需要创建 VALUE 索引。这通常发生在后代轴查找中，例如 //author[last-name="Howard"]，其中元素可以出现在轴的任何级别。层次结构和搜索值 ("Howard") 比路径更具选择性。它也出现在“通配符”查询中，例如 /book [@* = "novel"]，其中查询查找具有某些属性的元素值“小说”。

From XML Best Practices for Microsoft SQL Server 2005:

Use a typed or untyped 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.

Does associating the xml column with a schema improve query performance? See above point: use typed XML if you want to take advantage of query optimizations based on type information.

There is also a lengthy discussion over the benefits of XML indexes:

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.

And most importantly, the appropriate type of secondary XML index for your usage:

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".

回复收藏 0 原文

兮子 2024-09-22 04:01:26

如果像上面的示例一样，您使用 XML 来存储各种字符串列，那么我认为您不会真正从类型化 XML 中受益，除非您需要服务器来验证数据。就性能而言，我怀疑非类型化会更快。

对于这些类型的查询，您绝对需要有适当的 XML 索引，它们对于 XML 查询的良好性能至关重要。如果没有索引，XML 列将存储为 blob，因此为了查询它们，SQL 需要首先将 blob 分解为 XML，然后执行您请求的任何操作。主 XML 索引将分解的 XML 存储在数据库中，因此不需要即时完成。您需要首先创建主 XML 索引，然后可以创建辅助 XML 索引来支持您的查询。

二级 XML 索引有 3 种类型：PATH、VALUE 和 PROPERTY。您需要哪些二级索引取决于您要执行的查询类型，因此我鼓励您查看联机丛书中的二级 XML 索引主题，以确定哪些索引对您有用：
http://msdn.microsoft.com/en-us /library/bb522562(SQL.100).aspx

回复收藏 0 原文

~没有更多了~