Faster, More Scalable SQLCLR String Splitting

re: Faster, More Scalable SQLCLR String Splitting

Ted Krueger — Mon, 27 Apr 2009 01:13:29 GMT

Very impressive Adam as with anything I've read of yours. I'm glad the one on LTD got you up to writing this and that I had the chance to get the feedback on the imperfections in mine along with the more efficient ways of performing the task. It’s easy to say things can be better, but showing it like you have here is the best way we can learn from it. I know I have! -Ted

re: Faster, More Scalable SQLCLR String Splitting

Paul White — Mon, 27 Apr 2009 12:11:04 GMT

Hey Adam,

This debate is currently running over at SQLServerCentral:

http://www.sqlservercentral.com/Forums/Topic695508-338-1.aspx?Update=1

We used a very similar TVF to yours, and found that leaving SqlChars.Value as char[] improved the speed of the split slightly.

That is the current fastest CLR TVF for most cases, though for larger strings with fewer delimiters, a RegEx split is even faster.

A nice graph showing the two fastest CLR TVFs versus two of the best T-SQL approaches can be found here:

http://www.sqlservercentral.com/Forums/FindPost704614.aspx

The other interesting thing to come out so far is that the CLR TVFs are very significantly faster than tally (numbers) table based T-SQL routines, long held to be the best way to split strings.

There are a large number of tests in that thread, from small strings to splitting the entire text of Moby Dick!

Finally, it slightly sucks that while the TVF can stream its output via IEnumerator and IEnumerable, it is a shame that streaming input can't be achieved at the same time, either via the SqlChars or by using a SqlDataReader to read the rows-to-split from a persistent table.

Cheers,

Paul

re: Faster, More Scalable SQLCLR String Splitting

Adam Machanic — Mon, 27 Apr 2009 12:50:42 GMT

Hi Paul,

Thanks for the pointer. I just took a quick look through the thread but couldn't find any methods similar to mine. I saw the "chars" method, but it has the same problem as String.Split: It does all of the splitting work upfront, producing an intermediate collection (it uses an ArrayList -- if I've looked at the wrong solution please point me to the correct one). I didn't see anything in there about anyone actually load testing these methods, and that's where the scalability factor comes into play. The upfront allocations simply will not scale.

By the way, SqlChars does stream the data in, and if I can figure out a way to leverage the char collection without doing an upfront large allocation I'll post back an updated solution that should be even better.

re: Faster, More Scalable SQLCLR String Splitting

Paul White — Mon, 27 Apr 2009 13:27:43 GMT

Thanks for the quick reply - and, yes, you are quite right - all the solutions there do the allocation up-front, not using IEnumerator methods explicitly. I do apologize for my confusion there. Sorry.

The reason for my confusion was that I had experimented with a solution using explicit IEnumerator methods, when trying to write a CLR TVF to read all the strings to split directly from the source table. The rows were read into a SqlDataReader, and the plan was to stream the input from the Reader and also stream the output in the IEnumerator methods.

(BTW yes SqlChars can stream, but this method was trying to read all rows at once, rather than streaming individual rows...)

Sadly, it failed for two reasons:

(a) CLR TVFs can only do data access in the InitMethod, not in the method called via FillRowMethodName, so I would have had to read all the data up front again.

(b) Sending the results down the SqlPipe row-by-row using a SqlDataRecord and associated metadata is really slow. Building a DataTable in a DataSet and sending a SqlDataReader from the DataSet is even worse.

My apologies again.

Paul

re: Faster, More Scalable SQLCLR String Splitting

Adam Machanic — Mon, 27 Apr 2009 15:01:24 GMT

Hi Paul,

No need to apologize for anything :-)

I did some work with the SqlPipe for a running sums problem and found it to be plenty fast for my needs. How big was the data you were returning?

re: Faster, More Scalable SQLCLR String Splitting

Paul White — Mon, 27 Apr 2009 20:52:18 GMT

It was returning the split string results from Jeff Moden's test table. This generates 800K rows of single small strings O.0

It wasn't that slow in absolute terms, it was just a little disappointing that it was slower than the CROSS APPLY with the original CLR TVF. It was about the same speed as the simple tally table solution, which was one of the worst performers overall.

Of course, it may be simply that my C# skills are not all they could be!

The table-at-once approach was never intended to be a good addition to the SQL CLR developer's toolkit, for anyone wondering - it was an attempt to find the fastest possible solution for the original posted problem on SSC.

It's memory allocation in particular would be horrible - I just wanted to find a way to cut down the round trips between the database engine and CLR, and to reduce the number of calls (late-bound by reflection?) to the IEnumerator methods.

I was apologising for replying too quickly without taking the time to fully digest your solution. I stand by that! I will learn :c)

Paul

SQLCLR String Splitting Part 2: Even Faster, Even More Scalable

Adam Machanic — Tue, 28 Apr 2009 19:08:28 GMT

Two days ago, after posting what I thought was a pretty solid SQLCLR string splitting method , I received

re: Faster, More Scalable SQLCLR String Splitting

Thu, 30 Apr 2009 07:07:05 GMT

Have you investigated Regex.Split, using a precompiled regex?

re: Faster, More Scalable SQLCLR String Splitting

Paul White — Thu, 30 Apr 2009 08:25:23 GMT

Yes - see the link to SSC in Adam's article. The Regex is modestly faster for very large strings - but see Adam's next post for the ultimate solution. As you will see, the problem with all the CLR methods up to this point (including a pre-compiled Regex) is that memory is allocated for the entire split up front...

Cheers,

Paul

re: Faster, More Scalable SQLCLR String Splitting

Adam Machanic — Thu, 30 Apr 2009 14:03:20 GMT

M: As Paul mentioned, Regex.Split is out, due to upfront memory allocation. Precompiled regex might still be an option, but I can't find a way to stream the results (i.e., an IndexOf method or something similar). Do you have any ideas?

re: Faster, More Scalable SQLCLR String Splitting

Simon Sabin — Mon, 04 May 2009 20:58:40 GMT

What if you stream the positions back and then use substring in the TSQL. This avoids the need to make any copies of the strings.

re: Faster, More Scalable SQLCLR String Splitting

Paul White — Mon, 04 May 2009 21:53:42 GMT

Simon,

Wouldn't SQL Server be making a copy of the string when it performs the substring? ;c)

Paul

re: Faster, More Scalable SQLCLR String Splitting

Adam Machanic — Tue, 05 May 2009 17:11:32 GMT

I tried something similar in the past without much success, but it was 100% T-SQL. I'm not sure if the performance issue was with SUBSTRING or CHARINDEX but on VARCHAR(MAX) it was pretty bad. I'll have to test and see if that makes a difference. Only issue is that it would require two separate modules to do a split, which becomes a bit more of a maintenance nightmare.

re: Faster, More Scalable SQLCLR String Splitting

TechVsLife — Sun, 19 Jul 2009 16:28:22 GMT

Thank you, this is very useful (though it should be included as a built-in T-SQL function). I haven't read through all the many pages spilled on this subject, but I assume the conclusion is that your CLR version is among the fastest, and faster in most scenarios than the T-SQL version?

btw, I added a comment about an update to your pure T-SQL version, at:

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/splitting-a-string-of-unlimited-length.aspx

re: Faster, More Scalable SQLCLR String Splitting

Dave — Thu, 02 Feb 2012 12:21:49 GMT

Hi Adam

Would you mind please posting the whole code for your example? I am trying to learn how to create a CLR TVF do split strings, and this page comes highly recommended. However, I can't figure out how to fill in the gaps... Thanks :-)

re: Faster, More Scalable SQLCLR String Splitting

Adam Machanic — Sat, 04 Feb 2012 04:29:33 GMT

Hi Dave,

First of all, you should be using this code:

http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx

To use: Fire up Visual Studio, launch a new C# Database Project, right-click on the project and click "Add," select any item, and then go to the editor window and paste the code in on top of the template. Build it, deploy it, and you're done :-)

Feel free to shoot back with any remaining questions!

re: Faster, More Scalable SQLCLR String Splitting

Jesús López — Thu, 24 Jan 2013 10:41:44 GMT

The function would be simpler using yield return:

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction(

TableDefinition="token nvarchar(4000)",

FillRowMethodName="GetNextString")]

public static IEnumerable Split(

[SqlFacet(MaxSize=-1)] SqlString str,

[SqlFacet(IsFixedLength=true, MaxSize=1)] SqlString separator)

{

if (str.IsNull || separator.IsNull) yield break;

char splitter = separator.Value[0];

int currentPosition = 0;

int nextPosition = 0;

string inputStr = str.Value;

int strLength = inputStr.Length;

{

nextPosition = inputStr.IndexOf(splitter, currentPosition);

if (nextPosition == -1) nextPosition = strLength;

yield return inputStr.Substring(currentPosition, nextPosition - currentPosition).Trim();

currentPosition = nextPosition + 1;

} while (currentPosition < strLength);

}

public static void GetNextString(Object obj, out SqlString str)

{

str = new SqlString((string)obj);

}

};