I'm looking at a stored procedure that takes some XML from a web app and creates a SQL search with it. There are 11 variables but the execution plan says that this one line takes 68% of the whole execution time. The 68% is the cost relative to the batch. There are other similar @input.value processes that register as 0%, though the execution plan for this is more complex.
Can anyone tell me why or suggest a more efficient way of writing it?
SET @Message=@Input.value('(/Input/Message)[1][not(@xsi:nil = "true")]', 'int')
Best Answer
The query cost calculated for a query is an estimate. Even in the actual execution plan it is the estimated cost that is shown.
For some reason SQL Server Management Studio uses those estimated costs and comes up with a cost percentage for each query in a batch.
Generally speaking that percentage has nothing to do with the actual performance of the query and should (in my opinion) not be used.
Estimates are based on statics of the involved tables and columns but XML variables (and columns) does not have any statistics that describe what shape for form they have so for XML queries the estimates are even less useful then for other types of queries.
Measure the actual performance instead like duration and IO.
Nothing comes to mind that would improve the performance of your query so until you actually see a performance issue with this query you could use it as is. Provided it does return what you want.