Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Xml Query Performance

Author  Topic 

bbt2d
Starting Member

13 Posts
Posted - 2013-06-01 : 18:59:48
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 	Form	ItemGuid	                    ItemNumber  Visitor Location Sender
0	A1	F402C584-555E-4D07-8C35-E88889B9DA44	1	Dev01	STLRF	FGY(14A)
0	A1	F402C584-555E-4D07-8C35-E88889B9DA45	1	Dev02	STLRF	FGY(14A)
0	A1	F402C584-555E-4D07-8C35-E88889B9DA46	1	Dev03	FGRTY	FGY(14A)
0	A2	3563F33E-B03A-4859-850E-A87D35BD8562	1	GHFF	NULL	W33R
0	A2	CCFB2D5D-A23E-412D-8541-536451873713	2	UDT	NULL	RJ4

James K
Master Smack Fu Yak Hacker

3873 Posts
Posted - 2013-06-01 : 20:10:19
The three queries are not logically equivalent, unless you happen to have only one node each under each Form/*, so not sure if the comparisons are valid. The second and third would return fewer rows if there was more than one node under Form/*.

As for your question about using the * in the path, it is a wild card specification, so if you were to replace the * with a specific node name - A1, for example - you would not pick up the rows from any nodes at the same level - A2, for example.

You can try to avoid the multiple cross apply's and instead, go down to the lowest level and navigate up the tree for data from parent nodes. In general, navigating up the parent tree is expensive, but it is more expensive to generate all those virtual tables via cross applies. But I am speaking in generalities - not sure which one will work better in your case, or whether a combination might be better..
;WITH XMLNAMESPACES (Default 'http://www.w3.org/2001/XMLSchema' )
SELECT 
	
	 d.value('(../../../../@Number)[1]', 'int') as Number
	,d.value('(../../../@FormId)[1]', 'NVARCHAR(50)') Form
	,d.value('(../../@ItemGuid)[1]', 'uniqueidentifier') as ItemGuid
	,d.value('(../../@ItemNumber)[1]', 'INT') AS ItemNumber
	,d.value('(@Name)[1]','nvarchar(50)') As  Visitor
	,d.value('(@Location)[1]','nvarchar(50)') As Location,
	d.value('(../../Senders/Sender/@Name)[1]', 'NVARCHAR(50)') As Sender		
From
		XmlTable As X
		cross apply Xmldocument.nodes('Doc/Forms/*/Visitors/Visitor') as dd(d)
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts
Posted - 2013-06-01 : 20:10:19
The three queries are not logically equivalent, unless you happen to have only one node each under each Form/*, so not sure if the comparisons are valid. The second and third would return fewer rows if there was more than one node under Form/*.

As for your question about using the * in the path, it is a wild card specification, so if you were to replace the * with a specific node name - A1, for example - you would not pick up the rows from any nodes at the same level - A2, for example.

You can try to avoid the multiple cross apply's and instead, go down to the lowest level and navigate up the tree for data from parent nodes. In general, navigating up the parent tree is expensive, but it is more expensive to generate all those virtual tables via cross applies. But I am speaking in generalities - not sure which one will work better in your case, or whether a combination might be better..
;WITH XMLNAMESPACES (Default 'http://www.w3.org/2001/XMLSchema' )
SELECT 
	
	 d.value('(../../../../@Number)[1]', 'int') as Number
	,d.value('(../../../@FormId)[1]', 'NVARCHAR(50)') Form
	,d.value('(../../@ItemGuid)[1]', 'uniqueidentifier') as ItemGuid
	,d.value('(../../@ItemNumber)[1]', 'INT') AS ItemNumber
	,d.value('(@Name)[1]','nvarchar(50)') As  Visitor
	,d.value('(@Location)[1]','nvarchar(50)') As Location,
	d.value('(../../Senders/Sender/@Name)[1]', 'NVARCHAR(50)') As Sender		
From
		XmlTable As X
		cross apply Xmldocument.nodes('Doc/Forms/*/Visitors/Visitor') as dd(d)
Go to Top of Page

bbt2d
Starting Member

13 Posts
Posted - 2013-06-01 : 21:50:41
Thanks James I will give this a try! I was wondering if all the cross applies were the culprit bring performance down! I will take a look and maybe go with a combination!

Thanks!
Go to Top of Page

stepson
Aged Yak Warrior

545 Posts
Posted - 2013-06-02 : 01:24:24
Hi
try the approache with OpenXML


declare @XmlDocument as xml
declare @DocHandle as int
set @XmlDocument ='<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>'

EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument,
'<Document xmlns:dev="http://www.w3.org/2001/XMLSchema" />'

SELECT *
FROM OPENXML (@DocHandle, '/dev:Doc/dev:Forms/dev:*/dev:Visitors/dev:Visitor',1)
      WITH (Number int '(../../../../@Number)[1]',
			Form nvarchar(50) '(../../../@FormId)[1]',
			ItemGuid uniqueidentifier '(../../@ItemGuid)[1]',
			ItemNumber int '(../../@ItemNumber)[1]',
			Visitor nvarchar(50) '(@Name)[1]',
			Location nvarchar(50) '(@Location)[1]' ,
	        Sender nvarchar(50) '(../../dev:Senders/dev:Sender/@Name)[1]')
EXEC sp_xml_removedocument @DocHandle





S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site