Performance issue with xquery

Question

Performance issue with xquery

sam 55243

SSCrazy

Points: 2697
More actions
December 23, 2016 at 4:48 am

#332923

Hi All,
While investigating for performance issue in one of the query ,i found maximum execution cost (more than 90%) is going for calculating the logic for a derived column as
shown below:
Logic: NumberOfDeleiveries = t.ParcelsXML.value('count(/Deliverables/Deleiveries[State=1])', 'int'),
Its basically reading throught the xml and getting the no of occurances of tag '<State>' having value as 1.
Execution plan also attached:
Here is the sample xml used for:
<Deliverables>
<Deleiveries>
<ID>1</ID>
<BID>P11368</BID>
<TID>P11368</TID>
<Type>Actual</Type>
<Export>true</Export>
<QY>987</QY>
<Units>MT</Units>
<Conv>80</Conv>
<PortID>470</PortID>
<Port>Luanda</Port>
<PortPath></PortPath>
<VesselID>1</VesselID>
<Vessel>TBN</Vessel>
<State>1</State>
<EventDates>18 Jan 2012</EventDates>
<EventDatesXML><?xml version="1.0" encoding="utf-16"?>
<EventDates xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<EventDate>
<TypeID>0</TypeID>
<StatusID>0</StatusID>
<Value>2012-01-18Z</Value>
</EventDate>
</EventDates></EventDatesXML>
</Deleiveries>
<Deleiveries>
<PID>2</PID>
<BID>P11368</BID>
<TID>P11368</TID>
<Type>Actual</Type>
<Export>true</Export>
<QY>90</QY>
<Units>MT</Units>
<Conv>80</Conv>
<PortID>470</PortID>
<Port>Luanda</Port>
<PortPath></PortPath>
<VesselID>1</VesselID>
<Vessel>TBN</Vessel>
<State>1</State>
<EventDates>22 Feb 2012</EventDates>
<EventDatesXML><?xml version="1.0" encoding="utf-16"?>
<EventDates xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<EventDate>
<TypeID>0</TypeID>
<StatusID>0</StatusID>
<Value>2012-02-22Z</Value>
</EventDate>
</EventDates></EventDatesXML>
</Deleiveries>
</Deliverables>
Is there any alternative/better way to handle this. Please let me know
Thanks
Sam

Viewing 9 posts - 1 through 8 (of 8 total)

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

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

Send some more details like the Table definition (structure, indexes, constratints...), the query you use and the execution plan, so that someone could do a better view on your problem.

Igor Micev,My blog: www.igormicev.com

drew.allen SSC Guru Points: 76945 More actions · Answer 2

In my very small scale tests, it appears that using the XML count function is much slower than using the SQL count function. Try the following:

NumberOfDeleiveries = ( SELECT COUNT(c.query) FROM t.ParcelsXML.nodes('/Deliverables/Deleiveries[State=1]') T(c) ),

Drew

PS You've misspelled deliveries.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

sam 55243 SSCrazy Points: 2697 More actions · Answer 3

Im getting the below error when trying to use above code

Msg 4104, Level 16, State 1, Line 47

The multi-part identifier "c.query" could not be bound.

Am i missing something over here.

Here is my code below

declare @test-2 xml

set @test-2 = '<Deliverables>

<Deleiveries>

<ID>1</ID>

<BID>P11368</BID>

<TID>P11368</TID>

<Type>Actual</Type>

<Export>true</Export>

<QY>987</QY>

<Units>MT</Units>

<Conv>80</Conv>

<PortID>470</PortID>

<Port>Luanda</Port>

<PortPath></PortPath>

<VesselID>1</VesselID>

<Vessel>TBN</Vessel>

<State>1</State>

<EventDates>18 Jan 2012</EventDates>

<EventDatesXML><EventDates>

<EventDate><TypeID>0</TypeID><StatusID>1</StatusID><Value>2007-01-08Z</Value></EventDate><EventDate><TypeID>4</TypeID><StatusID>1</StatusID><Value>2007-01-15Z</Value></EventDate><EventDate><TypeID>5</TypeID><StatusID>1</StatusID><Value>2007-01-19Z</Value></EventDate>

</EventDates>

</EventDatesXML>

</Deleiveries>

<Deleiveries>

<PID>2</PID>

<BID>P11368</BID>

<TID>P11368</TID>

<Type>Actual</Type>

<Export>true</Export>

<QY>90</QY>

<Units>MT</Units>

<Conv>80</Conv>

<PortID>470</PortID>

<Port>Luanda</Port>

<PortPath></PortPath>

<VesselID>1</VesselID>

<Vessel>TBN</Vessel>

<State>1</State>

<EventDates>22 Feb 2012</EventDates>

<EventDatesXML><EventDates>

<EventDate><TypeID>0</TypeID><StatusID>1</StatusID><Value>2007-01-08Z</Value></EventDate><EventDate><TypeID>4</TypeID><StatusID>1</StatusID><Value>2007-01-15Z</Value></EventDate><EventDate><TypeID>5</TypeID><StatusID>1</StatusID><Value>2007-01-19Z</Value></EventDate>

</EventDates>

</EventDatesXML>

</Deleiveries>

</Deliverables>';

SELECT COUNT(c.query) FROM @test.nodes('/Deliverables/Deleiveries[State=1]') T(c)

Let me know if there is modification required

sam 55243 SSCrazy Points: 2697 More actions · Answer 4

Hi Igor,

Here is the execution plan for the query that is having performance issue (Attached).

There are lot of tables used in that so not possible to send all the details of each and every table.

Let me know if there is any work around for getting the count of child node in xml.

Thanks

sam

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 5

One option is to try to replace the Table Valued Function, as drew.allen proposes.

Do you have xml and xml path indexes on the table(s) that is used in TVF? Try it, maybe you'll get a better execution.

Is the execution fast enough? Why does it mind if the query cost relative to the batch is eating all the execution time? If it's fast enough then you're fine.

However, if you don't have xml indexes, putting some will improve your query.

Igor Micev,My blog: www.igormicev.com

sam 55243 SSCrazy Points: 2697 More actions · Answer 6

Hi Igor,

One quick query, how do i add index on xml column and on which node using sql server 2005. If you take the above xml for instance.

Thanks,

sam

sam 55243 SSCrazy Points: 2697 More actions · Answer 7

Hi,

I modified the query to use SQL count function instead of xml count as suggested by drew .With this change i got 25~30 percent improvement in comparison to existing code.

I didn't go with xml indexes there are few cases where this particular column gets updated and also inserted (assuming both this operation will now take lot of time to complete).

Thanks

Sam

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 8

sam 55243 (1/6/2017)
Hi,
I modified the query to use SQL count function instead of xml count as suggested by drew .With this change i got 25~30 percent improvement in comparison to existing code.
I didn't go with xml indexes there are few cases where this particular column gets updated and also inserted (assuming both this operation will now take lot of time to complete).
Thanks
Sam

Try with the xml indexes, they could improve it much.

--create Primary XML index

CREATE PRIMARY XML INDEX [PXML_name]

ON YourTable (YourXMLColumn);

--create XML index that is using the PXML index

CREATE XML INDEX [IXML_name_]

ON YourTable (YourXMLColumn)

USING XML INDEX [PXML_name] FOR PATH ;

Igor Micev,My blog: www.igormicev.com