adam machanic now blogs at http://sqlblog.com

Running sums yet again: SQLCLR saves the day!

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!