What is faster .exist or .value in the where clause?

Question

I am doing some rough tests with the SQL Server 2008 XML type. I have seen many places where .exist used in where clauses. I recently compared two queries and got odd results.

 select count(testxmlrid) from testxml where Attributes.exist('(form/fields/field)[@id="1"]')=1 

This query takes about 1.5 seconds to run, without indexes on anything but the primary key (testxmlrid)

 select count(testxmlrid) from testxml where Attributes.value('(/form/fields/field/@id)[1]','integer')=1 

This request, on the other hand, takes about 0.75 seconds to run.

I use untyped XML, and my benchmarking happens on an instance of SQL Server 2008 Express. There are about 15,000 rows in the dataset, and each XML row is about 25 rows long.

Are these results correct? If so, why is everyone using .exist ? Am I doing something wrong, can an .exist be faster?

Answer 1

You do not consider the same. Your request .exist (form/fields/field)[@id="1"] checks all occurrences of @id in XML until it finds it with a value of 1 , and your request .value (/form/fields/field/@id)[1] will retrieve the first occurrence of @id .

Check this:

 declare @T table ( testxmlrid int identity primary key, Attributes xml ) insert into @T values ('<form> <fields> <field id="2"/> <field id="1"/> </fields> </form>') select count(testxmlrid) from @T where Attributes.exist('(form/fields/field)[@id="1"]')=1 select count(testxmlrid) from @T where Attributes.value('(/form/fields/field/@id)[1]','integer')=1 

The .exist request .exist is 1 because it finds @id=1 in the second field node, and the number of .value requests is 0 because it only checks the value of the first occurrence of @id .

An .exist request that only checks the value of the first occurrence of @id , like your .value request, will look like this.

 select count(testxmlrid) from @T where Attributes.exist('(/form/fields/field/@id)[1][.="1"]')=1 

Answer 2

The difference can be obtained from your indices.

A PATH index will improve the performance of the exist() predicate in the WHERE , while a PROPERTY index will improve the performance of the value() function.

Read: http://msdn.microsoft.com/en-us/library/bb522562.aspx