Speed Up XML Queries in SQL Server 2005

Question

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

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.

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'

Answer 1

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.

After adding /text() such as

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.

Answer 2

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

http://blogs.technet.com/b/wardpond/archive/2005/06/23/sql-server-2005-xquery-performance-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.

Answer 3

Read XML Best Practices for Microsoft SQL Server 2005

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

• Use node/text() instead of just node for your xpaths.
• Try never to use ../ in your xpath expressions, as it slows it down SIGNIFICANTLY

Answer 4

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