running sum cursor

Question

running sum cursor

Viewing 15 posts - 16 through 30 (of 34 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

managed to get both methods working...10006 rows later, update was faster..by 11 seconds!
means I can do away with the cursor for now...I will still keep it though as it might just come in handy later on...I will post the final findings of the entire thing for anyone's else future reference.

That should be proof enough to throw the cursor away and never look back. 10 rows is nothing. Imagine if this was 1 million. The most helpful thing a cursor can teach you is how NOT to use them. There are times when a cursor is required. These are few and far between and are typically either email or administrative tasks. There is no need for a cursor when updating, inserting, deleting or retrieving data.

It would be great if you could post the actual execution times for both methods. That way others who stumble in here will also realize that the cursor is painfully slow.

Thanks for much for posting back your findings. It is hard to tell timing on small test datasets. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sdhanpaul Right there with Babe Points: 787 More actions · Answer 2

Well I can think of a senairo where cursor would actually be better...

Suppose you have a large database and lots of users. Using the update method, I doubt that two users can pull the same report...running sum, at the same time.

I read a lot of topics on cursors n no one likes them. The best solution to this runnin sum problem is clrsql but I haven't started doing that yet.

In terms of running sum methods, these two are best.

I don't like the self join and i don't like nested subquery....too slow!

There is one method that i saw someone posting about ... Gonna have to try for the purpose of learning really... Cause I already got what i wanted. Everything else in my databae is pretty simple...for me but you take a look at some of the queries.... You'd get lost!

sdhanpaul Right there with Babe Points: 787 More actions · Answer 3

Ps.... I never took a computer class in my entire life so it means a lot to me that you helped.

N I only started SQL server three days ago...

Thanks.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

Suppose you have a large database and lots of users. Using the update method, I doubt that two users can pull the same report...running sum, at the same time.

I can't see that there would be any issues at all with this. What problems do you envisage?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

sdhanpaul Right there with Babe Points: 787 More actions · Answer 5

what the quirky query is doing is that it is update a column in the table called running total...suppose two users hit previewing the a running total report out of that same table at the same time...

the quirky query will run for both users...might cause a crash...in addition, you gotta make sure that the data is in the right order...

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

sdhanpaul (10/23/2012)
what the quirky query is doing is that it is update a column in the table called running total...suppose two users hit previewing the a running total report out of that same table at the same time...
the quirky query will run for both users...might cause a crash...in addition, you gotta make sure that the data is in the right order...

That is why there is a table hint TABLOCKX. The second person would have to wait until the first query releases the lock.

http://msdn.microsoft.com/en-us/library/ms187373.aspx

I can think of exactly 0 reasons why you would want to use a cursor over the quirky update for this process. It is faster, easier to understand, and easier to maintain.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sdhanpaul Right there with Babe Points: 787 More actions · Answer 7

im not quite sure that one person would want to wait until the first person finishes with the data before running his report.

I am not saying that i would prefer to use cursor over the update...I am working around getting a permanent solution for the sorting problem...Auto Id seems like it will cause a bit of confusion later on...

Also, to allow persons to view the report simultaneously i figure that after the report is loaded, there is no need for the query to run anymore. so you can kill the query, have the report loaded and the other persons can go about his business. hence lock time, 5 seconds, max.

I have another problem but I think I wait another 24 hrs before i asked you...dont want you to think that i am getting bottle fed...

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

There's a problem with the quirky update posted here (as far as I remember).

You're mssing the clustered index. You must ensure that you have it.

For the concurrency problem, you can use a temp table (#Table). This way they'll both have an independent set of data.

All this is mentioned within the article.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sdhanpaul Right there with Babe Points: 787 More actions · Answer 9

I'll keep using this thread to post updates as I go along for anyone who is like me...have no idea what I am getting into and its all just trial and errors...

so...when you save the quirky update in SSME 2005 it saves as a .sql file...how the heck do i get that to run in MS Access Project file...(front end)?

Well, I figured that an update query and all, I'd create a stored procedure as an update query...and came up with this

ALTER PROCEDURE dbo.[RunningTotals-SET] --notice the position of the original code...took me several hrs to figure this out.

AS /*************************************************************************************

Pseduo-cursor Running Total update using the "Quirky Update" takes about 4 seconds

on my box.

*************************************************************************************/

--===== Supress the auto-display of rowcounts for speed an appearance

SET NOCOUNT ON

--===== Declare the working variables

DECLARE @rawtrxid INT

DECLARE @forcount int

declare @emppin int

declare @runningtotal int

declare @trxdate datetime

--===== Update the running total for this row using the "Quirky Update"

-- and a "Pseudo-cursor"

UPDATE dbo.rawtrx

SET @RunningTotal = RunningTotal = CASE

WHEN emppin = @emppin and trxdate = @trxdate

THEN @RunningTotal+forcount

ELSE forcount

END,

@emppin = emppin, @trxdate = trxdate

FROM dbo.rawtrx WITH (TABLOCKX)

OPTION (MAXDOP 1) --notice to take out the word GO

sdhanpaul Right there with Babe Points: 787 More actions · Answer 10

Okay, well I am sort of like Columbus with this whole server thing...

anyone else who has the same problem...here is your solution:

SQL Server 2005. Management Studio Express. Front End - MS Access 2007

Update and other queries run great in management studio but when i try to run that quirky update query it stopped after a certain number of records...

Solution: MS Access ADP files (not sure if other formats do this too) returns by default 10,000 records...that tricked me cause the update query did more than 10,000...anyways, by setting the number of records returned by the query / table / whatever to 0, you get unlimited...

Click on the office button==> Access Options ==> Advanced ==> right down at the bottom Advanced. Change to 0.

Problem solved.

sdhanpaul Right there with Babe Points: 787 More actions · Answer 11

Okay, I need your expert advice which can only come from years of experience...

I have a list of stored procedures...

I only just realized that I can group a set of stored procedures which run in a particular sequence.

Should I group these procedures?

Would it make the database run faster if I had them grouped rather than running them individually?

I'd hate to know that I go ahead and group them and it comes back to bite me in the *** later on.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

You don't need to do any of this because you're using an Access front end. IIRC, Access has running total capabilites built right in. It's been more than a decade since I've used Access but I'm sure that if you do a search for it, you'll find "Running Total" in Access help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sdhanpaul Right there with Babe Points: 787 More actions · Answer 13

Hi Jeff,

Thanks for the reply.

I got the whole running total thing sorted out...I have a running sum setup in ms access but what this sql server update is doing for me in 15 seconds, MS Access would take weeks! trust me, I know...

Right now, I really want to know if I should group the stored procedures or have them run individually.

I read an article about the pros of grouping them but afraid of the con...cant run it individually after...

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 14

sdhanpaul (10/23/2012)
Hi Jeff,
Thanks for the reply.
I got the whole running total thing sorted out...I have a running sum setup in ms access but what this sql server update is doing for me in 15 seconds, MS Access would take weeks! trust me, I know...
Right now, I really want to know if I should group the stored procedures or have them run individually.
I read an article about the pros of grouping them but afraid of the con...cant run it individually after...

Something is really wrong here. The Quirky Update will do a running total or a grouped running total on a million rows in 4 seconds flat even on my 10 year old single cpu 1.8ghz 1gb ram desktop box.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RocksteadyDev SSC Veteran Points: 220 More actions · Answer 15

T-SQL 2012 has a new built in function to give a running total, so you don't need a cursor for running totals that sum distinct counts. Previously sum and count functions could not be nested. Here is an example.

SELECT date, sum(count(DISTINCT unique_id)) OVER (ORDER BY date) AS total_per_date

FROM dbo.table

GROUP BY date