Jeff,

I am terribly sorry, but there is no way that your test reflects the picture. I ran Split8K and xml only tests and results I get are totally different. First, I would like to clarify that you did mean 25 seconds, not 25 minutes to process 10,000 records with 100 items in one record, right? The biggest problem with Split8KTally was pointed out a while ago by Lynn Pettis who correctly said that it becomes pretty slow when the datalength of the items in the record becomes close to 8K. This assessment is absolutely correct, yet you tested a rather small number of items to split in each record (4, 16 etc). When I ran your tests I saw that the smaller number of items to split, the better Split8KTally becomes. So, for 16 items per record I saw Split8kTally takes 1 second while xml takes 4. However, when I increased the records to hold 100 items each, I saw that Split8KTally takes 15 seconds while xml takes 20, still faster but not as much. When I increased the number to 1000 per record, which basically pushed the Split8K to about as much as it can handle then I saw that

Split8KTally took 4 minutes 39 seconds

xml still took 20 seconds.

Then I could no longer test Split8KTally (does not handle more than 8K worth of csv data per record), but continued with xml. I increased the number of items to split in each record to 10,000, which made the longest record to have 117,840 characters, and the number of records as a result of cross apply - 100 million (10,000 records with 10,000 csv values in each). This took 45 seconds.

This clearly shows that xml handily beats other methods (outside of CLR procedure, which can be created to complete the whole thing in a heartbeat) once the data to split becomes big enough in number of csv values to split.

Honestly, I would not even go for all this re-testing, but after I saw the 25 minutes instead of expected 25 seconds coupled with the fact that the WHILE loop (the which should not even exist in T-SQL) can beat my xml solution, I had to do it.

Please let me know if my assumptions are incorrect is some way. I used your harness without making any changes.

Thanks,

Oleg