Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two queries as shown below which do the same thing. Here xmlcolumn is a column with datatype XML. I use these queries to search for a string anywhere in the XML column.

I checked the execution plan of both these queries and find that the first query has a lower I/O cost and subtree cost than second one. I was expecting that the first one would have a higher cost as it uses cast and charindex, but that is not the case.

Why does it have a lower cost?

First query:

SELECT * 
FROM mytable
WHERE ( Charindex('abc',CAST([xmlcolumn] AS VARCHAR(MAX)))>0 ) 

Second query:

SELECT * 
FROM mytable t1
WHERE t1.[xmlcolumn].exist('//*/text()[contains(.,"abc")]')=1
share|improve this question

1 Answer 1

Your queries are not equivalent. The first will give you rows where both node names and values match where your second query only check the node values.

SQL Fiddle

The extra cost for the second query comes from using a table valued function to shred the XML.

First query:

enter image description here

Second query:

enter image description here

share|improve this answer
    
ok.So first query checks both node names and values where as second one checks only values, but still first one has less cost.(first one does more but has less cost). Is it due to second query using xquery? The first one has to convert whole xml to varchar and then search and still has less cost!! –  IT researcher Jun 24 '13 at 6:54
1  
@ITresearcher It does not make much sense comparing costs for queries that does not return the same result. But anyway, SQL Server query optimizer thinks that shredding the XML is more expensive than converting the XML to varchar(). –  Mikael Eriksson Jun 24 '13 at 7:27
    
I got it.But i want a query which is alternative to my second query in the question which has performance better than that.Should i post new question for this? or continue with this post itself? –  IT researcher Jun 24 '13 at 7:45
1  
@ITresearcher I think you should ask a new question that asks about performance. That is not necessarily the same as estimated query cost. You have full text index and XML index that could be used to speed things up but with a penalty elsewhere (storage size, performance of DML, complexity). For this query where you are scanning the entire table anyway I would try to combine your where clauses in one query. SQL Fiddle. Rows will first be filtered using like and the rows that is left will be shredded by the function. –  Mikael Eriksson Jun 24 '13 at 8:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.