cursors and running totals - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

cursors and running totals

Author

Topic

gallff
Starting Member

5 Posts

Posted - 2004-09-03 : 07:30:43

Hi!

I'm having trouble getting the running totals right. I would like to have a table with probabilities up to 100, but for some reason I end up with 110 on the last row. It seems like only the last row is wrong too which makes me even more confused. Could it be that the cursor is updating the last field twice?? If anybody could help I would really appreciate it!

BR Gallff

--First I create a temp table
create table #playtile
(
matchplayprobabilitiesID int,
receivingtile int,
probabilitypercentage int,
modifier int,
probpercentacclow int,
probpercentacchigh int)

insert into #playtile
select matchplayprobabilitiesid, receivingtile, probabilitypercentage, 0, 0, 0
from matchplayprobabilities
where currenttile=2 and teamplay=1
--------------------------------------------

This is the result of the temp table:
matchplayprobabilitiesid receivingtile probabilitypercentage
------------------------ ------------- --------------------- ----------- ----------- -----------
1 3 10 0 0 0
2 4 5 0 0 0
3 5 10 0 0 0
4 6 25 0 0 0
5 7 10 0 0 0
6 8 10 0 0 0
7 9 10 0 0 0
8 10 10 0 0 0
9 11 10 0 0 0

(9 row(s) affected)
-----------------------------------------------------

--then I use an updating cursor to update the last two columns
declare @MatchPlayProbID int
declare @receivingtile int
declare @probpercentage int
declare @modifier int
declare @probpercentacclow int
declare @probpercentacchigh int
declare @runningtotal int

set @runningtotal=0

DECLARE SettingRunningTotalCursor CURSOR FOR
SELECT matchplayprobabilitiesID, receivingtile, probabilitypercentage, modifier,
probpercentacclow, probpercentacchigh
FROM #playtile
OPEN SettingRunningTotalCursor

FETCH NEXT FROM SettingRunningTotalCursor into @MatchPlayProbID, @receivingtile, @probpercentage,
@modifier, @probpercentacclow, @probpercentacchigh
UPDATE #playtile SET probpercentacchigh = probabilitypercentage+modifier
WHERE matchplayprobabilitiesID=@matchplayprobID

WHILE @@FETCH_STATUS = 0
begin
set @runningtotal=@runningtotal+@probpercentage+@modifier

FETCH NEXT FROM SettingRunningTotalCursor
into @MatchPlayProbID, @receivingtile, @probpercentage, @modifier, @probpercentacclow,
@probpercentacchigh

UPDATE #playtile SET probpercentacclow = @runningtotal+1
WHERE matchplayprobabilitiesID=@matchplayprobID

UPDATE #playtile SET probpercentacchigh = probabilitypercentage+@runningtotal+modifier
WHERE matchplayprobabilitiesID=@matchplayprobID

end

close SettingRunningTotalCursor
deallocate SettingRunningTotalCursor

--Only a select from the temp table...
SELECT matchplayprobabilitiesID, receivingtile, probabilitypercentage, modifier,
probpercentacclow, probpercentacchigh
FROM #playtile

--...and this is the result from the select
matchplayprobabilitiesID receivingtile probabilitypercentage modifier probpercentacclow probpercentacchigh
------------------------ ------------- --------------------- ----------- ----------------- ------------------
1 3 10 0 0 10
2 4 5 0 11 15
3 5 10 0 16 25
4 6 25 0 26 50
5 7 10 0 51 60
6 8 10 0 61 70
7 9 10 0 71 80
8 10 10 0 81 90
9 11 10 0 101 110

(9 row(s) affected)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-03 : 08:55:23

OR you COULD just do something like this instead of the cursor and the temp table:


select matchplayprobabilitiesid, receivingtile, probabilitypercentage,
       ProbPct - ProbabilityPercentage as ProbPercentAccLow,
       ProbPct as ProbPercentAccHigh
from
(
  select mpp.*, 
    (select sum(ProbabilityPercentage)
     from MatchPlayProbabilities B
     where B.CurrentTile = mpp.CurrentTile AND
         B.TeamPlay = mpp.TeamPlay AND
         B.MatchPlayProbabilitiesID <= mpp.MatchPlayProbabilitiesID
     ) as ProbPct
  from 
    matchplayprobabilities mpp
  where 
    currenttile=2 and teamplay=1
) a

something like that -- without knowing you requirements exactly I don't know for sure.

- Jeff

gallff
Starting Member

5 Posts

Posted - 2004-09-03 : 16:35:19

That works great! Thanks a lot! Now I just have to spend a couple of hours figuring out exactly what your magic code does

However, the strange behavior with the cursor still interests me. Do you or anybody else know where to read more about cursors?

Gallf

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-04 : 07:56:09

A stored procedure without a cursor is like a fish without a bicycle.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-04 : 10:15:20

In general cursors are done like so:


Declare cursor

Fetch 1st row to prime variables

WHILE more rows
BEGIN
   perform calcs

   Fetch Next
END

You are doing

Declare cursor

Fetch 1st row

perform calcs   -- (a)

WHILE more rows
BEGIN

   Fetch next  -- (b)

   perform calcs -- (c)

END

As you can see, the while condition is not being tested until AFTER the final fetch has failed and calcs are performed, so you end up performing the calculations twice for the final row.

You can fix it by removing (a) and swapping the order of (b) and (c)

Of course the Doctors solution is even better.
Or, for another interesting solution:



declare @probpercentacclow int
declare @probpercentacchigh int
declare @runningtotal int

UPDATE #playtile 
SET 	@probpercentacchigh = probpercentacchigh = IsNull(@runningtotal,0) + probabilitypercentage,
	@probpercentacclow = probpercentacclow = IsNull(@runningtotal + 1,0),
	@runningtotal = IsNull(@runningtotal,0) + probabilitypercentage + modifier

SELECT matchplayprobabilitiesID, receivingtile, probabilitypercentage, modifier, 
probpercentacclow, probpercentacchigh
FROM #playtile

matchplayprobabilitiesID receivingtile probabilitypercentage modifier    probpercentacclow probpercentacchigh 
------------------------ ------------- --------------------- ----------- ----------------- ------------------ 
1                        3             10                    0           0                 10
2                        4             5                     0           11                15
3                        5             10                    0           16                25
4                        6             25                    0           26                50
5                        7             10                    0           51                60
6                        8             10                    0           61                70
7                        9             10                    0           71                80
8                        10            10                    0           81                90
9                        11            10                    0           91                100

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

gallff
Starting Member

5 Posts

Posted - 2004-09-05 : 14:22:00

Hi!

Thanks a lot, now I changed my code and it works well. Thanks to you I can pick any of three alternative ways of doing it - and I have got a better understanding of cursors.

Thanks again!

Gallff

P.S. Here's a copy of the modified code (admittedly the worst coding practise of the three...

)

--updating cursor
declare @MatchPlayProbID int
declare @receivingtile int
declare @probpercentage int
declare @modifier int
declare @probpercentacclow int
declare @probpercentacchigh int
declare @runningtotal int

set @runningtotal=0

DECLARE SettingRunningTotalCursor CURSOR FOR
SELECT matchplayprobabilitiesID, receivingtile, probabilitypercentage, modifier,
probpercentacclow, probpercentacchigh
FROM #playtile
OPEN SettingRunningTotalCursor

FETCH NEXT FROM SettingRunningTotalCursor
into @MatchPlayProbID, @receivingtile, @probpercentage, @modifier, @probpercentacclow,
@probpercentacchigh

WHILE @@FETCH_STATUS = 0
begin

UPDATE #playtile SET probpercentacclow = @runningtotal+1
WHERE matchplayprobabilitiesID=@matchplayprobID

UPDATE #playtile SET probpercentacchigh = probabilitypercentage+@runningtotal+modifier
WHERE matchplayprobabilitiesID=@matchplayprobID

set @runningtotal=@runningtotal+@probpercentage+@modifier

FETCH NEXT FROM SettingRunningTotalCursor
into @MatchPlayProbID, @receivingtile, @probpercentage, @modifier, @probpercentacclow,
@probpercentacchigh
end

close SettingRunningTotalCursor
deallocate SettingRunningTotalCursor

SELECT matchplayprobabilitiesID, receivingtile, probabilitypercentage, modifier,
probpercentacclow, probpercentacchigh
FROM #playtile

gallff
Starting Member

5 Posts

Posted - 2004-09-05 : 15:26:10

Just so that I understand it completely:

The
WHILE @@FETCH_STATUS = 0

only checks if there has been a fetch that has failed, it doesn't really check if there are any more rows. Is that correct?

/Gallff

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-05 : 16:18:07

quote:
Originally posted by gallff

Just so that I understand it completely:

The
WHILE @@FETCH_STATUS = 0

only checks if there has been a fetch that has failed, it doesn't really check if there are any more rows. Is that correct?

/Gallff

Right. I was just generalizing.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-05 : 16:27:59

For what it's worth, do you realize the tremendous overhead CURSORS incur over SET-BASED statements? For a table with 100,000 rows, your cursor will probably take 10 minutes to run. Jeff's suggestion would probably run in 45 seconds. My last suggestion (while not quite "standard") would probably run in 10 seconds. This means not only end-user response time, but server CPU and disk uitilization. If you plan on doing a lot of SQL stored procedure work it would serve you greatly to gain a better understanding of set-based principles. There are VERY FEW operations done with cursors than can not be done better without them.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-05 : 17:03:30

http://www.nigelrivett.net/Cursors.html
http://www.nigelrivett.net/BadThings.html

Not that I'm opionated at all.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

gallff
Starting Member

5 Posts

Posted - 2004-09-06 : 03:23:04

I know that cursors in general shouldn't be used, and my previous experience with them are that they are really slooow, as you are saying.

But, inspiration came from this article here at SQLteam.com:
http://www.sqlteam.com/item.asp?ItemID=3856
And if I have understood that article correctly cursors may be a faster alternative when calculating running totals?

/Gallff

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-09-06 : 04:13:41

Point well taken. I had forgotten a dialog between Jeff and I about generating just this kind of running total against a sizable table. The corelated subquery solution can indeed be quite slow. For what it's worth, in the example from that article, adding 50,000 instead of 5,000 rows of data resulted in the cursor solution finishing in 17 seconds. The technique I posted earlier applied to that problem:

ALTER Table Sales Add RunningTotal money

DECLARE @RunningTotal money
SET @RunningTotal = 0

UPDATE Sales
SET @RunningTotal = RunningTotal = Sales + @RunningTotal

completes in less than 2 seconds. As I said though, it is not quite "standard" in that it depends on updateing all rows and there must be a clustered index, but in both cases (yours and the Sales example from the article) those are true conditions. I was going to run the Solution 2: The "Celko" Solution, on 50,000 rows, but cancled it after 30 minutes. :)

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources