Search results matching tags 'Performance' and 'SQLCLR'

SQLCLR Performance Session at TechEd US

Adam Machanic — Wed, 08 May 2013 16:14:00 GMT

I am super-excited to visit New Orleans next month for Microsoft TechEd; it will be my sixth time speaking at the show.

My session takes an in-depth look at some of the techniques I've developed for using SQLCLR modules -- and some of the great performance gains I've been able to achieve.

Hope to see you in NOLA! If you're not attending the show, the video will be available on demand a few days after I give the talk.

Performance impact: Try/Catch blocks in SQLCLR – a follow up

Linchi Shea — Fri, 08 Jul 2011 03:48:00 GMT

My previous post showed a simple test that appears to suggest that you may experience significant performance degradation if multiple users are calling the same SQLCLR function at the same time and they are all catching a lot of exceptions.

However, it’s not clear whether that behavior is limited to SQLCLR or applies to .NET in general. To see if I would run into similar behavior, I wrote a simple C# program for a quick test. To simulate the concurrent exception-handling load, the test program spawns 10 background threads, each calling the following method nonstop (the complete program is listed at the end of this post):

static void TryCatch()

{

int c = 0;

for(int i = 0; i < 200000; i++)

{

try { c= i/c; } catch (Exception) { c = 1;}

}

Note that variable c is assigned value 0, thus forcing a divide-by-zero exception, and this exception is handled 200,000 times in a loop.

The elapsed time of the method is measured both when there is no additional background threads and when 10 additional threads are running.

On my old two-core 2GB PC workstation, the following output from the program is typical among many runs:

C:\junk>test2.exe

TryCatch() without any background thread = 15617968 ticks

TryCatch() without any background thread = 15559616 ticks

TryCatch() without any background thread = 15566064 ticks

TryCatch() without any background thread = 17472496 ticks

TryCatch() without any background thread = 15782952 ticks

Thread 0 Called TryCatch() 1000 times.

Thread 1 Called TryCatch() 1000 times.

Thread 2 Called TryCatch() 1000 times.

Thread 3 Called TryCatch() 1000 times.

Thread 4 Called TryCatch() 1000 times.

Thread 5 Called TryCatch() 1000 times.

Thread 6 Called TryCatch() 1000 times.

Thread 7 Called TryCatch() 1000 times.

Thread 8 Called TryCatch() 1000 times.

Thread 9 Called TryCatch() 1000 times.

TryCatch() with 10 background threads = 17498336 ticks

TryCatch() with 10 background threads = 17535984 ticks

TryCatch() with 10 background threads = 17664424 ticks

TryCatch() with 10 background threads = 17515200 ticks

TryCatch() with 10 background threads = 17465312 ticks

TryCatch() with 10 background threads = 17498432 ticks

TryCatch() with 10 background threads = 17508656 ticks

TryCatch() with 10 background threads = 17710856 ticks

At least for this test, the adverse concurrency impact that we saw with SQLCLR--and reported in the previous post--is not observed.

Although it’s not strictly an apple-to-apple comparison between this test without SQLCLR and that described in the previous post with SQLCLR, the end user experience is so different that it calls into question why SQLCLR does not seem to handle many concurrent exceptions as gracefully. I don’t have an answer.

I have absolutely no knowledge of how SQLCLR works internally, and can’t explain the concurrency behavior observed in the previous post.

By the way, when I set variable c to 1 in the TryCatch() method, thus avoiding the exception, its concurrency impact (or the lack of) did not change much, if at all.

Anyway, here is the test program. For the output presented above, the program was compiled with .NET Framework 3.5.

using System;

using System.Diagnostics;

using System.Threading;

public partial class Test

{

public static void Main()

{

Stopwatch stop_watch = new Stopwatch();

// warming up a bit

for(int i = 0; i < 5; i++)

{

TryCatch();

}

// measure the elaped time without any additional background threads

for(int i = 0; i < 5; i++)

{

stop_watch.Reset();

stop_watch.Start();

TryCatch();

stop_watch.Stop();

Console.WriteLine("TryCatch() without any background thread = {0} ticks",

stop_watch.ElapsedTicks);

Thread.Sleep(2000);

}

Thread[] user_threads = new Thread[10];

for(int i = 0; i < 10; i++)

{

user_threads[i] = new Thread(new ThreadStart(StartTryCatch));

user_threads[i].Name = i.ToString();

user_threads[i].IsBackground = true;

user_threads[i].Start();

Thread.Sleep(10);

}

Thread.Sleep(5000);

// now measure the elaped time again with 10 additiona threads running

for(int i = 0; i < 20; i++)

{

stop_watch.Reset();

stop_watch.Start();

TryCatch();

stop_watch.Stop();

Console.WriteLine("TryCatch() with 10 background threads = {0} ticks",

stop_watch.ElapsedTicks);

Thread.Sleep(2000);

}

// this will never be reached. You have to Cltr-C to stop the program

for (int i = 0; i < 10; i++)

{

if (user_threads[i] != null)

{

user_threads[i].Join();

}

static void StartTryCatch()

{

int i = 0;

while (true)

{

if (i % 1000 == 0)

{

Console.WriteLine("Thread {0} Called TryCatch() 1000 times.", Thread.CurrentThread.Name);

}

TryCatch();

Thread.Sleep(10);

i++;

}

static void TryCatch()

{

int c = 0;

for(int i = 0; i < 200000; i++)

{

try { c= i/c; } catch (Exception) { c = 1;}

}

};

Performance impact: too many try/catch blocks may be harmful in SQLCLR

Linchi Shea — Wed, 06 Jul 2011 04:40:00 GMT

If you have many try/catch blocks in your .NET code and your code execution actually passes through them, you should expect a performance hit. That’s intuitive and no surprise.

What is surprising is the extent to which you may experience severe performance degradation when multiple users are executing a piece of SQLCLR code (e.g. calling a SQLCLR table valued function) that contains many try/catch blocks. In other words, there may significant and adverse concurrency implication if you use too many try/catch blocks in your SQLCLR code.

Here is an example to prove it!

I have implemented two SQLCLR table-valued functions: tvf_GetDataWithTryCatch() and tvf_GetDataWithoutTryCatch(). These two functions return exactly the same resultset: 2000 rows with 100 columns of integers. The functions simulate the case where the integer values are out of bound (i.e. greater than the maximum value allowed for the integer datatype, which is SqlInt32.MaxValue in the attached C# code), and when that happens, the functions return NULL instead.

There are many ways to check whether a value is equal to or greater than SqlInt32.MaxValue. As the name implies, tvf_GetDataWithTryCatch() implements the check in a try block and returns NULL in the corresponding catch block. The tvf_GetDataWithoutTryCatch() function, on the other hand, implements the check with a C# conditional operator (?:).

When there is no background concurrent load

The tvf_GetDataWith TryCatch() function is expected to be slower than the tvf_GetDataWithoutTryCatch() function because of the inherent overhead with a try/catch block, and tests show that is indeed the case. On my rather antiquated PC with 2GB of physical memory, the following query

select * from dbo.[tvf_GetDataWithTryCatch]()

returns all the 2000 rows in about 8 seconds, whereas this query

select * from [dbo].[tvf_GetDataWithoutTryCatch]()

returns in about 1 second. Consistently, the function doing 200,000 try/catch’es is about eight times slower than the function doing 200,000 conditional operations. So far so good! No surprises!

When there is background concurrent load

When I have 10 users running tvf_GetDataWithoutTryCatch() nonstop against the same SQL Server 2008 instance in the background, the tvf_GetDataWithoutTryCatch() returns in about 2 seconds consistently. So running additional load of tvf_GetDataWithoutTryCatch() does slow it down a bit, but not a lot.

Now, when I have 10 users running tvf_GetDataWithTryCatch() nonstop in the background against the same SQL Server 2008 instance, the performance of tvf_GetDataWithTryCatch() degrades dramatically. This time it takes about 76 seconds to return all the 2000 rows. That is about 10 times slower than it is when there is no concurrent load. And it is about 38 times slower than tvf_GetDataWithoutTryCatch() under the same 10-user background concurrent load. Note that tvf_GetDataWithoutTryCatch() returns in about 2 seconds regardless whether the 10-user background load is calling tvf_GetDataWithTryCatch() or calling tvf_GetDataWithoutTryCatch().

If you think 38 times slower is bad, in real production environments I have seen far worse concrrency impact as a result of too many try/catch blocks.

With the attached C# code, you can run the tests yourself to see the behavior. Your results may differ in some details, but you should see a similar pattern. If not, post back a comment and let us know.

Performance, Discounts, and an Excuse to Visit New York City

Adam Machanic — Thu, 02 Jun 2011 14:21:00 GMT

A couple of weeks ago I announced a two-day advanced performance seminar in New York City, which will be delivered in July. This seminar will cover SQLCLR and parallelism techniques to help you take performance well beyond the levels that typical tuning exercises yield. Check out the links for more details, including a full outline.

Thanks to the great response so far, we have decided to extend the early registration discount for a few more days. You have until the end of the day tomorrow, June 3, to take advantage of the $100 savings off of the $1050 course fee. To get the discount, use the code EARLYBIRD on the registration page.

Finally, I would like to point out that one of the main reasons that I chose New York City for this course is that it is by far my favorite city to visit. I just noticed yesterday that a bunch of hotels are offering a free third night this summer--so I hope that you'll be able to come for the course and then stay an extra night or two to enjoy the city. Note that we also have a hotel discount available to course attendees, unrelated to the third night offer. Let me know if you would like more information on that.

Hope to see you in New York next month!

Two Days of Advanced Performance Techniques - July 14-15, New York City

Adam Machanic — Wed, 18 May 2011 16:19:00 GMT

I am pleased to announce that I will be delivering two days of training in New York City, July 14 and 15.

This seminar focuses on achieving "next-level" performance--going beyond that which you can gain via normal tuning methodologies. The vehicles for this performance improvement are two technologies that I've been pushing on this blog and in other venues for a long time: SQLCLR and parallelism. The seminar will be based on the in-depth materials that I used for my full-day sessions PASS conference in 2009 and 2010. These seminars were both quite well-received, but I have tweaked and tuned the content to make it even better and more focused on the bottom line goal of achieving maximum performance.

Full information on the seminar is available on the Data Education web site. There is also an early bird discount currently in effect. Use the discount code "EARLYBIRD" to save $100 on the $1050 registration fee.

I would like to take this opportunity to mention that Data Education is a new training venture that I've recently launched. This will be the company's second public training event (our first featured Kalen Delaney in the Boston area). The company is an evolution of Boston SQL Training, a company that I started a couple of years ago with the goal of bringing extremely high-quality SQL Server training events to the Boston area. The new name, Data Education, reflects our desire to focus beyond Boston and on a broader technology spectrum. We plan to eventually move into training on data-related programming (Entity Framework and similar), other DBMS platforms, NoSQL technologies, and wherever else the database industry moves.

Currently, aside from my course in New York we've announced an Analysis Services and PowerPivot course featuring Teo Lachev, which will take place in the Boston area September 19-23. Several other courses will be announced shortly, so stay tuned and consider following us on Twitter (@DataEducation).

If you've read this far, I would greatly appreciate your taking part in a quick and informal poll: in the comments section below, please let me know what geographic location would be interesting to you for an advanced SQL Server course, and what topic areas you're not seeing enough of.

Thanks, everyone, and I'm looking forward to seeing you in New York!

T-SQL Tuesday #001: Exploring "Fuzzy" Interval Islands Using SQLCLR

Adam Machanic — Tue, 08 Dec 2009 19:13:00 GMT

When working with time intervals, we often want to ask a couple of basic questions:

Which time periods are not covered by our intervals? These are known as "gaps".
What are the time ranges that we are fully covering? These are known as "islands".

If you're unfamiliar with "gaps" and "islands" I highly recommend reading some of Itzik Ben-Gan's recent work in SQL Server Magazine. He's had a great series going on the topic. But one thing that he hasn't found a good T-SQL solution for is a problem that I call "fuzzy islands."

When is an island fuzzy? When it doesn't necessarily have a fixed end time. For an example of this, consider a store, selling a number of products. Management might want to see a report showing when each product was selling. This is, effectively, an island question. The goal is to find all of the covered ranges during which sales occurred. But running such a report, you might find that way too much data is returned. A given product may have sold units on Monday, Tuesday, and Thursday, but for some reason no one bought one on Wednesday. Creating a new island every time there is a small gap will create a 300-page report where a 1-page dashboard might suffice--not a good user experience, nor a good way of representing the data. The solution? Introduce a bit of fuzziness--a rule that says, for instance, that a gap is only a gap if it's longer than 7 days.

Answering the fuzzy islands question is not a very difficult thing to do in T-SQL. The basic algorithm follows:

Find all of the "start" dates or times. These are simply those dates or times for which a previous date or time in the fuzzy interval does not exist. So if a row is dated 2009-12-08 and our fuzzy granularity is 7 days, we know we have a start date if there is no other covered data from the end of November.
For each start date identified, find the minimum date greater than the start date. This is done by looking ahead rather than behind, so if our date is 2009-12-08 and we have a granularity of 7 days, we'll look forward until December 15th.
Optionally, add the fuzzy factor to the end date. This is something that I think is a good idea, as it introduces the concept of an "active" interval--a period over which, for example, a product is considered to have been selling. The interval shouldn't necessarily terminate the day that the last sale occurred. But of course this depends on the situation in question.

The following query produces a fuzzy islands report for each product in the AdventureWorks (or AdventureWorks2008) Production.TransactionHistory table. You can modify the @active_interval variable to tweak the fuzziness and change the output.

--Find all "active" product time ranges, meaning that the product
--has sold within the previous 7 days
DECLARE @active_interval INT = 7

SELECT DISTINCT
    t_s.ProductID,
    t_s.TransactionDate AS StartDate,
    DATEADD
    (
        dd,
        @active_interval,
        (
            SELECT
                MIN(t_e.TransactionDate)
            FROM Production.TransactionHistory AS t_e
            WHERE
                t_e.ProductID = t_s.ProductID
                AND t_e.TransactionDate >= t_s.TransactionDate
                AND NOT EXISTS
                (
                    SELECT *
                    FROM Production.TransactionHistory AS t_ae
                    WHERE
                        t_ae.ProductID = t_s.ProductID
                        AND t_ae.TransactionDate BETWEEN 
                            DATEADD(dd, 1, t_e.TransactionDate) 
                            AND DATEADD(dd, @active_interval, t_e.TransactionDate)
                )
        )
    ) AS EndDate
FROM 
(
    SELECT DISTINCT
        ProductID,
        TransactionDate
    FROM Production.TransactionHistory
) AS t_s
WHERE
    NOT EXISTS
    (
        SELECT *
        FROM Production.TransactionHistory AS t_ps
        WHERE
            t_ps.ProductID = t_s.ProductID
            AND t_ps.TransactionDate BETWEEN 
                DATEADD(dd, -@active_interval, t_s.TransactionDate) 
                AND DATEADD(dd, -1, t_s.TransactionDate)
    )
ORDER BY
    ProductID,
    StartDate
GO

Running this query you'll find that it works... But the results are returned a bit more slowly than we might desire--15 to 16 seconds on my end. Looking at the query plan, the reason for this becomes quite obvious: Lots and lots of table scans. How can we eliminate all of the overhead?

SQLCLR to the rescue. The best way to solve this problem--at least until the SQL Server team adds proper OVER clause support (LAG and LEAD, specifically)--is to use a cursor algorithm. We could do this in a T-SQL cursor, but why bother? Cursor logic in SQLCLR is much, much faster.

To solve the problem, I implemented an enumerator, called active_products_enumerator. The enumerator is initialized using a SqlDataReader and an "active interval" -- the number of days we're allowing for fuzziness. The DataReader is expected to return rows ordered by ProductID and TransactionDate. The enumeration process uses the following algorithm:

If the current ProductID is not the same as the previous ProductID, return an end date for the previous interval and start a new one
If the current period date is greater than the previous period date plus the active interval, return an end date for the previous interval and start a new one
Otherwise, continue

Following is the MoveNext method for the enumerator (the complete code is attached to this post so that you can run it on your end without my bombarding you with a gigantic code-filled post):

public bool MoveNext()
{
    try
    {
        current_results = null;

        while (r.Read())
        {
            new_ProductID = r.GetSqlInt32(0);
            new_period_date = r.GetDateTime(1);

            if (new_ProductID != ProductID)
            {
                if (ProductID != 0)
                {
                    current_results = new results(
                        ProductID,
                        StartDate,
                        period_plus_interval);
                }

                ProductID = new_ProductID;
                StartDate = new_period_date;
                period_plus_interval = new_period_date.AddDays(activeInterval);

                if (current_results != null)
                    return (true);
            }
            else
            {
                if (period_plus_interval < new_period_date)
                {
                    current_results = new results(
                        ProductID,
                        StartDate,
                        period_plus_interval);

                    StartDate = new_period_date;
                }

                period_plus_interval = new_period_date.AddDays(activeInterval);

                if (current_results != null)
                    return (true);
            }
        }

        //return the last row of data
        if (ProductID != 0)
        {
            current_results = new results(
                ProductID,
                StartDate,
                period_plus_interval);

            //set this to 0 so we don't return another row
            ProductID = 0;
        }

        if (current_results != null)
            return (true);
        else
        {
            r.Dispose();
            return (false);
        }
    }
    catch
    {
        r.Dispose();
        throw;
    }
}

Put into a table-valued function, as the attached code does, this algorithm will return the same data as the T-SQL query in under a third of a second on my end--around 45 times faster than the T-SQL version.

Note that I've played some games with a loopback connection to get this whole thing to work. That's a topic for a future blog post, so stay tuned. In the meantime, please realize that you'll have to catalog the assembly with EXTERNAL_ACCESS permission to make this happen.

This post was created for T-SQL Tuesday, the revolving SQL Server blog party, hosted this month by... me. Enjoy!

Full Outline for my PASS Pre-Con: SQLCLR From Beginner to Expert

Adam Machanic — Mon, 17 Aug 2009 14:00:00 GMT

Since the announcement of my SQLCLR pre-conference seminar at this November's PASS conference I have received a few e-mails asking for more detail about what I will be covering. In addition to the Q&A I did with PASS, I thought it might be helpful if I post the entire schedule for the day (see below).

The seminar's goal is to literally move you from "Beginner" to "Expert" and although some familiarity with the topic will help you get into the latter category faster, none is required. I do recommend at least basic knowledge of C# and ADO.NET; there are a couple of free online courses (such as this one), and if you're planning to addend the pre-con and have not yet worked with C# I highly recommend taking a few hours to go through one of them. As a matter of fact, even if you aren't planning to attend the pre-con you should do this; learning another language is fun and, in my opinion, will improve your T-SQL skills by opening your mind to new and different ways of solving problems.

Not sure if you're interested in getting involved with all of this SQLCLR stuff? Then check out my 24 Hours of PASS session, SQLCLR or T-SQL? A Brief Survey of Performance Options. In this session I will present just a few of the performance examples from the full-day pre-con, and in slightly less detail due to time constraints, but it should be enough to show you the power and flexibility that well-engineered SQLCLR solutions can provide.

If you have any questions about either the 24 Hours of PASS session or the pre-con, feel free to post here or contact me through my blog. I'm really looking forward to both events and hope to see some of you there!

SQLCLR From Beginner to Expert

Module 1: Overview (What SQLCLR Can Do)

Why .NET in the data tier?
Does it perform?
What kinds of business and technical challenges can it solve?
Is it hard to manage?

Module 2: Introduction to SQLCLR: UDFs

Creating your first SQLCLR method
CREATE ASSEMBLY and related statements
Intro to permission sets
SqlTypes library
Interaction concerns for nullable types and new SQL Server 2008 data types
SqlFunction attribute
Determinism, precision, and system data access
Dealing with Visual Studio deployment issues
IEnumerable and table-valued UDFs
Performance and UDFs

Module 3: SQLCLR Stored Procedures and Data Access

Stored procedures vs. functions
Introduction to the in-proc data access model
The context connection
SqlContext class
SqlPipe class
Exceptions and exception handling
When does it make sense to use a CLR stored procedure?
When should a CLR UDF be used instead?
A few thoughts on SQLCLR triggers
Use cases

Data access and performance
Better administration through SQLCLR
Solving sequential and time series problems

Module 4: Security, Reliability, and Design Considerations

What do the permission sets REALLY mean?
HostProtection
Code Access Security
Database trustworthiness
Strong named assemblies
Designing for least privilege
Designing for reuse
Creating robust utility classes

Module 5: User-Defined Types

Why complex types?
SqlUserDefinedType attribute
INullable interface
Serialization and ordering
Static and instance methods
Data validation and domain rules checking
The Parse method
Eliminating the Parse method: Factory type patterns and strongly-typed methods
Design considerations
Use cases for types and static methods
Helping to bridge the app/database gap

Module 6: User-Defined Aggregates

Where do UDAs fit and what problems do they solve?
What are their limitations?
SqlUserDefinedAggregate attribute
The various methods that make up a UDA
The SQL Server 2005 8000-byte limitation and the SQL Server 2008 lack thereof
Solving business and analytical problems using UDAs

Module 7: Troubleshooting and Ongoing Maintenance

Deployment concerns
The SQLCLR management UDAs
ALTER ASSEMBLY
Assembly versioning
Changing code (without breaking everything)

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

Adam Machanic — Tue, 28 Apr 2009 18:54:00 GMT

Two days ago, after posting what I thought was a pretty solid SQLCLR string splitting method, I received a comment telling me about a big thread on SQLServerCentral dedicated to the question of how best to split strings. So I jumped in, and went back and forth, and back and forth, and back... and forth...

Many, many messages and several revisions and re-revisions later, I am happy to present the newer, better, more bug-free SQLCLR string splitting function. Not only is it faster than the previous one I posted, but it also handles multi-character delimiters, ignores consecutive delimiters as well as pre and post delimiters, and best of all, it won't get itself stuck in an infinite loop in certain cases (always a really great stability feature).

This version walks the SqlChars character array rather than using the IndexOf method on the string, which we found to be a somewhat faster technique--and it's certainly the most scalable and memory efficient method I can imagine.

Special thanks to SQLServerCentral member Florian Reischl, who was the main person keeping the thread going with me during the last couple of days, and who managed to re-write my versions and eke out even better performance by modifying the algorithms. Great stuff--it was definitely the most fun I've had on a technical forum in quite a long time.

But without further ado, the code:

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(
       FillRowMethodName = "FillRow_Multi",
       TableDefinition = "item nvarchar(4000)"
       )
    ]
    public static IEnumerator SplitString_Multi(
      [SqlFacet(MaxSize = -1)]
      SqlChars Input,
      [SqlFacet(MaxSize = 255)]
      SqlChars Delimiter
       )
    {
        return (
            (Input.IsNull || Delimiter.IsNull) ?
            new SplitStringMulti(new char[0], new char[0]) :
            new SplitStringMulti(Input.Value, Delimiter.Value));
    }

    public static void FillRow_Multi(object obj, out SqlString item)
    {
        item = new SqlString((string)obj);
    }

    public class SplitStringMulti : IEnumerator
    {
        public SplitStringMulti(char[] TheString, char[] Delimiter)
        {
            theString = TheString;
            stringLen = TheString.Length;
            delimiter = Delimiter;
            delimiterLen = (byte)(Delimiter.Length);
            isSingleCharDelim = (delimiterLen == 1);

            lastPos = 0;
            nextPos = delimiterLen * -1;
        }

        #region IEnumerator Members

        public object Current
        {
            get
            {
                return new string(theString, lastPos, nextPos - lastPos);
            }
        }

        public bool MoveNext()
        {
            if (nextPos >= stringLen)
                return false;
            else
            {
                lastPos = nextPos + delimiterLen;

                for (int i = lastPos; i < stringLen; i++)
                {
                    bool matches = true;

                    //Optimize for single-character delimiters
                    if (isSingleCharDelim)
                    {
                        if (theString[i] != delimiter[0])
                            matches = false;
                    }
                    else
                    {
                        for (byte j = 0; j < delimiterLen; j++)
                        {
                            if (((i + j) >= stringLen) || (theString[i + j] != delimiter[j]))
                            {
                                matches = false;
                                break;
                            }
                        }
                    }

                    if (matches)
                    {
                        nextPos = i;

                        //Deal with consecutive delimiters
                        if ((nextPos - lastPos) > 0)
                            return true;
                        else
                        {
                            i += (delimiterLen-1);
                            lastPos += delimiterLen;
                        }
                    }
                }

                lastPos = nextPos + delimiterLen;
                nextPos = stringLen;

                if ((nextPos - lastPos) > 0)
                    return true;
                else
                    return false;
            }
        }

        public void Reset()
        {
            lastPos = 0;
            nextPos = delimiterLen * -1;
        }

        #endregion

        private int lastPos;
        private int nextPos;

        private readonly char[] theString;
        private readonly char[] delimiter;
        private readonly int stringLen;
        private readonly byte delimiterLen;
        private readonly bool isSingleCharDelim;
    }
};

Enjoy!

Faster, More Scalable SQLCLR String Splitting

Adam Machanic — Sun, 26 Apr 2009 23:59:00 GMT

It seems like every couple of months we see yet another post on SQLCLR string splitting routines. Many bloggers, I suppose, are still struggling, even three years later, to find that "perfect" use case for SQLCLR. Is string splitting it? Probably not. And with SQL Server 2008 table-valued parameters now available, SQLCLR string splitting has become an even less interesting exercise than it was before. None the less, a recent post on the Less Than Dot site has inspired me to counter with my own best SQLCLR string splitting method, for those of you who are still interested in solving this problem.

I've noticed that almost invariably, the methods posted online stress how very easy it is to do string splitting in .NET, thanks to the String.Split method. And while this easy method tends to work pretty well for small strings and on workloads that don't need to scale, it quickly breaks down when any amount of load is introduced (something that, unfortunately, most writers don't bother considering). The Less Than Dot writer, "onpnt" did do some testing, and discovered that--surprise, surprise--String.Split isn't all that great.

The issue? Well, it all comes down to large memory allocations and the art of scalable .NET programming--an area about which many SQL Server developers can (and do) remain blissfully naïve. In .NET, reduction of memory utilization--especially large allocation done en masse--is king, and String.Split does exactly the wrong thing. It takes the input string, breaks it into N chunks, and allocates all of the memory needed to store those chunks and pointers to those chunks, in one big huge operation. This can't possibly scale, and indeed it doesn't. I did a quick SQLQueryStress test of a TVF based on String.Split and saw fairly good performance when the input sentences were small (in the 40-400 byte range--see below), but after a certain point the AppDomains began recycling and performance became abysmal. Protections put in place for stability of the CLR host include memory leak detection, and this kicks in quite readily when we force allocation of so much memory in one shot--a good thing for the SQL Server instance as a whole, but not great when we're trying to really split a huge string.

Students of .NET who are concerned with scale (and really, everyone should be) are urged to look at the way problems are handled in LINQ. Here the vast majority of requests are internally handled using streaming iterator patterns, rather than moving around huge chunks of memory. This turns out to a much more scalable option for several reasons: Lower in-flight memory utilization, fewer large object heap allocations, and better access by the garbage collector to collect intermediate data that is no longer needed.

So how can we apply streaming to the string splitting problem? Rather than break the string up into all of its component parts upfront, we can walk the string step-by-step, only finding the next piece as required. In order to handle this, I created the following worker class:

    public class splitIt : IEnumerator
    {
        public splitIt(string theString, char delimiter)
        {
            this.theString = theString;
            this.delimiter = delimiter;
            this.lastPos = -1;
            this.nextPos = -1;
        }

        #region IEnumerator Members

        public object Current
        {
            get { return theString.Substring(lastPos, nextPos - lastPos).Trim(); }
        }

        public bool MoveNext()
        {
            if (nextPos >= theString.Length)
                return false;
            else
            {
                lastPos = nextPos + 1;

                if (lastPos == theString.Length)
                    return false;

                nextPos = theString.IndexOf(delimiter, lastPos);
                if (nextPos == -1)
                    nextPos = theString.Length;

                return true;
            }
        }

        public void Reset()
        {
            this.lastPos = -1;
            this.nextPos = -1;
        }

        #endregion

        private int lastPos;
        private int nextPos;
        private string theString;
        private char delimiter;
    }

This class is a simple enumerator implementation that looks for the next delimiter on each iteration, only when requested. Splitting strings in this way, rather than using String.Split, means that no huge upfront allocation takes place. Aside from the sentence itself, only one "chunk" is in play at any given time, and any chunks that have already been used can be garbage collected as needed when memory is tight.

Wiring this class up in a SQLCR TVF is just about as simple as when using String.Split:

    [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "FillIt", TableDefinition = "output nvarchar(4000)")]
    public static IEnumerator faster_split
        (
            SqlChars instr, 
            [SqlFacet(IsFixedLength=true, MaxSize=1)]
            SqlString delimiter
        )
    {
        return (
            (instr.IsNull || delimiter.IsNull) ? 
            new splitIt("", ',') : 
            new splitIt(instr.ToSqlString().Value, Convert.ToChar(delimiter.Value)));
    }

    public static void FillIt(object obj, out SqlString output)
    {
        output = (new SqlString((string)obj));
    }

I've enhanced this example slightly compared with most of the usual suspects: A SqlFacet attribute is used to make sure that the delimiter is only a single character, and I've added a bit of additional code in the main method to deal with NULL inputs.

The scalability difference between this method and the String.Split method is staggering in the simple tests I ran today on my SQL Server 2008 test server. With small sentences, even under moderate load (100 concurrent threads), each method performs more or less equivalently. But as sentence size increases to 50KB, the String.Split method begins slowing, taking almost 2 seconds per iteration, and the occasional AppDomain recycle is seen in the SQL Server log. The streaming method, on the other hand, continues to complete its job in just over 1/10th of a second, and causes no AppDomain recycles. Increasing to 500KB sentences, String.Split causes numerous AppDomain recycles and time per iteration increases to over 30 seconds, while the streaming method averages just 16 seconds per iteration. Jumping to 5MB sentences, String.Split causes almost continuous AppDomain recycles, and each iteration takes almost 6 minutes to complete. Yet with the streaming method, even with sentences of this size I am still unable to cause an AppDomain recycle to occur, and iterations complete in around 55 seconds.

The test I did was quite simple, and I won't post too many details here as I prefer that you test with your own workloads and draw your own conclusion about how this method fares when compared with T-SQL versions or the naïve String.Split method. I hope that if you do test you'll post back here with your results so that we can all learn the best way to handle these problems--whether or not string splitting really is all that interesting in the post-SQL Server 2005 world.

Running sums yet again: SQLCLR saves the day!

Adam Machanic — Thu, 13 Jul 2006 01:52:00 GMT

Back again! Fourth post for the month of February, making this my best posting month in, well, months. Expect this trend to continue.

After yesterday's post on running sums and the evils of cursors, Jamie Thompson came up with a faster solution than the curser I posted. Alas, Jamie's solution uses an undocumented form of UPDATE syntax, and I am really not comfortable using it. So I set out to find still another solution. As promised at the end of my last post, SQLCLR is where I looked. And my instinct proved correct.

Jamie's solution runs in 4 seconds on my laptop. Compared to 14 seconds for the cursor I posted, that's a great enhancement. But I knew that we could do better still, and without undocumented syntax and temp tables.

The answer? A SQLCLR stored procedure. Same logic as the cursor: Pull back the data in order, then loop over the rows and maintain the running sum in a variable. But thanks to the SqlPipe's SendResults methods, we don't need a temporary table for this one -- the results can be sent back one row at a time, and will still show up on the client as a single result set.

Here's how I did it:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void TransactionHistoryRunningSum()
    {
        using (SqlConnection conn = new SqlConnection("context connection=true;"))
        {
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = @"" +
                "SELECT TransactionID, ActualCost " +
                "FROM Production.TransactionHistory " +
                "ORDER BY TransactionID";

            SqlMetaData[] columns = new SqlMetaData[3];
            columns[0] = new SqlMetaData("TransactionID", SqlDbType.Int);
            columns[1] = new SqlMetaData("ActualCost", SqlDbType.Money);
            columns[2] = new SqlMetaData("RunningTotal", SqlDbType.Money);

            decimal RunningSum = 0;

            SqlDataRecord record = new SqlDataRecord(columns);

            SqlContext.Pipe.SendResultsStart(record);

            conn.Open();

            SqlDataReader reader = comm.ExecuteReader();

            while (reader.Read())
            {
                decimal ActualCost = (decimal)reader[1];
                RunningSum += ActualCost;

                record.SetInt32(0, (int)reader[0]);
                record.SetDecimal(1, ActualCost);
                record.SetDecimal(2, RunningSum);

                SqlContext.Pipe.SendResultsRow(record);
            }

            SqlContext.Pipe.SendResultsEnd();
        }
    }
};

Results? 3 seconds on my laptop. 25% better than the previous best solution. Not bad!

And, I even did better than that. By creating a CLR table-valued user-defined function that uses a custom class wrapping a SqlDataReader, I was able to get this operation down to 2 seconds on my laptop. However, due to restrictions related to passing around context connections, that solution can not use a context connection and is therefore highly suboptimal. Until I figure out how to pass around a context connection (or if it's even possible), I'll keep that one off the blog.

So to recap: We certainly have not gotten rid of the cursor. This SQLCLR solution is really just a cursor in disguise. But we've built a better cursor, because this one doesn't require temporary tables. And that I can live with--for now.

Thanks to Jamie Thompson for prompting me to not wait several weeks before following up as I usually do with these posts!