Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Rocks!

SQL Database Administrator/Developer. Background in developing OLTP/document based databases, SQLXML and performance tuning with an unhealthy fascination for the SQL query optimiser!

Simple XML exist performance enhancement

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!

Comments

Posted by Steve Jones on 17 October 2011

Interesting. Did you look at the cost of the plans, the reads, or any other metrics?

Posted by Arthur Olcot on 17 October 2011

Hi Steve, In hindsight yes I should have added some metrics to the blog article to make it more complete! Lesson learnt on good blogging for me there. I might do a quick follow up later in the week with the stats laid out properly. However to answer your question, the logical reads for both queries are identical which is good, but there is a difference with the parse/compile time. On my rig here (which isn't the best!) query 1 (name in predicate) averages at around 4ms and query 2 (dot in predicate) averages 2ms. Looking at the execution plans in more detail, there is a quite a difference as well with the estimated sub-tree cost on the select operator in both plans which is quite interesting, 1.09116 versus 0.128313.

Posted by Anonymous on 18 October 2011

Pingback from  Dew Drop – October 18, 2011 | Alvin Ashcraft's Morning Dew

Leave a Comment

Please register or log in to leave a comment.