在SQL Server 2005中加速XML查询。

I store all my data in on XML column in SQL Server 2005.

我将所有数据存储在SQL Server 2005的XML列中。

As more and more records are being inserted, I notice the queries are slowing down. I've tried creaeting a Primary XML Index, as well as a Secondary VALUE index and this did not do anything to help the speed.

随着越来越多的记录被插入，我注意到查询正在减慢。我尝试过创建一个主XML索引，以及一个辅助值索引，但这并没有提高速度。

Any tips,thoughts, or tricks that I'm missing?

你有什么建议、想法或诀窍吗?

Sample View that I query:

我查询的示例视图:

SELECT Id
, CaseNumber
, XmlTest.value('(/CodeFiveReport/ReportEvent/StartDate)[1]', 'varchar(25)') + ' ' + XmlTest.value('(/CodeFiveReport/ReportEvent/StartTime)[1]', 'varchar(25)') as StartDate
, XmlTest.value('(/CodeFiveReport/@Status)[1]', 'varchar(10)') as [Status]
, XmlTest.value('(/CodeFiveReport/ReportEvent/Address/PatrolDistrict/@Name)[1]', 'varchar(100)') as PatrolDistrict
, XmlTest.value('(/CodeFiveReport/PrimaryUnit/@Name)[1]', 'varchar(40)') as PrimaryUnit
, XmlTest.value('(/CodeFiveReport/ReportEvent/Address/@StreetNumber)[1]', 'varchar(50)') + ' ' + XmlTest.value('(/CodeFiveReport/ReportEvent/Address/@StreetName)[1]', 'varchar(50)') + ' ' + XmlTest.value('(/CodeFiveReport/ReportEvent/Address/StreetSuffix/@Name)[1]', 'varchar(50)') + ' ' + XmlTest.value('(/CodeFiveReport/ReportEvent/Address/@City)[1]', 'varchar(50)') + ' ' + XmlTest.value('(/CodeFiveReport/ReportEvent/Address/State/@Abbreviation)[1]', 'varchar(50)') + ' '  + XmlTest.value('(/CodeFiveReport/ReportEvent/Address/@ZipCode)[1]', 'varchar(50)') as Location
, XmlTest.value('(/CodeFiveReport/ReportEvent/ReportType/@Name)[1]', 'varchar(50)') as ReportType
, XmlTest.value('(/CodeFiveReport/ReportEvent/Offenses/OffenseDescription/OffenseType/@CodeAndDescription)[1]', 'varchar(50)') as IncidentType
, XmlTest as Report
, CreatedBy as UserId
, XmlTest.value('(/CodeFiveReport/PrimaryUnit/@ID)[1]', 'integer') as UnitId
, XmlTest.value('(/CodeFiveReport/PrimaryUnit/@Code)[1]', 'varchar(6)') as UnitCode
, XmlTest.value('(/CodeFiveReport/Owner/AgencyID)[1]', 'char(2)') as AgencyId   
, IsLocked
, LockedBy
, XmlTest.value('(/CodeFiveReport/VersionUsed)[1]', 'varchar(20)') as VersionUsed
FROM UploadReport
WHERE XmlTest.value('(/CodeFiveReport/Owner/AgencyID)[1]', 'char(2)') = '06'

4 个解决方案

#1

Read XML Best Practices for Microsoft SQL Server 2005

阅读Microsoft SQL Server 2005的XML最佳实践

The two tips I recall the most making a difference in speead are

我记得最能改变速度的两个技巧是

• Use node/text() instead of just node for your xpaths.
• 使用node/text()而不是仅使用node。
• Try never to use ../ in your xpath expressions, as it slows it down SIGNIFICANTLY
• 尽量不要使用…/在xpath表达式中，当它显著降低时

#2

The query suffered from a performance issue only when the results were used in an insert into a table or a join. Simply returning the values from the select in management studio was nearly instant. Prefix that with an INSERT INTO and it would take over 30 seconds for the same work.

只有在将结果用于插入表或连接时，查询才会遇到性能问题。简单地从management studio中的select返回值几乎是即时的。在前面加上一个插入，同样的工作需要30秒以上的时间。

After adding /text() such as

添加/text()之后

from @list.nodes('/List/Id/text()') as C(C)

instead of what I had:

而不是我拥有的:

from @list.nodes('/List/Id') as C(C)

This brought the query back to a zero-second execution even with the insert.

这使查询返回到零秒执行，即使使用插入。

#3

Well, I was able to drastically speed up my query using two subqueries then parsing the XML from that result set.

我可以使用两个子查询来快速查询，然后从结果集中解析XML。

#4

I was able to speed up a query from running in 4 minutes 30 seconds to 20 seconds by using some tips from:

通过使用以下的一些提示，我可以在4分钟30秒到20秒内加速查询。

http://blogs.technet.com/b/wardpond/archive/2005/06/23/sql-server-2005-xquery-performance-tips.aspx

http://blogs.technet.com/b/wardpond/archive/2005/06/23/sql服务器- 2005 - xquery -性能- tips.aspx

I had this:

我有这个:

SELECT
Package.query('(/D/D[@n="Main"]/node()[@n="FirstNames"]/text())[1]') as [Main.FirstNames],
Package.query('(/D/D[@n="Main"]/node()[@n="LastName"]/text())[1]') as [Main.LastName],

... lots more columns

…更多的列

Changing to this made all the difference:

改变到这一点就完全不同了:

SELECT
Package.query('(/D[1]/D[@n="Main"][1]/node()[@n="FirstNames"][1]/text())[1]') as [Main.FirstNames],
Package.query('(/D[1]/D[@n="Main"][1]/node()[@n="LastName"][1]/text())[1]') as [Main.LastName],

... lots more columns

…更多的列

By the look of your above query this might have helped you as well.

从上面的查询来看，这也可能对您有所帮助。