CODEWITHSUNDEEP
sql-servert-sqlxquerysqlxmlxquery-sql
Hemus San
Hemus San

Reputation: 399

SQL Server : optimize TSQL query with XQuery

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);

Upvotes: 0

Views: 350

Answers (2)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

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.

Upvotes: 1

marc_s
marc_s

Reputation: 754488

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?

Upvotes: 3

Related Questions