Using XML in SQL — performance

Question

First of all, I am using untyped XML. Secondly, I am using SQL Server.

Recently I got familiar with XML in SQL and have one question: which of presented approaches is better in terms of performance (in this example the difference would negligible, but I'm asking generally)?

    declare @t table(c1 int, c2 int, c3 int)

    declare @xml xml
    set @xml = '<matrix>
                    <row>
                        <col>1</col>
                        <col>2</col>
                        <col>3</col>
                    </row>
                    <row>
                        <col>4</col>
                        <col>5</col>
                    <col>6</col>
                </row>
                <row>
                    <col>7</col>
                    <col>8</col>
                    <col>9</col>
                </row>
            </matrix>'

-- FIRST APPROACH

insert into @t values
(@xml.value('(/matrix[1]/row[1]/col)[1]','int'), @xml.value('(/matrix[1]/row[2]/col)[1]','int'),@xml.value('(/matrix[1]/row[3]/col)[1]','int')),
(@xml.value('(/matrix[1]/row[1]/col)[2]','int'),@xml.value('(/matrix[1]/row[2]/col)[2]','int'), @xml.value('(/matrix[1]/row[3]/col)[2]','int')),
(@xml.value('(/matrix[1]/row[1]/col)[3]','int'), @xml.value('(/matrix[1]/row[2]/col)[3]','int'), @xml.value('(/matrix[1]/row[3]/col)[3]','int'))

select * from @t
delete from @t

-- SECOND APPROACH

insert into @t (c1,c2,c3)
select c.value('(./row[1]/col)[1]','int'), c.value('(./row[2]/col)[1]','int'), c.value('(./row[3]/col)[1]','int')
from @xml.nodes('/matrix') as T(c)

insert into @t (c1,c2,c3)
select c.value('(./row[1]/col)[2]','int'), c.value('(./row[2]/col)[2]','int'), c.value('(./row[3]/col)[2]','int')
from @xml.nodes('/matrix') as T(c)

insert into @t (c1,c2,c3)
select c.value('(./row[1]/col)[3]','int'), c.value('(./row[2]/col)[3]','int'), c.value('(./row[3]/col)[3]','int')
from @xml.nodes('/matrix') as T(c)

select * from @t

[Obligatory Eric Lippert rant](https://ericlippert.com/2012/12/17/performance-rant/). It's like you're asking us to set up a benchmark for you. — Jeroen Mostert, May 10 '17 at 08:18
No. I just thought that somebody already had gone through similar problem. — Michał Turczyn, May 10 '17 at 08:23
The problem with performance questions is pinning down what a "similar problem" is. How deep is the XML to be nested? How many rows? How many columns? Does the right approach vary based on the size of the XML? The hardware? The version of SQL Server? Maybe none of that matters. Maybe some of it does. You won't know until you test. A proper quality answer that covers this question "generally" (beyond someone just saying "X is faster" with no justification) is not easy to come by, compared to just optimizing a particular case. — Jeroen Mostert, May 10 '17 at 08:38

score 0 · Answer 1 · answered May 10 '17 at 08:42

0

By asking help from Display the Estimated Execution Plan :-

First approach:-

The difference part of its execution plan:

-

Second approach:-

The difference part of its execution plan:

So, the First approach is better than first one in performance.

answered May 10 '17 at 08:42

ahmed abdelqader

3,409
17
36

This answer is exactly what I meant with my comment. The *estimated* execution plan is just that -- an *estimate*. And even for *actual* execution plans, the percentage cost indicators are awfully unreliable. This is at best a first step towards measuring. – Jeroen Mostert May 10 '17 at 08:47

score 0 · Accepted Answer · answered May 10 '17 at 09:27

So i have done benchmark on 25x25 matrix (it took lot of programming to get the query as text because of its complexity). Second approach it's 2 times faster.

So conclusion (which I wanted) is: it's better to use XQuery methods, when possible. Even when the difference between two approaches is very little (like in example I provided).

Using XML in SQL — performance

2 Answers2