Search code examples
sqlsql-serverxmlxqueryxquery-sql

Using XML in SQL — performance

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
Solution

  • 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).