XML performance tips

Parsing data from XML using XQuery is a routine practice. In order to do this most effectively, little effort is required.

Suppose we need to parse data from the disk file with the following structure:

Use BULK INSERT, if you need to read data from a file:

A sample xml file is here.

However, bear in mind one particular thing… Try not to read the data directly:

Assign data to a variable. This way you can get a more efficient execution plan:

Compare the results:

As you can see, the second option is substantially faster.

Another important feature of SQL Server when working with XQuery is that reading a parent element can result in poor performance. Consider the following example:

Let’s look at the actual number of rows received from the operator. The value is abnormally large:

Results

The request can be easily optimized using CROSS APPLY:

Let’s compare the execution time:

As you can see from the example, the request with CROSS APPLY works instantly.

Thanks for your attention. I hope this article was useful. Feel free to ask any questions, leave your comments and suggestions concerning this article.

codingsight

codingsight

A community platform for IT specialists
codingsight

Latest posts by codingsight (see all)

codingsight

A community platform for IT specialists