CODEWITHSUNDEEP
xmlt-sqlxpathsql-server-2012xquery
gotqn
gotqn

Reputation: 43636

Optimize deletion of XML elements without children

I have a large XML value stored in variable. I am first deleting all condition elements with attribute @Suspended set to 1:

 SET @A.modify('delete /definitions/definition/conditions/group/condition[@Suspended=1]')

Then I want to delete all group elements which have not got any condition elements left (no children elements are left after the previous deletion). I have manage to do this using the following statement:

SET @A.modify('delete for $Node in /definitions/definition/conditions/group 
                                where not ( ($Node/condition)[1] instance of element() )
                                return $Node
                             ')

The first deletion is pretty fast - 0 seconds. The second deletion execution time is about 8 seconds.

I am looking for a better/faster way to perform the second deletion if possible.

The following is sample XML structure:

<definitions>
  <definition>
    <conditions>
      <group name="Group 1">
        <condition name="condition" type="answerisin" objType="question" objID="213219" Suspended="1">
          <value name="parameter">12</value>
        </condition>
        <condition name="condition" type="answerisin" objType="question" objID="113219" Suspended="1">
            <value name="parameter">4</value>
        </condition>
      </group>
    </conditions>
  </definition>
  <definition>
    <conditions>
      <group name="Group 1">
        <condition name="condition" type="answerisin" objType="question" objID="213219" Suspended="1">
          <value name="parameter">34</value>
        </condition>
      </group>
      <group name="Group 2">
        <condition name="condition" type="answerisin" objType="question" objID="213219">
          <value name="parameter">12</value>
        </condition>
      </group>
    </conditions>
  </definition>
</definitions>

Upvotes: 2

Views: 203

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

You could rewrite to this:

set @A.modify('delete /definitions/definition/conditions/group[empty(condition)]');

But I doubt it will be any faster. The query plan is somewhat different but much the same in the parts that matter for performance.

Another option that might work for you is to shred the XML to a table variable on definitions/definition, modify the XML in the table variable and then recreate the XML using for xml path.

declare @T table(X xml);

insert into @T(X)
select T.X.query('.')
from @A.nodes('/definitions/definition') as T(X);

update @T
set X.modify('delete /definition/conditions/group[empty(condition)]');

set @A = (
         select T.X as '*'
         from @T as T
         for xml path(''), root('definitions')
         );

Shredding the XML and modifying in small chunks was in my tests 10x faster than modifying a big XML. But that depends of course on what your actual XML looks like and if shredding on definitions/definition is fine grained enough.

Upvotes: 2

Related Questions