1

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
Improve this question

1 Answer 1

Reset to default
5

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

Improve this answer
4
  • 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
    Commented Jun 24, 2013 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
    Commented Jun 24, 2013 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
    Commented Jun 24, 2013 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
    Commented Jun 24, 2013 at 8:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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