Simple XML exist performance enhancement

By Art on October 13, 2011

Tags: Performance Xml

I came across this really simple best practice from BOL for the xml method exist which when used will give you a much cleaner and faster query plan. If like me you commonly use the name of an element or attribute as part of an XQuery predicate, then you would benefit immediately from this.

So lets create a table with a single blob of xml and try out two queries to show this in action. The full script can be downloaded from here

Setup

--Create example table
    CREATE TABLE XmlTable
    (
        Id INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
        MyXml XML NOT NULL
    )

    --Populate table with an xml blob 
    DECLARE @XmlData XML

    SET @XmlData = (SELECT *
                    FROM sys.tables
                    FOR XML PATH('Table'), ROOT('Tables'))

    INSERT INTO XmlTable (MyXml) 
    VALUES (@XmlData)
    GO

Query 1: Using the name of an element as part of the predicate

SELECT * 
    FROM XmlTable 
    WHERE MyXml.exist('/Tables/Table[name = "XmlTable"]') = 1
    GO

Returns a single record and the execution plan looks like this

Query 2: Using a dot as part of the predicate

SELECT * 
    FROM Xmltable 
    WHERE MyXml.exist('/Tables/Table/name[. = "XmlTable"]') = 1
    GO

Also returns a single record but the execution plan is simpler and looks like this

Here is the record that is returned in both queries

As you can see, both queries return the exact same result (single record) because they are identical queries. However because of the way the first query has to work, it incurs this additional evaluation step which is reflected in the plan. The BOL explanation for this is:

Although the result is the same the first form usually requires one more evaluation step. This is because the query processor is evaluating only one node in the second form (using the PATH index if its present) and is using two evaluation steps (one for /Tables/Table, one for /Tables/Table/name) in the first form. Although looking at the plan for two "equivalent" queries might seem strange for XML aficionados, SQL query tuners have been doing this for years. Note that the two queries above only produce the same results when using the XML data type exist method, they produce different results when used with the query method.

Enjoy!