1

I am stuck with a timeout exception in one of my ASP.NET applications and I can blame it upon a SQL Server stored procedure that is causing it to break.

When I saw the execution plan of the procedure, it turned out that almost 99% of the query execution cost is for XML Reader with XPath Filter. I have actually passed a parameter as CSV and in my procedure I break it with help of XML Reader to get my values. But it is not at all efficient for such a simple thing.

Here is my query:

Declare @IDs varchar(max)  = '1,2,7,234,74',
        @xml xml= ''

Set @xml = N'<root><r>' + replace(@IDs,',','</r><r>') + '</r></root>'

select 
    t.value('.','varchar(max)') as ID 
from 
    @xml.nodes('//root/r') as a(t)

Any alternative with minor changes available?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Cyberpks
  • 1,401
  • 6
  • 21
  • 51
  • Specifically I recommend this function for your situation: http://www.sommarskog.se/arrays-in-sql-2005.html#iter-list-of-integers, but there are a host of other approaches on that site, too. Even more answers are here: http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x – Tomalak Mar 19 '15 at 07:59

0 Answers0