XML, XQuery & Performance Issues

In this article, we will discuss some pitfalls related to XML and XQuery that may lead to performance issues.

The following code will generate a test XML file:

To enable xp_cmdshell, execute the following code:

Eventually, we will have a XML file with the following structure:

Let’s do some tests. What is the most efficient way to download from XML file? IMHO, we can use OPENROWSET:

There is a subtle aspect. The combination of the loading and parsing operations decreases performance. For example, we need to get obj_id values from our test XML file:

The query takes too much time on my PC:

Now let’s perform loading and parsing separately:

The results are much better in this case:

What was the problem? To answer this question, we need to compare the following execution plans:

compare_execution_plans

As it turned out, the problem lies in the conversion of types. Thus, you need to pass a parameter to the nodes function in the XML type.

Consider a situation when we need to filter results while parsing. Remember that SQL Server does not optimize function calls to work with XML.

To demonstrate this, I execute the following query. The value function will be executed twice.

compare_execution_plans2

This nuance may reduce performance, so it is recommended to reduce the function calls:

compare_execution_plans3

Alternatively, we can filter the following way:

There is no significant benefit. Although QueryCost says it all.

compare_execution_plans4

As we can see, the third variant is the most effective… This is an additional argument to use QueryCost that is only internal assessment.

There is one important issue while parsing XML. Execute the following query:

And analyze the execution time:

Why it happens? SQL Server has some problems with reading the parent nodes from child nodes.

compare_execution_plans_resultset

In this case we need to start reading from the parent nodes and move to child nodes with help of CROSS/OUTER APPLY:

Another interesting situation is when we need to look at two levels above. I didn’t get any problems in this case:

Additionally, OPENXML doesn’t have any problems with reading parent elements:

You don’t have to think that OPENXML has clear advantages over XQuery. There are enough issues with OPENXML. For instance, if you forget to call sp_xml_removedocument, it may result in memory leaks.

All tests were performed on SQL Server 2012 SP3 (11.00.6020).

Execution plans were taken with help of dbForge Studio.

Leave a Comment