Running Totals ARRGGHH so slow...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Running Totals ARRGGHH so slow...

Author

Topic

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2013-11-21 : 09:39:34

Hi,

I have a stock table with about 10 million rows in and I want to have a running total column, I've just tried:

SELECT a.[Date]
,a.[Item]
,a.[Colour]
,a.[Quantity] AS [Quantity]
,SUM(b.[Quantity]) AS [Running Quantity]

FROM Stock a,
stock b

WHERE a.[Item] = b.[Item]
AND a.[Colour] = b.[Colour]
AND b.[Date] <= a.[Date]

GROUP BY a.[Date]
,a.[Item]
,a.[Colour]
,a.[Quantity]

ORDER BY a.[Date]
,a.[Item]
,a.[Colour]

which is running really, really slow, searching the internet now and I have read this isn't the best way to do this, but seems there are different methods some users saying using something called a cursor others stay away from cursors.

What it is the best way to do this?
What is the Quickest way to do this?

Thanks,

Roger

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-21 : 09:41:52

whats the version you're using?
the above method causes Hidden RBAR which can be a performance killer.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-21 : 09:43:31

try

SELECT 
	 a.[Date]
	,a.[Item]
	,a.[Colour]
	,a.[Quantity] AS [Quantity]
	,c.[Running Quantity]
FROM 	Stock a
CROSS APPLY
(
	SELECT	[Running Quantity] = SUM([Quantity])
	FROM	stock b
	WHERE	b.[Item]	= a.[Item]
	AND	b.[Colour]	= a.[Colour]
	AND	b.[Date]	<= a.[Date]
) c
ORDER BY a.[Date]
	,a.[Item]
	,a.[Colour]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-21 : 09:44:02

try this too as an alternative


SELECT a.[Date]
,a.[Item]
,a.[Colour]
,a.[Quantity] AS [Quantity]
,b.[Running Quantity]

FROM Stock a
CROSS APPLY (SELECT SUM(Quantity]) AS [Running Quantity]
FROM stock 
WHERE [Item] = a.[Item]
AND [Colour] = a.[Colour]
AND [Date] <= a.[Date]
)b
ORDER BY a.[Date]
,a.[Item]
,a.[Colour]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2013-11-21 : 09:57:40

Hi I'm on SQL 2005.
Thanks will try the cross apply examples now.

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2013-11-21 : 10:14:40

Hi,

I have just done a test on one Item which has 95,000 rows in Stock table and visakh16 script took 8 seconds and khtan 10 seconds so now going to run it on the full 10 million rows... I suppose I should leave it running and grab a coffee as I guess this will take a while to run...

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2013-11-21 : 10:25:43

Hmmm looks like it has completed in only a few minutes on the whole table which is great, just checking the output now...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-21 : 10:53:53

quote:
Originally posted by rogerclerkwell

Hi,

I have just done a test on one Item which has 95,000 rows in Stock table and visakh16 script took 8 seconds and khtan 10 seconds so now going to run it on the full 10 million rows... I suppose I should leave it running and grab a coffee as I guess this will take a while to run...

actual both query are identical

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-21 : 10:54:22

what are the index on that table ?

KH
[spoiler]Time is always against us[/spoiler]

rogerclerkwell
Yak Posting Veteran

85 Posts

Posted - 2013-11-21 : 11:06:07

Hi Khan,

Sorry that post was misleading, I have now run both scripts on all the data now in the table and both performed the same so not sure why when I tested on one item earlier there was a few seconds difference.
Thanks both for your help the scripts are perfect.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-22 : 05:38:36

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources