Xml Query Performance Sql Server

Question

Post reply

Xml Query Performance Sql Server

mail4bbt

SSC-Addicted

Points: 416
More actions
June 1, 2013 at 4:52 pm

#394893

Hello, I have the below data structure
The query is taking a while to run. I have a primary xml index, secondary xml indexes, and selective xml index on the table, (which sped it up alot), but it is the Wildcard that is really hurting the performance of this query. I have tried rewriting the query as well, but they were still running slow.
Can the wildcard be removed and still return expected results?
I am running this on both Sql Server 2008 and 2012
Xml Schema -- This is slimmed down version there are more Forms Elements.
USE tempdb
GO
SET NOCOUNT ON
GO
IF OBJECT_ID('[dbo].[XmlTable]') IS NOT NULL
DROP TABLE [dbo].[XmlTable]
GO
CREATE TABLE [dbo].[XmlTable](
[XmlId] [int] IDENTITY(1,1) NOT NULL,
[XmlDocument] [xml] NOT NULL,
CONSTRAINT [PK_XmlTable] PRIMARY KEY CLUSTERED
(
[XmlId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT INTO [dbo].[XmlTable] ( XmlDocument )
SELECT '<dev:Doc xmlns:dev="http://www.w3.org/2001/XMLSchema" Number="0" SchemaVersion="0.1" Settings="Testing" Title="Ordering">
<dev:Forms FormId="A1">
<dev:A1 ItemNumber="1" ItemGuid="{F402C584-555E-4D07-8C35-E88889B9DA44}">
<dev:Codes>D</dev:Codes>
<dev:Required>true</dev:Required>
<dev:Informational>false</dev:Informational>
<dev:Visitors>
<dev:Visitor Name="Dev01" Location="STLRF">
<dev:Divisions>
<dev:Division Number="1" Name="TFR3" Usage="Monitor">
<dev:Description>Development Fundamentals</dev:Description>
</dev:Division>
<dev:Division Number="2" Name="DEF32" Usage="Monitor">
<dev:Description>Testing Fundamentals</dev:Description>
</dev:Division>
<dev:Division Number="3" Name="DEP13" Usage="None">
<dev:Description>Guided Fundamentals</dev:Description>
</dev:Division>
</dev:Divisions>
</dev:Visitor>
<dev:Visitor Name="Dev02" Location="STLRF">
<dev:Divisions>
<dev:Division Number="1" Name="TFR3" Usage="Monitor">
<dev:Description>Development Fundamentals</dev:Description>
</dev:Division>
<dev:Division Number="2" Name="DEF32" Usage="Monitor">
<dev:Description>Testing Fundamentals</dev:Description>
</dev:Division>
<dev:Division Number="3" Name="DEP13" Usage="None">
<dev:Description>Guided Fundamentals</dev:Description>
</dev:Division>
</dev:Divisions>
</dev:Visitor>
<dev:Visitor Name="Dev03" Location="FGRTY">
<dev:Divisions>
<dev:Division Number="1" Name="TFR3" Usage="Monitor">
<dev:Description>Development Fundamentals</dev:Description>
</dev:Division>
<dev:Division Number="2" Name="DEF32" Usage="Monitor">
<dev:Description>Testing Fundamentals</dev:Description>
</dev:Division>
<dev:Division Number="3" Name="DEP13" Usage="None">
<dev:Description>Guided Fundamentals</dev:Description>
</dev:Division>
</dev:Divisions>
</dev:Visitor>
</dev:Visitors>
<dev:Senders>
<dev:Sender Name="FGY(14A)" />
</dev:Senders>
</dev:A1>
</dev:Forms>
<dev:Forms FormId="A2">
<dev:A2 ItemNumber="1" ItemGuid="{3563F33E-B03A-4859-850E-A87D35BD8562}">
<dev:Codes>C</dev:Codes>
<dev:Required>true</dev:Required>
<dev:Informational>false</dev:Informational>
<dev:Remarks>Support</dev:Remarks>
<dev:Notes>Ready</dev:Notes>
<dev:Visitors>
<dev:Visitor Name="GHFF">
<dev:Divisions>
<dev:Division Number="0" Name="Trial" Usage="None">
<dev:FromLocation>LOPO</dev:FromLocation>
<dev:ToLocation>RDSS</dev:ToLocation>
<dev:Description>Rich Filter</dev:Description>
</dev:Division>
</dev:Divisions>
</dev:Visitor>
</dev:Visitors>
<dev:Senders>
<dev:Sender Name="W33R" />
</dev:Senders>
<dev:IsReady>true</dev:IsReady>
<dev:IsCall>false</dev:IsCall>
</dev:A2>
<dev:A2 ItemNumber="2" ItemGuid="{CCFB2D5D-A23E-412D-8541-536451873713}">
<dev:Codes>A</dev:Codes>
<dev:Required>true</dev:Required>
<dev:Informational>false</dev:Informational>
<dev:Remarks>Loader Ready</dev:Remarks>
<dev:Notes>Ready</dev:Notes>
<dev:Visitors>
<dev:Visitor Name="UDT">
<dev:Divisions>
<dev:Division Number="0" Name="Trial" Usage="None">
<dev:FromLocation>TYUJ</dev:FromLocation>
<dev:ToLocation>DETF</dev:ToLocation>
<dev:Description>Web Enhance</dev:Description>
</dev:Division>
</dev:Divisions>
</dev:Visitor>
</dev:Visitors>
<dev:Senders>
<dev:Sender Name="RJ4" />
</dev:Senders>
<dev:IsReady>true</dev:IsReady>
<dev:IsCall>false</dev:IsCall>
</dev:A2>
</dev:Forms>
</dev:Doc>'
GO
The best working query
;WITH XMLNAMESPACES (Default 'http://www.w3.org/2001/XMLSchema' )
SELECT
a.value('@Number[1]', 'int') as Number
,b.value('(@FormId)[1]', 'NVARCHAR(50)') Form
,c.value('@ItemGuid[1]', 'uniqueidentifier') as ItemGuid
,c.value('@ItemNumber[1]', 'INT') AS ItemNumber
,d.value('(@Name)[1]','nvarchar(50)') As Visitor
,d.value('(@Location)[1]','nvarchar(50)') As Location
,e.value('(@Name)[1]', 'NVARCHAR(50)') As Sender
From
XmlTable As X
CROSS APPLY Xmldocument.nodes('Doc') As aa(a)
CROSS APPLY a.nodes('Forms') As bb(b)
CROSS APPLY b.nodes('*') As cc(c)
CROSS APPLY c.nodes('Visitors/Visitor') as dd(d)
CROSS APPLY c.nodes('Senders/Sender') as ee(e)
attempted rewrite #1 Including the Visitor and Sender path in the value select slowed it down alot, plus some can contain more than one like A1 has multiple Visitor, so they must be in the nodes method.
;WITH XMLNAMESPACES (Default 'http://www.w3.org/2001/XMLSchema' )
SELECT
a.value('@Number[1]', 'int') as Number
,a.value('(Forms/@FormId)[1]', 'NVARCHAR(50)') Form
,b.value('@ItemGuid[1]', 'uniqueidentifier') as ItemGuid
,b.value('@ItemNumber[1]', 'INT') AS ItemNumber
,b.value('(Visitors/Visitor/@Name)[1]', 'NVARCHAR(50)') AS Visitor
,b.value('(@Location)[1]','nvarchar(50)') As Location
,b.value('(Senders/Sender/@Name)[1]', 'NVARCHAR(50)') As Sender
From
XmlTable As X
CROSS APPLY Xmldocument.nodes('Doc') As aa(a)
CROSS APPLY a.nodes('Forms/*') As bb(b)
attempted rewrite #2
;WITH XMLNAMESPACES (Default 'http://www.w3.org/2001/XMLSchema' )
SELECT
a.value('@Number[1]', 'int') as Number
,b.value('(@FormId)[1]', 'NVARCHAR(50)') Form
,c.value('@ItemGuid[1]', 'uniqueidentifier') as ItemGuid
,c.value('@ItemNumber[1]', 'INT') AS ItemNumber
,c.value('(Visitors/Visitor/@Name)[1]', 'NVARCHAR(50)') AS Visitor
,c.value('(@Location)[1]','nvarchar(50)') As Location
,c.value('(Senders/Sender/@Name)[1]', 'NVARCHAR(50)') As Sender
From
XmlTable As X
CROSS APPLY Xmldocument.nodes('Doc') As aa(a)
CROSS APPLY a.nodes('Forms') As bb(b)
CROSS APPLY b.nodes('*') As cc(c)
Expected Results
Number FormItemGuid ItemNumber Visitor Location Sender
0A1F402C584-555E-4D07-8C35-E88889B9DA441Dev01STLRFFGY(14A)
0A1F402C584-555E-4D07-8C35-E88889B9DA451Dev02STLRFFGY(14A)
0A1F402C584-555E-4D07-8C35-E88889B9DA461Dev03FGRTYFGY(14A)
0A23563F33E-B03A-4859-850E-A87D35BD85621GHFFNULLW33R
0A2CCFB2D5D-A23E-412D-8541-5364518737132UDTNULLRJ4

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

lgegerton Old Hand Points: 316 More actions · Answer 1

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=185751

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

And now we know why I hate XML and the bloat it brings to all facets of data. This is "progress"? :blink:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)