Оптимизация обновлений XML - записей в SQL server

---

Я написал код T-SQL для обновления XML-записей в потенциально очень большой таблице (скажем, миллионы строк) с каждой строкой, имеющей столбец XML. Код изменяет значения всех узлов в XML, которые соответствуют определяемому пользователем выражению XPath. Так, например, предположим, что XML-это:

<myrec>
    <amt>1</amt>
    <main>
        <amt bonus="11">2</amt>
        <supp>
            <amtort>referrer</amtort>
            <amt>3</amt>
            <rows>
                <row>
                    <amt bonuses="999">4</amt>
                </row>
                <row>
                    <amt>5</amt>
                </row>
                <row>
                    <someotherlevel>
                        <amt bonus="22">6</amt>
                        <keep>optional</keep>
                    </someotherlevel>
                </row>
            </rows>
        </supp>
    </main>
</myrec>

И предположим, что определяемый пользователем XPath-это //amt, тогда код (в качестве примера) добавит 100 к каждому элементу данных <amt>. Тогда XML станет:

<myrec>
   <amt>101</amt>
  <main>
    <amt bonus="11">102</amt>
    <supp>
      <amtort>referrer</amtort>
      <amt>103</amt>
      <rows>
        <row>
          <amt bonuses="999">104</amt>
        </row>
        <row>
          <amt>105</amt>
        </row>
        <row>
          <someotherlevel>
            <amt bonus="22">106</amt>
            <keep>optional</keep>
          </someotherlevel>
        </row>
      </rows>
    </supp>
  </main>
</myrec>

Это будет сделано для всех узлов <amt> В XML в соответствии с XPath //amt.

Обратите внимание, что код to работает для любого XML-документа и любого (введенного пользователем) XPath. Так что это код общего назначения.

Мой код работает, но у меня есть ощущение, что есть лучший способ сделать это, так как я не получаю той производительности, на которую надеялся (в настоящее время требуется около 266 секунд, чтобы обновить 1 миллион строк (один узел XML обновляется в строке) на экземпляре Azure B2s (2 VCPU, 4 ГБ оперативной памяти, SSD).

Код, который я написал, использует итерацию и функцию .modify() on и переменную типа XML. Я также прибегал к использованию динамического SQL, чтобы создавать произвольные строки XQuery (как уже упоминалось, это код общего назначения для любого XML / XPath).

Может ли кто-нибудь предложить лучший / более эффективный подход? Я новичок в XML в MSSQL, поэтому все еще чувствую свой путь через него.

Мой код находится ниже:

Что я уже пробовал:

CREATE PROCEDURE dbo.ModifyXML 
	@var_xpath VARCHAR(max), 
	@var_rec XML,
	@new_xml XML OUTPUT
AS
	SET NOCOUNT ON

	DECLARE @sql_matches NVARCHAR(max);
	DECLARE @sql_modify NVARCHAR(max);
	DECLARE @sql_get_new NVARCHAR(max);
	DECLARE @new_value INTEGER;
	DECLARE @i INTEGER;

	SET @sql_modify = N'';
	SET @new_xml = @var_rec
	SET @i = 1;

	-- Get original data value in the first node
	SET @sql_get_new = N'SET @new_value = dbo.my_transform(@xml.value(N''(' + @var_xpath + '/text())[1]'', ''INTEGER''));';
	EXECUTE sp_executesql @sql_get_new, N'@xml XML, @new_value INTEGER OUTPUT', @xml = @var_rec, @new_value = @new_value OUTPUT;

	WHILE @new_value is not NULL
	BEGIN
		-- Replace the original value with the new value in the XML.
		-- This is a dynamic SQL string and is appended to the previous one, ... to be executed once the loop exits
		SET @sql_modify = @sql_modify + N'SET @new_xml.modify(''replace value of (' + @var_xpath + '/text())[' + CAST(@i AS NVARCHAR(max)) +'] with "' + CAST(@new_value AS NVARCHAR(max)) + '"'');';

		-- Next match
		SET @i = @i + 1;

		-- Create masked value from original
		SET @sql_get_new = N'SET @new_value = dbo.my_transform(@xml.value(N''(' + @var_xpath + '/text())[' + CAST(@i AS NVARCHAR(max)) + ']'', ''INTEGER''));';
		EXECUTE sp_executesql @sql_get_new, N'@xml XML, @new_value INTEGER OUTPUT', @xml = @var_rec, @new_value = @new_value OUTPUT;
	END

	-- Execute the accumulated dynamic SQL modification queries
	IF @sql_modify <> '' EXECUTE sp_executesql @sql_modify, N'@new_xml XML OUTPUT', @new_xml = @new_xml OUTPUT;
GO

---