aktikt (4/13/2009)

---

Say it ain't so. This is the first I've heard a set based solution losing to RBAR.

I don't have the desire to check right now, but what about using a CTE instead for the tally table?

aktikt

aktikt (4/13/2009)

---

Say it ain't so. This is the first I've heard a set based solution losing to RBAR.

I don't have the desire to check right now, but what about using a CTE instead for the tally table?

aktikt

Jeff Moden (4/13/2009)

---

I'm thinking that the hey-day of the Tally table have finally been replaced by faster methods.

Florian Reischl (4/13/2009)

---

Don't say that. There might be actually two business cases where the tally table looses but there are many more things to do with than only some string manipulations.

Tally for ever!

Jeff Moden (4/13/2009)

---

Thanks for the vote of confidence... Like you said, two business cases aren't necessarily a killer, but they do indicate serious potential can be had... to wit, I've got a whole lot of testing to do across a whole lot of scenarios. As Phil mentioned, that would also include some testing on things not having to do with a While Loop.

You've done a couple of very cool tricks in the While Loop that most have not... like calculating the CHARINDEX only once instead of twice like a good number of folks end up doing. Others have tried methods similar to yours but have failed because of the doubled CHARINDEX and a couple of other nuances that you've very cleverly avoided. It's brilliant code in its simplicity and my hat's off to you.

You should write an article about this, Flo... this is good stuff.

Bruce W Cassidy (4/13/2009)

---

[font="Verdana"]Flo, I'll add my kudos as well: it's good to see some numbers showing how CLR rates in comparison with T-SQL. And I'll look forward to the article.

BTW, in your while loop:

WHILE EXISTS (SELECT TOP(1) 1 FROM @procs)
BEGIN
   SELECT TOP(1) @name = name, @definition = definition FROM @procs

   [...]
      
END

Did you try this approach:

while (1=1) begin
    select top (1) @name = name, @definition = definition from @procs (order by?)
    if @@rowcount = 0 break;

    [...]
end; -- while

In the times where I resort to a while loop (er, trying to do it less, honest Jeff!) I often find myself using that format, because otherwise I end up writing more code and making SQL Server do more work. So taking out the additional select may make your while loop a little faster. Just curious as to whether it changes the numbers a little.

P.S. Phil Factor's blog post makes interesting reading, particularly for this little gem:

RBarryYoung (4/13/2009)

---

1) For splitting the lines in stored procedure definitions, there is a subtle bug that exists in almost every line-splitting routine, including the ones so far in this thread: there is actually two different kinds of line-breaks incorporated in the system stored procedures: CR-LF, and LF alone. To correctly split them, you must take both into account (which is not trivial).

RBarryYoung (4/13/2009)

---

3) The final observation that I had at that time was that the loop-based routines start to catch up with the tally-based methods if the average distance between each separator gets large enough, and for line-splitting system procedures, they tend to be quite large of course (as opposed to comma-separated strings, for instance). This is because the loop-based methods can skip the ahead of "dead" characters in-between with CHARINDEX(). Because Florian figured out how to call CHARINDEX only once per loop, that lowered where that threshold is.

Paul White

---

The tally or numbers table solution is, to an extent, a brute-force approach. In the case of many long strings where the frequency of the searched-for string is low, a solution based on a loop/CHARINDEX approach should out-perform the tally approach.

RBarryYoung (4/13/2009)

---

So anyway, I wanted to test my line splitting functions against these also, so here are the results:

Flo

---

Start clr xml solution
Milliseconds: 790 | Lines: 28545
Start clr tvf (PW) solution
Milliseconds: 1083 | Lines: 25661