XQuery performance in SQL Server

Question

Why does this quite simple xquery takes 10min to execute in sql server (the 2mb xml document stored in one column) compared to 14 seconds when using oxygen/file based querying?

SELECT model.query('declare default element namespace "http://www.sbml.org/sbml/level2";
for $all_species in //species, $all_reactions in //reaction
where data($all_species/@compartment)="plasma_membrane" and $all_reactions/listOfReactants/speciesReference/@species=$all_species/@id
return <result>{data($all_species/@id)}</result>') from sbml;

Answer 1

Do you have a schema declaration for your XML? Did you place appropiate indexes on it?

• XML Best Practices
• Indexes on XML Data Type Columns
• CREATE XML INDEX (Transact-SQL)
• Adding Namespaces Using WITH XMLNAMESPACES
• CREATE XML SCHEMA COLLECTION (Transact-SQL)

Answer 2

"//" kills MSSql, use a so exact path as possible instead: http://scarydba.wordpress.com/2009/11/30/xquery-for-idiots/