Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p><strong>Update</strong>, if you are running SQL Server 2012 see: <a href="https://stackoverflow.com/a/10309947">https://stackoverflow.com/a/10309947</a></p> <p>The problem is that the SQL Server implementation of the Over clause is <a href="http://wayback.archive.org/web/20090625062153/http://www.mydatabasesupport.com/forums/sqlserver-programming/189015-tsql-accmulations.html" rel="noreferrer">somewhat limited</a>. </p> <p>Oracle (and ANSI-SQL) allow you to do things like: </p> <pre><code> SELECT somedate, somevalue, SUM(somevalue) OVER(ORDER BY somedate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal FROM Table </code></pre> <p>SQL Server gives you no clean solution to this problem. My gut is telling me that this is one of those rare cases where a cursor is the fastest, though I will have to do some benchmarking on big results.</p> <p>The update trick is handy but I feel its fairly fragile. It seems that if you are updating a full table then it will proceed in the order of the primary key. So if you set your date as a primary key ascending you will <code>probably</code> be safe. But you are relying on an undocumented SQL Server implementation detail (also if the query ends up being performed by two procs I wonder what will happen, see: MAXDOP): </p> <p>Full working sample: </p> <pre><code>drop table #t create table #t ( ord int primary key, total int, running_total int) insert #t(ord,total) values (2,20) -- notice the malicious re-ordering insert #t(ord,total) values (1,10) insert #t(ord,total) values (3,10) insert #t(ord,total) values (4,1) declare @total int set @total = 0 update #t set running_total = @total, @total = @total + total select * from #t order by ord ord total running_total ----------- ----------- ------------- 1 10 10 2 20 30 3 10 40 4 1 41 </code></pre> <p>You asked for a benchmark this is the lowdown. </p> <p>The fastest SAFE way of doing this would be the Cursor, it is an order of magnitude faster than the correlated sub-query of cross-join. </p> <p>The absolute fastest way is the UPDATE trick. My only concern with it is that I am not certain that under all circumstances the update will proceed in a linear way. There is nothing in the query that explicitly says so. </p> <p>Bottom line, for production code I would go with the cursor. </p> <p>Test data: </p> <pre><code>create table #t ( ord int primary key, total int, running_total int) set nocount on declare @i int set @i = 0 begin tran while @i &lt; 10000 begin insert #t (ord, total) values (@i, rand() * 100) set @i = @i +1 end commit </code></pre> <p>Test 1:</p> <pre><code>SELECT ord,total, (SELECT SUM(total) FROM #t b WHERE b.ord &lt;= a.ord) AS b FROM #t a -- CPU 11731, Reads 154934, Duration 11135 </code></pre> <p>Test 2: </p> <pre><code>SELECT a.ord, a.total, SUM(b.total) AS RunningTotal FROM #t a CROSS JOIN #t b WHERE (b.ord &lt;= a.ord) GROUP BY a.ord,a.total ORDER BY a.ord -- CPU 16053, Reads 154935, Duration 4647 </code></pre> <p>Test 3:</p> <pre><code>DECLARE @TotalTable table(ord int primary key, total int, running_total int) DECLARE forward_cursor CURSOR FAST_FORWARD FOR SELECT ord, total FROM #t ORDER BY ord OPEN forward_cursor DECLARE @running_total int, @ord int, @total int SET @running_total = 0 FETCH NEXT FROM forward_cursor INTO @ord, @total WHILE (@@FETCH_STATUS = 0) BEGIN SET @running_total = @running_total + @total INSERT @TotalTable VALUES(@ord, @total, @running_total) FETCH NEXT FROM forward_cursor INTO @ord, @total END CLOSE forward_cursor DEALLOCATE forward_cursor SELECT * FROM @TotalTable -- CPU 359, Reads 30392, Duration 496 </code></pre> <p>Test 4: </p> <pre><code>declare @total int set @total = 0 update #t set running_total = @total, @total = @total + total select * from #t -- CPU 0, Reads 58, Duration 139 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POCalculate a Running Total in SQL Server
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USSam Saffron
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCalculate a Running Total in SQL Server
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks. So your code sample is to demonstrate that it will sum in the order of the primary key, I presume. It would be interesting to know if cursors are still more efficient than joins for larger data sets.
      singulars
      1. PO
      1. UScodeulike
    2. COI'm sure you can find stats on google or your favorite search engine.. but the quick answer is that set-based operations are much faster than cursor operations when working with large sets of data.
      singulars
      1. PO
      1. USSam Axe
    3. CO@sambo99 - Thanks for all the extra detail
      singulars
      1. PO
      1. UScodeulike
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload