mister.magoo (12/22/2012)
Jeff Moden (12/21/2012)
Steven Willis (12/21/2012)
First test used the same data as generated in a post above using a Tally table CTE. I generated test data with 100 rows, 1,000 rows, and 10,000 rows.There is a major fault with this testing. It's a well advertised fact that the DelimitedSplit8K method doesn't work well with blobs. In fact, any method that internally joins to a blob at the character level is guaranteed to run much slower than other methods such a XML. That fact is even stated in the "Tally OH!" ariticle.
Methods like XML, however, are (with the understanding that I've not had the time to do the testing the code on this thread deservers) typically much slower than the DelimitedSplit8K method on datatypes of VARCHAR(8000) or less because of the required concatenations.
Just to be clear, I am not suggesting that the XML method is in any way a replacement for the DelimitedSplit8K function, which is blinding fast.
I am suggesting that it may be useful in this particular exercise because the length of the input may exceed 8000.
Not to worry, ol' friend. I absolutely understood that from the begining. That's why I said "N-i-i-i-i-c-c-c-c-e-e-e!" about your code post previously. It was "Thinking out of the box" at its best.
I just want everyone to know that the DelimitedSplit8K fuction has the term "8K" in it for a bloody good reason. It's absoluetly not meant to split blobs and, if modified to do so, is going to lose just about any race in a terrible fashion as would any code that joins to a blob at the character level.
--Jeff Moden
Change is inevitable... Change for the better is not.