Which is faster .exist or .value in a where clause?

Question

I am doing some crude benchmarks with the xml datatype of SQL Server 2008. I've seen many places where .exist is used in where clauses. I recently compared two queries though 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, with no indexes on anything but the primary key(testxmlrid)

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

This query on the otherhand takes about .75 seconds to run.

I'm using untyped XML and my benchmarking is taking place on a SQL Server 2008 Express instance. There are about 15,000 rows in the dataset and each XML string is about 25 lines long.

Are these results I'm getting correct? If so, why does everyone use .exist? Am I doing something wrong and .exist could be faster?

Mikael Eriksson · Accepted Answer · 2011-05-27T13:59:17.147

You are not counting the same things. Your .exist query (form/fields/field)[@id="1"] checks all occurrences of @id in the XML until it finds one with the value 1 and your .value query (/form/fields/field/@id)[1] only fetches the first occurrence of @id.

Test 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 query count is 1 because it finds the @id=1in the second field node and the .value query count is 0 because it only checks the value for the first occurrence of @id.

An .exist query that only checks the value for the first occurrence of @id like your .value query would look like this.

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

Does `exist()` not short-circuit as soon as it finds the first occurrence anyway? — Yuck, May 27 '11 at 12:10
@Yuck - Yes it does but in this case the `.exist` query is searching for the presence of `@id=1`. If the first occurrence if `@id` is something else than 1 it continues to search. The `.value` query looks at the value for the first occurrence of `@id` and does not continue searching if that values is not 1. — Mikael Eriksson, May 27 '11 at 13:30

score 0 · Answer 2 · answered May 27 '11 at 06:36

The difference could come from your indexes.

A PATH index will boost performance of the exist() predicate on the WHERE clause, whereas a PROPERTY index will boost performance of the value() function.

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

Which is faster .exist or .value in a where clause?

2 Answers2