ariel_mlk (9/5/2012)
Sean,No offense taken at all ! right now the procedure that i copied here is the only one that uses it, i just did some copy paste of existing code ( yeah I was lazy ) as this is still not in production, but it will be changed, i'm just yet looking for where the rCTE IO comes from right now, or if I'm misinterpreting results.
I don't have the actual numbers right now but the rCTE does something like a hundred table scans on the #hierarchy table ! the like approach doesn't gets even near that and still is outperformed by the rCTE. Right now I'm more worried on understanding that behavior than using a tally for string split, which I *think* will cause minor improvement on this, or RBARs can really be THAT bad on, such small strings (200 is small isn't it) ?
thanks for the attention so far =)
Get rid of that RBAR function and then start your comparison. You are trying to read stats on a query that you know you need to drastically change. Change the query first and then look at what it is doing. I realize that with the code you posted it isn't doing anything but still...
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/