Настройка запроса для анализа XML-данных в SQL Server 2014

У меня есть таблица в базе данных SQL Server 2014, в которой хранится информация аудита для изменений записей в столбце VARCHAR(MAX) (CDC для бедняков).

Эти данные имеют следующий формат:

<span class="fieldname">Assigned To</span>
   changed from <span class="oldvalue">user1</span>
   to <span class="newvalue">user2</span><br />
<span class="fieldname">Status</span>
   changed from <span class="oldvalue">QA</span>
   to <span class="newvalue">Development</span><br />
<span class="fieldname">Progress</span>
   changed from <span class="oldvalue">Yes</span>
   to <span class="newvalue">No</span><br />
...

Мне нужно проанализировать эту информацию, чтобы получить транспонированные данные, чтобы они выглядели так:

Record    FieldName      OldValue   NewValue
------    ---------      --------   --------
1234      Assigned To    user1      user2
1234      Status         QA         Development
1234      Progress       Yes        No

Хранимая процедура пытается сделать это путем преобразования данных в XML, а затем с помощью XPath извлекает необходимые фрагменты:

;WITH TT AS (
   SELECT TransId,
      CAST('<root><rec>' + REPLACE(REPLACE(TransDescription, 'Ticket reopened... Status', 'Status'), '<br />', '</rec><rec>') + '</rec></root>' AS XML) TransXml
   FROM dbo.Trans
   WHERE TransDate >= '11/1/2016'
      AND (TransDescription LIKE '%Ticket reopened... Status%' OR TransDescription LIKE '%Status%'))
SELECT TransId,
   TransXml,
   FieldName = T.V.value('span[@class="fieldname"][1]', 'varchar(255)'),
   OldValue = NULLIF(T.V.value('span[@class="oldvalue"][1]', 'varchar(255)'), 'nothing'),
   NewValue = NULLIF(T.V.value('span[@class="newvalue"][1]', 'varchar(255)'), 'nothing')
INTO #tmp
FROM TT
   CROSS APPLY TT.TransXml.nodes('root/rec') T(V);

Вот план выполнения: https://www.brentozar.com/pastetheplan/?id=rJF2GRB7g

Соответствующая статистика IO:

Table 'Trans'. Scan count 9, logical reads 27429, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 2964994, physical reads 0, read-ahead reads 0, lob logical reads 2991628, lob physical reads 0, lob read-ahead reads 0.

Этот запрос мучительно медленный (пример был для данных всего за 10 дней) и становится все медленнее с увеличением количества данных.

Каковы мои варианты настройки этого запроса?


sql-server xpath query-performance query-tuning
person Chahk    schedule 07.12.2016    source источник
comment
Вам нужен только TransDescription LIKE '%Status%', он включает набор LIKE '%Ticket reopened... Status%'. Не думайте, что это сильно повлияет на производительность.   -  person HoneyBadger    schedule 07.12.2016
comment
@HoneyBadger Да, начальный «%» делает это неуправляемым, поэтому производительность уже страдает из-за этого. Тем не менее, согласно статистике ввода-вывода, именно Worktable становится все хуже.   -  person Chahk    schedule 07.12.2016
comment
Что вам действительно нужно для ускорения работы, так это индексирование xml. Однако, поскольку вы создаете XML на лету, этого не происходит. По сути, это широкий эквивалент CROSS JOIN, который со временем будет экспоненциально замедляться.   -  person Laughing Vergil    schedule 13.12.2016
comment
См. stackoverflow.com/questions/24196516/ для подробного обсуждения и того, как помогает индексация. Если вы хотите сделать это с помощью XML, вам действительно нужно будет хранить XML, чтобы вы могли индексировать XML.   -  person Laughing Vergil    schedule 13.12.2016
comment
@LaughingVergil Сначала сбрасывание CTE во временную таблицу, а затем уничтожение XML действительно решает проблему. Благодарю вас! Можете ли вы опубликовать это как ответ, чтобы я мог его принять?   -  person Chahk    schedule 16.12.2016

Ответы (2)


arrow_upward
2
arrow_downward

Что вам действительно нужно для ускорения работы, так это индексирование xml. Однако, поскольку вы создаете XML на лету, этого не происходит. По сути, это широкий эквивалент CROSS JOIN, который со временем будет экспоненциально замедляться.

См. запрос перекрестного применения xml работает экспоненциально хуже, чем xml документ растет для подробного обсуждения и того, как помогает индексация. Если вы хотите сделать это с помощью XML, вам действительно нужно будет хранить XML, чтобы вы могли индексировать XML.

person Laughing Vergil    schedule 16.12.2016

arrow_upward
1
arrow_downward

CROSS JOIN - это одна из тех вещей, которые просто не очень хорошо масштабируются, поскольку ваши таблицы становятся больше, "количество выполнений" в ваших "вложенных циклах" растет в геометрической прогрессии. В плане выполнения, который вы представили, цифры превышали 0,6 миллиона на каждом цикле. Ваши логические чтения действительно низки, но страницы обрабатываются снова и снова. (Если размер вашего запроса когда-либо превысит размер вашего буфера и будет спулирован на диск, вы сильно пострадаете.)

Вот решение, которое позволяет вам использовать XML-индексы, это может помочь в вашей ситуации.

--PREPARE SAMPLE DATA
DROP TABLE #Trans
CREATE TABLE #Trans(TransID INT
                    ,TransDate DATE
                    ,TransDescription VARBINARY(MAX)
                    )
INSERT INTO #Trans VALUES
(
1, '20160101'
,CAST('<span class="fieldname">Assigned To</span>
   changed from <span class="oldvalue">user1</span>
   to <span class="newvalue">user2</span><br />
<span class="fieldname">Status</span>
   changed from <span class="oldvalue">QA</span>
   to <span class="newvalue">Development</span><br />
<span class="fieldname">Progress</span>
   changed from <span class="oldvalue">Yes</span>
   to <span class="newvalue">No</span><br />' AS varbinary(MAX)))
,(2, '20160101'
,CAST('<span class="fieldname">Assigned To</span>
   changed from <span class="oldvalue">user1</span>
   to <span class="newvalue">user2</span><br />
<span class="fieldname">Status</span>
   changed from <span class="oldvalue">QA</span>
   to <span class="newvalue">Development</span><br />
<span class="fieldname">Progress</span>
   changed from <span class="oldvalue">Yes</span>
   to <span class="newvalue">No</span><br />' AS varbinary(MAX)))
,(3, '20160101'
,CAST('<span class="fieldname">Assigned To</span>
   changed from <span class="oldvalue">user1</span>
   to <span class="newvalue">user2</span><br />
<span class="fieldname">Status</span>
   changed from <span class="oldvalue">QA</span>
   to <span class="newvalue">Development</span><br />
<span class="fieldname">Progress</span>
   changed from <span class="oldvalue">Yes</span>
   to <span class="newvalue">No</span><br />' AS varbinary(MAX)))

---------------------------------------------------------------------------------------------------
--RUN BELOW THIS LINE COLLECTIVELY, THE ORIGINAL QUERY IS SHOWING UP WITH APPROX 93% OR OVERALL COST

--BUILD A TEMP TABLE TO RECIEVE XML FORMATTED DATA
DROP TABLE #XmlData
CREATE TABLE #XmlData (
    TransId INT NOT NULL,
    TransXml xml NOT NULL,
CONSTRAINT [PK_XmlData] PRIMARY KEY CLUSTERED (TransId)
) 

--INSERT DATA INTO XML TABLE
INSERT INTO #XmlData
SELECT TransId,
    CAST('<root><rec>' + REPLACE(REPLACE(TransDescription, 'Ticket reopened... Status', 'Status'), '<br />', '</rec><rec>') + '</rec></root>' AS XML) TransXml
FROM #Trans
WHERE TransDate >= '11/1/2015'
    AND (TransDescription LIKE '%Ticket reopened... Status%' OR TransDescription LIKE '%Status%')

--CREATE AN XML INDEX
CREATE PRIMARY XML INDEX PXML_TransXml
ON #XmlData(TransXml)

--APPLY NODES QUERY AGAINST XML INDEX
SELECT TransId,
   TransXml,
   FieldName = T.V.value('span[@class="fieldname"][1]', 'varchar(255)'),
   OldValue = NULLIF(T.V.value('span[@class="oldvalue"][1]', 'varchar(255)'), 'nothing'),
   NewValue = NULLIF(T.V.value('span[@class="newvalue"][1]', 'varchar(255)'), 'nothing')
FROM #XmlData TT
   CROSS APPLY TT.TransXml.nodes('root/rec') T(V);

---------------------------------
--Original Query
;WITH TT AS (
   SELECT TransId,
      CAST('<root><rec>' + REPLACE(REPLACE(TransDescription, 'Ticket reopened... Status', 'Status'), '<br />', '</rec><rec>') + '</rec></root>' AS XML) TransXml
   FROM #Trans--dbo.Trans
   WHERE TransDate >= '11/1/2015'
      AND (TransDescription LIKE '%Ticket reopened... Status%' OR TransDescription LIKE '%Status%'))

SELECT TransId,
   TransXml,
   FieldName = T.V.value('span[@class="fieldname"][1]', 'varchar(255)'),
   OldValue = NULLIF(T.V.value('span[@class="oldvalue"][1]', 'varchar(255)'), 'nothing'),
   NewValue = NULLIF(T.V.value('span[@class="newvalue"][1]', 'varchar(255)'), 'nothing')
--INTO #tmp
FROM TT
   CROSS APPLY TT.TransXml.nodes('root/rec') T(V);
person pacreely    schedule 16.12.2016