Running sums yet again: SQLCLR saves the day!

re: Running sums yet again: SQLCLR saves the day!

Jeff Smith — Thu, 13 Jul 2006 16:54:05 GMT

Hi Adam -- great post. It reminds me a lot of my post about doing cross tabs at the client instead of trying to jump through hoops to do them in T-SQL. And the results are the same -- not only is it easier to do these things at the client, it is faster!

http://weblogs.sqlteam.com/jeffs/archive/2005/05/12/5127.aspx

The only time that SQL Server should be calculating running sums is when those values are needed for further processing within SQL Server (i.e., to store in a table or to calc percentages or something like that). As I often say, if people will just use SQL the way it is intended they would be surprised at how much simplier their code is and how much easier their lives are!

Great blog, the site looks great, looking forward to your posts.

re: Running sums yet again: SQLCLR saves the day!

stook — Tue, 08 May 2007 22:28:48 GMT

Hi,

great post.

the update syntax is not undocumented actually. At least my sql 2000 help says:

SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.

SQLCLR is faster of course, but the SET is simpler one.

About performance tests - if these take less then 10 seconds I'll usualy make a 100 loop for timing to be sure.

re: Running sums yet again: SQLCLR saves the day!

Adam Machanic — Tue, 22 May 2007 18:13:12 GMT

Hi Stook,

That's technically only documented for a single row; the result of a multirow operation is what is not documented (and not deterministic).

"Cursors Run Just Fine"

Adam Machanic — Sat, 13 Oct 2007 14:06:39 GMT

I found Linchi's recent post on use of cursors in the TPC-E test to be quite interesting. The question

re: Running sums yet again: SQLCLR saves the day!

Kevin Boles — Sun, 25 Nov 2007 01:41:15 GMT

Per Hugo's blog here, http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx, you can actually get the TSQL cursor solution to run a tad faster. Instead of this for the cursor declaration:

CURSOR LOCAL FAST_FORWARD FOR

try this:

CURSOR LOCAL FORWARD_ONLY STATIC FOR

On my mondo test server . . . er laptop :), I was able to cut runtime from 5.56 seconds to 4.82 seconds average time. 13.3% faster if I did the math correctly.

Really blows my mind to find that FAST_FORWARD isn't the fastest cursor! I have been advising clients and students for years to use that 'optimization' if they absolutely had to use a cursor. oopsie!

re: Running sums yet again: SQLCLR saves the day!

David — Mon, 01 Dec 2008 15:17:10 GMT

Did some tests with:

LOCAL FAST_FORWARD

LOCAL READ_ONLY FORWARD_ONLY STATIC

(note the READ_ONLY might speed things up a bit)

And it seemed the FORWARD_ONLY approach was faster only on SQL 2000 and not 2005.