SQL Server : optimize TSQL query with XQuery

Question

In SQL Server 2012 I have a table with 1 row which contains a XML column - XMLdata. Size of a XML is around 10 MB. I wrote a query but execution takes around 1 hour. Are there any options how to rewrite query that it will work faster?

Structure of XML:

<Settings>
    <Group Name="A">
        <Group Name="AA">
            <Group Name="AAA">
                <Parameter Name="aaa">
                    <Value>test1</Value>
                    <Items>
                            <Item Index="0" Name="A"/>
                            <Item Index="1" Name="B"/>
                    </Items>
                </Parameter>
            </Group>
        </Group>
    </Group>
</Settings>

Query:

SELECT
    A.B.value('../../../../../@Name', 'nvarchar(100)') + '/' + A.B.value('../../../../@Name', 'nvarchar(100)') + '/' + A.B.value('../../../@Name', 'nvarchar(100)') AS BlockPath
    , A.B.value('../../@Name', 'nvarchar(100)') AS ParameterName
    , A.B.value('./@Index', 'nvarchar(100)') AS ItemIndex
    , A.B.value('./@Name', 'nvarchar(100)') AS ItemName
FROM 
    [table]
CROSS APPLY 
    XMLdata.nodes('//Item') AS A(B);

Did you try looking at xml indexes? https://msdn.microsoft.com/en-us/library/bb934097.aspx — Evaldas Buinauskas, Sep 18 '15 at 08:40
Thank you for your suggestion. I rapidly increased the performance. — Hemus San, Sep 18 '15 at 10:23

score 3 · Answer 1 · answered Sep 18 '15 at 09:10

3

Try to use more focused XPath rather than the notoriously inefficient //Items approach.

I tried this and get the same results, and a better performance:

SELECT
    BlockPath = XC.value('../../../@Name', 'nvarchar(100)') + '/' + XC.value('../../@Name', 'nvarchar(100)') + '/' + XC.value('../@Name', 'nvarchar(100)'),
    ParameterName = XC.value('@Name', 'varchar(100)'),
    ItemIndex = XCItem.value('@Index', 'int'),
    ItemName = XCItem.value('@Name', 'varchar(100)')
FROM 
    [table]
CROSS APPLY 
    XMLdata.nodes('/Settings/Group/Group/Group/Parameter') AS XT(XC)
CROSS APPLY 
    XC.nodes('Items/Item') AS XT2(XCItem);

The first CROSS APPLY gets the <Parameter> nodes - but with a direct XPath with any // inside it - and then a second CROSS APPLY gets the Items/Item nodes under each <Parameter> node.

Try this - how much improvement do you get?

answered Sep 18 '15 at 09:10

marc_s

732,580
175
1,330
1,459

I used `SELECT TOP 100` with my code and your code. My code took 78 seconds and your code 47 seconds. It's better but still to slow. – Hemus San Sep 18 '15 at 10:11
I added primary and secondary XML indexes and I rapidly increased the performance. `SELECT TOP 100` needs 3 seconds to execute. – Hemus San Sep 18 '15 at 10:22
@AljPra: OK, great to hear. My experience with XML indices was less than favorable - they did speed up XQuery, but they also bloated my database from roughly 1.5 GB in size to around 11 GB in size..... – marc_s Sep 18 '15 at 11:27

score 1 · Answer 2 · answered Sep 18 '15 at 10:28

1

As suggested in comments, it's a good idea to create XML Index on your column. You can also query your XML field as marc_s suggested. Combination of these two should give you a huge performance boost.

answered Sep 18 '15 at 10:28

Evaldas Buinauskas

13,739
11
55
107

SQL Server : optimize TSQL query with XQuery

2 Answers2