trying to find a more efficent way

Question

Post reply

trying to find a more efficent way

itmasterw 60042

SSCrazy

Points: 2852
More actions
November 28, 2014 at 7:35 pm

#295507

Hi,

I have to running totals like this at work, and this works; but it is not efficent since it does a su- select, which means that for each record it has to go through the sub query each time.

So I am woundering if there is another way to do this that would take out the sub select in the selct statement.

For example:

If we have a table like this

Product ID Sales

1 100

2 200

3 300

4 400

The the query below would return:

Product ID Sales runing total

1 100 100

2 200 300

3 300 600

4 400 1000

I was thinking of a inline view in the from section but I cannot fget that to work.

This can be done with temp tables orwhatever would amke this work.

So I was wonder if anyone had a more efficent was to do this.

Thank you

My code:

Select a.product_ID, a.Sales, (Select Sum(b.Sales)

From Monthlysales b

Where b.product_ID <= a.product_ID)

From Monthlysales a

order by a.product_ID

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Take a look at the first link under "Helpful Links" in my signature line below. For this one, we're going to need to know the actual table structure is and some sample data (see the link for how to post it correctly).

It would also be helpful to know how many rows you expect and whether or not we can split the query up or not. For example, are you allowed to use Temp Tables or not?

--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)
Intro to Tally Tables and Functions

pietlinden SSC Guru Points: 63198 More actions · Answer 2

One option is to do this with a windowing function, like this:

SELECT ProductID



	, Sales

	, SUM(Sales) OVER (ORDER BY Sales

						ROWS BETWEEN UNBOUNDED PRECEDING

						AND CURRENT ROW) AS RunningTotal

FROM (SELECT 1 AS ProductID, 100 As Sales

UNION ALL

SELECT 2, 200

UNION ALL

SELECT 3, 300

UNION ALL

SELECT 4, 400) a;

Everything between the parentheses is just a virtual table to test against. You would use a query against your table there.

itmasterw 60042 SSCrazy Points: 2852 More actions · Answer 3

itmasterw 60042

SSCrazy

Points: 2852

November 29, 2014 at 10:29 am

#1761517

Hi,

And thanks for the alternate way of doing this, but when I compard the execution plans the cost was about the same; I was looking for a more efficent way. Which I think it woudl come, in some way by taking the processing out of the select part of the statement.

Thanks

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

itmasterw 60042 (11/29/2014)

Hi,

And thanks for the alternate way of doing this, but when I compard the execution plans the cost was about the same; I was looking for a more efficent way. Which I think it woudl come, in some way by taking the processing out of the select part of the statement.

Thanks

Looking at execution plan cost is not the way to determine if something runs faster. I could show you two queries where the execution plan says it will take 0% when compared to the other when the opposite is actually true.

If the query that pietlinden showed you actually worked, then you're not using SQL Server 2008 because his method didn't show up until 2012. What version of SQL Server are you actually using?

--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)
Intro to Tally Tables and Functions

pietlinden SSC Guru Points: 63198 More actions · Answer 5

pietlinden

SSC Guru

Points: 63198

November 29, 2014 at 11:08 am

#1761520

Sorry, can't remember if any windowing functions were available in 2008R2. Haven't used 2008 in an age (partly because windowing functions are super handy!)

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

pietlinden (11/29/2014)

Sorry, can't remember if any windowing functions were available in 2008R2. Haven't used 2008 in an age (partly because windowing functions are super handy!)

Not your fault... With the way the MS has been trickling the windowing functions in over the years, it's hard to remember which ones came when. The only reason why I remember this one is because running totals are a near'n'dear subject for me. 🙂

--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)
Intro to Tally Tables and Functions

aaron.reese SSChampion Points: 13449 More actions · Answer 7

aaron.reese

SSChampion

Points: 13449

November 30, 2014 at 2:00 pm

#1761569

Normally running totals should be delegated to the reporting application; they infer a sequence which goes against Set Theory.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

aaron.reese (11/30/2014)

Normally running totals should be delegated to the reporting application; they infer a sequence which goes against Set Theory.

You've touch a "sweet" spot for me, Aaron. I have to admit that I'm torn in two when someone says something of that nature. On the one hand, I absolutely agree. Things like this generally "should be done in the app" just like formatting dates and currency generally "should be done in app".

On the other hand, consider things like having 10 years worth of static (once it's been entered) data per user and what a pain it is to display a running total for any given month via "the app". Even if you have "balance-stops" embedded in a table somewhere, doing it "in the app" may still not be the best use. If the data is truly static (and it frequently is), then why recalculate the running total all the time? In some cases (a lot of cases, actually), it's far better to have that data, which is also as static as the base data, be stored in the database in a static fashion. "It Depends".

Since I teach and advocate a lot of methods to get away from the use of While Loops, Cursors, and other RBAR methods such as certain rCTEs, most will drop their jaw when they hear me say that sometimes the job requires you to get away from Set Theory in SQL. That's part of the reason the built in the "previous row", Lead, and Lag functionality into some of the Windowing functions in 2012 and why it's been in the ANSI standard for quite a while. Sometimes it's not for a "reporting application" or sometimes a query IS the "reporting application". 🙂

I also generate HTML using T-SQL for formatting output for emails for morning reports, use xp_CmdShell to call Powershell (really handy, by the way), and do some remarkable ETL without ever uttering the letters "SSIS", all of which are "anti-patterns according to "best practices". It's not that I'm trying to break everyone's best practices. It sometimes just makes sense to deviate depending on the task at hand and that includes the sometimes Draconian presumption that everything in SQL Server needs to be "Set Theory".

If someone wanted to format a date in SQL for use in the front end for a web site or to store in a table, my answer would probably be "Hell NO!" but if someone wanted to format a date in T-SQL as an ISO date for inclusion in a transmittal file, my answer would be "Hell YES!".

Heh... and then there's XML and a thousand other sins (like a "push stack" to convert Adjacency Lists to Nested Sets or an rCTE to generate dates, etc, etc) that people (some are some pretty heavy hitters in the world of SQL) use and condone every day that blows "Set Theory" all to pieces.

--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)
Intro to Tally Tables and Functions

itmasterw 60042 SSCrazy Points: 2852 More actions · Answer 9

itmasterw 60042

SSCrazy

Points: 2852

November 30, 2014 at 4:56 pm

#1761578

Thanks that makes sennce.

thnak you.

itmasterw 60042 SSCrazy Points: 2852 More actions · Answer 10

Just curious, this would be more efficient if it were possable to do the sub query in a inline view.

Does anyone know if that is possable in anyway?

I cannot get this to work, but I am looking for something like this.

Select a.product_ID,

a.Sales,

sb.Sales

From Monthlysales a

INNER JOIN

(Select Sum(b.Sales)

From Monthlysales b

Where b.product_ID <= a.product_ID

) sb

On a.product_ID = sb.a.product_ID

order by a.product_ID