Very interesting post from Paul regarding the Spooling and Trace 8690... If I turn that trace flag on in SQL2008, the performance improves spectacularly.

(Also, just as a note, my testing...

Okay, as requested, I'm attaching my results... 2 sets... one for SQL2005 (32-bit) and one for SQL2008 (64-bit).

I used a Laptop... Windows 7 Professional, 4-processor Intel Core i5 CPU M430...

Jeff:

Did you read my blog post about XML that I mentioned earlier?:

http://bradsruminations.blogspot.com/2010/01/delimited-string-tennis-again-final.html

I had the same loooonnnnng performance issues as you did until someone suggested I add a .query('.')...

Jeff:

Using the Numbers table approach (using the code I had in my blog) with the procedure in cache results in the following:

CPU = 6486

Reads = 244155

Duration = 1887ms (avg)

The Numbers...

Jeff,

Here's the key to the mystery as to why you and I get differing results.

All of my tests included compile time also, because I did a DBCC FREEPROCCACHE and DBCC...

Sorry, Jeff, I had a deadline yesterday that kept me busy.

I'll re-do my tests and see what I come up with.

--Brad

Hi Jeff...

Just wanted to quickly respond... I've been busy all day (mostly writing my T-SQL Tuesday Blog Post).

Regarding the plethora of CROSS APPLYs in my code for the Tally Table:...

Check out this white paper on Query Recompilation... it might answer some of what you're looking for:

http://technet.microsoft.com/en-us/library/cc966425.aspx

Note about halfway thru the paper there is a flowchart that illustrates...

The CWT_PrimaryKey clustered index is a temporary table created by the processing of the CURSOR.

When you OPENed the cursor, the system created a temp table (inaccessible by anything else) and...

Wow! Excellent stuff, Jacob.