Back again! Fourth post for the month of February, making this my best
posting month in, well, months. Expect this trend to continue.
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
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:
public partial class StoredProcedures
public static void
using (SqlConnection conn =
new SqlConnection("context connection=true;"))
SqlCommand comm = new SqlCommand();
comm.CommandText = @"" +
TransactionID, ActualCost " +
Production.TransactionHistory " +
SqlMetaData columns = new SqlMetaData;
columns = new SqlMetaData("TransactionID", SqlDbType.Int);
columns = new SqlMetaData("ActualCost", SqlDbType.Money);
columns = new SqlMetaData("RunningTotal", SqlDbType.Money);
decimal RunningSum = 0;
SqlDataRecord record = new SqlDataRecord(columns);
SqlDataReader reader = comm.ExecuteReader();
ActualCost = (decimal)reader;
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!