t-sqlsql-server-2008sqlxml

Reducing execution time of join to SQLXML table


I have a large T-SQL stored procedure which contains 3 joins to SELECT statements which query XML values passed in as parameters.

The parameters look like this:

@Code xml = NULL

The joins look like this:

    AND (@Code IS NULL OR (t.Code IN (
        select Tbl.Col.value('.[1]', 'nvarchar(2)')
        from @Code.nodes('//codes/code') Tbl(Col)
    )))

An example of a parameter value is:

'<codes><code>GB</code></codes>'

The could be as many as 100 <code>...</code> elements within these blocks.

Every element displayed on the execution plan shows Cost: 0 % except for these joins, which show Cost: 49 %.

Is there anything I can do to improve execution time?


Solution

  • Generally, // descendant axis is slow, try to use the normal / child axis.

    Also, using . to retrieve the node value is also slow, better to use text()

    You could do one of the following syntaxes, which are likely to be faster than what you have

        AND (@Code IS NULL OR
            @Code.exist('/codes/code[text() = sql:column("t.Code")]')) = 1
        )
    
    -- or better
    
        AND ISNULL(@Code.exist('/codes/code[text() = sql:column("t.Code")]'), 1) = 1
    
        AND (@Code IS NULL OR (t.Code IN (
            select Tbl.Col.value('text()[1]', 'nvarchar(2)')
            from @Code.nodes('/codes/code') Tbl(Col)
        )))