XML parsing is slow and takes up 90% of processing time inside the stored procedure-CodePudding

I have a stored procedure that takes an xml file as one of the inputs. The XML file contains the region ids. I have to get all the region ids into a temp table and use it in the subsequent queries inside the stored procedure.

Here's the code that parses the xml inside the stored proc:

Declare @xml XML = '<regions>
<regionId>7C3BD242-D835-4748-96D7-9C91B6048549</regionId>
<regionId>25B7B2CF-4E0E-4873-B2FB-F3BFCFFB4228</regionId>
</regions>'

Select r.c.value('.','uniqueidentifier')
  Into #region
  From @xml.nodes('/regions/regionId/text()') r(c)

This was not performing well and was taking up almost 99% of the query execution time in the query plan. So we added a hint to optimize it.

Select r.c.value('.','uniqueidentifier')
  Into #region
  From @xml.nodes('/regions/regionId/text()') r(c)
 OPTION(OPTIMIZE FOR (@xml = null))

This improved the performance a bit and the query execution time for the xml came down to 85%. The SP executes in about 10 seconds out of which the XML processing takes about 5 seconds. What I find amusing is why would such a simple query take up so much query cost when the subsequent queries have tables that have millions of rows in them and don't take as much time.

I would really appreciate it if anyone has suggestions on how I can improve the performance.

Note: I tried running the SP with just the select statement for the XML parsing without the temp table and it still took about 85% of the query execution time. We're on SQL Server 2012.

CodePudding user response：

Often (in my experience at least) XML can be pretty slow.

As an alternative maybe consider treating the xml as just a string and apply string_split to extract the values - something like:

Declare @xml varchar(max) = '<regions>
<regionId>7C3BD242-D835-4748-96D7-9C91B6048549</regionId>
<regionId>25B7B2CF-4E0E-4873-B2FB-F3BFCFFB4228</regionId>
</regions>'

select [value] into #region
from (select Replace(Replace(Replace(Replace(Replace(@xml,Char(13) Char(10),''),'<regionId>',''),'<regions>',''),'</regions>',''),'</regionId>',','))x(x)
cross apply String_Split(x, ',')
where value !=''

CodePudding user response：

XML parsing is slow and takes up 90% of processing time inside the stored procedure

XML parsing can be problematic in SQL Server - especially when you get an execution plan that

Your methodology for gathering statement level execution durations is flawed. If you are running this in SSMS then the easiest way in SQL Server 2012 would probably be to run SET STATISTICS IO, TIME ON; first (you may find adding PRINT statements inside the proc useful to quickly associate the output with a statement).

You can also look in dm_exec_query_stats to get historic information about timings per statement as long as the execution plan is in the cache.