Reducing execution time of join to SQLXML table

Question

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?

Answer 1

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