How to speed up XQuery-modify?

Question

I am on SQL Server 2012/SQL Server 2016.

The Xquery function 'modify' does only change one row at once. So I loop through my XML-data, element-by-element. This is quite slow. When there is only one distinct value to change I would cast the XML and replace the value. But there are multiple distinct values in most of the cases (rows).

I hope the structure of my_xml becomes clear from the (stronly simplified) data: There are multiple labels within a group of element (a and b).

--My data

if object_id ('tempdb..#t1') is not null drop table #t1

create table #t1 (id int not null identity (1,1) unique clustered, my_xml xml)
insert #t1 (my_xml) values 
     ('<root><a label=''name A'' /> <b /> </root>'),        --A
     ('<root><a label=''name A'' /> <b label=''name B'' /> </root>'),   --AB
     ('<root><a label=''name A'' /> <b label=''name B'' /> <a label=''name A'' /> <b label=''name B'' /><a label=''name A'' /> <b label=''name B'' /></root>'), --ABABAB
     ('<root><a label=''name B'' /> <b label=''name A'' /> <a label=''name B'' /> <a label=''name B'' /> <a label=''name B'' /> </root>') --BABBB

select * from #t1

I would like to change the value of the label to the SHA-Hash of the original value.

I query some information about the structure of my_xml to minimize the rows to modify:

--My helper-table    

if object_id ('tempdb..#t2') is not null drop table #t2

select 
     id, 
     max(my_xml.value('count(/root/*[self::a or self::b]/@label)', 'int')) max_label,
     max(my_xml.value('count(distinct-values(/root/*[self::a or self::b]/@label))', 'int')) max_label_distinct
into #t2
from #t1
group by id

select * from #t2

Then I modify XML (take only those rows with more than one distinct label as only with them I need to loop through the XML)

 --My query:

 set nocount on
 declare 
     @label         char(64), 
     @id                int,
     @n_max_label   smallint

 declare test cursor for 

     select 
         id, 
         max_label 
     from #t2
     where max_label_distinct > 1

 open test
 fetch next from test into @id, @n_max_label
 while (@@FETCH_STATUS <> -1)
 begin

     while @n_max_label > 0 
     begin

         select @label = isnull(convert(char(64), HASHBYTES('SHA2_256', my_xml.value('((/root/*[self::a or self::b]/@label)[sql:variable("@n_max_label")])[1]', 'char(64)')), 1), 'x') 
         from #t1
         where id = @id

         update #t1
         set my_xml.modify('replace value of ((/root/*[self::a or self::b]/@label)[sql:variable("@n_max_label")])[1] with sql:variable("@label")')
         where id = @id

         set @n_max_label = @n_max_label - 1

     end

     fetch next from test into @id, @n_max_label
 end
 close test
 deallocate test

final result:

 select * from #t1

I would like to improve the solution by the factor of 10 to 100 (in terms of speed) as by now I would need about 2 month to calculate the result - what is to long to manipulate about 600 GB of data. (unloading and using other tools is no option)

Answer 1

As you've found out .modify() will change one single value at a time. This is slow and needs procedural (CURSOR) logic.

You did not provide a closer look onto your original XML so this might point to the wrong direction, but you might try this:

Your new helper table

WITH cte AS
(
    select 
          id 
         ,A.AllLabels.value('local-name(..)','nvarchar(max)') AS ElementName
         ,A.AllLabels.value('local-name(.)','nvarchar(max)') AS LabelName
         ,A.AllLabels.value('.','nvarchar(max)') AS LabelValue
         ,HASHBYTES('SHA2_256', A.AllLabels.value('.','nvarchar(max)')) AS LabelHash
    from #t1
     --use "/root/" instead of "//" if always below <root>
    cross apply my_xml.nodes('//*[local-name()="a" or local-name()="b"]/@label') AS A(AllLabels)
)
SELECT id
      ,ElementName
      ,LabelName
      ,LabelValue
      ,LabelHash
      ,(SELECT LabelHash FOR XML PATH(''),TYPE).value('.','nvarchar(max)') AS HashBase64
      ,CONCAT(' ',LabelName,'="',LabelValue,'"' ) AS ReplaceThis
      ,CONCAT(' ',LabelName,'="',(SELECT LabelHash FOR XML PATH(''),TYPE).value('.','nvarchar(max)'),'"' ) AS WithThat
INTO #t2
FROM cte
GROUP BY id,ElementName,LabelName,LabelValue,LabelHash;

SELECT * FROM #t2;

The result

+----+-------------+-----------+------------+--------------------------------------------------------------------+----------------------------------------------+----------------+------------------------------------------------------+
| id | ElementName | LabelName | LabelValue | LabelHash                                                          | HashBase64                                   | ReplaceThis    | WithThat                                             |
+----+-------------+-----------+------------+--------------------------------------------------------------------+----------------------------------------------+----------------+------------------------------------------------------+
| 1  | a           | label     | name A     | 0x6F5A56BD0B5E367787D4D3F798F5467C17FB798C0849ECF88373A7346EB405CB | b1pWvQteNneH1NP3mPVGfBf7eYwISez4g3OnNG60Bcs= | label="name A" | label="b1pWvQteNneH1NP3mPVGfBf7eYwISez4g3OnNG60Bcs=" |
+----+-------------+-----------+------------+--------------------------------------------------------------------+----------------------------------------------+----------------+------------------------------------------------------+
| 2  | a           | label     | name A     | 0x6F5A56BD0B5E367787D4D3F798F5467C17FB798C0849ECF88373A7346EB405CB | b1pWvQteNneH1NP3mPVGfBf7eYwISez4g3OnNG60Bcs= | label="name A" | label="b1pWvQteNneH1NP3mPVGfBf7eYwISez4g3OnNG60Bcs=" |
+----+-------------+-----------+------------+--------------------------------------------------------------------+----------------------------------------------+----------------+------------------------------------------------------+
| 3  | a           | label     | name A     | 0x6F5A56BD0B5E367787D4D3F798F5467C17FB798C0849ECF88373A7346EB405CB | b1pWvQteNneH1NP3mPVGfBf7eYwISez4g3OnNG60Bcs= | label="name A" | label="b1pWvQteNneH1NP3mPVGfBf7eYwISez4g3OnNG60Bcs=" |
+----+-------------+-----------+------------+--------------------------------------------------------------------+----------------------------------------------+----------------+------------------------------------------------------+
| 4  | b           | label     | name A     | 0x6F5A56BD0B5E367787D4D3F798F5467C17FB798C0849ECF88373A7346EB405CB | b1pWvQteNneH1NP3mPVGfBf7eYwISez4g3OnNG60Bcs= | label="name A" | label="b1pWvQteNneH1NP3mPVGfBf7eYwISez4g3OnNG60Bcs=" |
+----+-------------+-----------+------------+--------------------------------------------------------------------+----------------------------------------------+----------------+------------------------------------------------------+
| 2  | b           | label     | name B     | 0xC8144356A508FA516CC263E0839B7D22811A6F8333851F5319E778D94FE5B0C3 | yBRDVqUI+lFswmPgg5t9IoEab4MzhR9TGed42U/lsMM= | label="name B" | label="yBRDVqUI+lFswmPgg5t9IoEab4MzhR9TGed42U/lsMM=" |
+----+-------------+-----------+------------+--------------------------------------------------------------------+----------------------------------------------+----------------+------------------------------------------------------+
| 3  | b           | label     | name B     | 0xC8144356A508FA516CC263E0839B7D22811A6F8333851F5319E778D94FE5B0C3 | yBRDVqUI+lFswmPgg5t9IoEab4MzhR9TGed42U/lsMM= | label="name B" | label="yBRDVqUI+lFswmPgg5t9IoEab4MzhR9TGed42U/lsMM=" |
+----+-------------+-----------+------------+--------------------------------------------------------------------+----------------------------------------------+----------------+------------------------------------------------------+
| 4  | a           | label     | name B     | 0xC8144356A508FA516CC263E0839B7D22811A6F8333851F5319E778D94FE5B0C3 | yBRDVqUI+lFswmPgg5t9IoEab4MzhR9TGed42U/lsMM= | label="name B" | label="yBRDVqUI+lFswmPgg5t9IoEab4MzhR9TGed42U/lsMM=" |
+----+-------------+-----------+------------+--------------------------------------------------------------------+----------------------------------------------+----------------+------------------------------------------------------+

You can either use this to try a REPLACE on string base using the columns ReplaceThis and WithThat or you can use .modify within a CURSOR

Some things to keep in mind:

• Within XML a binary value should be Base64 rather than a hex-string (normally)
• You can do the multi replace in one single go using a quirky update UDF (like provided here, modify it to your needs)
• If you do not have to look at the element containing your @label you can omit this column in your helper table to reduce the rows.
• With thousands of data I'd suggest to use indexes on your helper table
• You must be aware, that white-space can blow this up (label = "blah" is not the same as label="blah" on string level)
• Same with single vs. double quotes
• If you go with .modify() you can build a statement within your CURSOR dynamically and use EXEC(@YourStatement), but I doubt this will be fast...