SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculate the Running Total for the last five Transactions


Calculate the Running Total for the last five Transactions

Author
Message
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30553 Visits: 9671
I must admit I was wrong on this one....


USE tempdb
GO
CREATE TABLE dbo.Accounts
(
ID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
TransactionDate datetime,
Balance float
)
go
DECLARE @i as INT
set @i = 0

while @i < 1000000
begin
insert into dbo.Accounts (TransactionDate, Balance) SELECT DATEADD(DD, @i, GETDATE()), CEILING(RAND() * 999)
SET @i = @i + 1
end
--SELECT * FROM dbo.Accounts
go
--activate execution plans before running this one
go
SELECT Accounts.ID, CONVERT(varchar(50),TransactionDate,101) AS TransactionDate, Balance,
( SELECT Sum( Balance )
FROM ( SELECT Top 5 A.Balance
FROM Accounts A
WHERE A.ID <= Accounts.ID
ORDER BY ID DESC
) AS A
) AS RunningTotal
FROM dbo.Accounts
go
DROP TABLE dbo.Accounts





That 1M rows query only processes 95MB of data (from stats in actual plan). I was expecting some sort of triangular join here, but the speed is really not all that bad. My statement returns the 1M rows in les than 8 sec (download included)... so that version might very well make it to production.

Thanks pp.pragnesh
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30553 Visits: 9671
... of course the cost of this query would be quite more considerable (maybe 2 or 3 times) if this was a real table with 1000 bytes of data and if the clustered index was on the date column is it probably would. Just something to keep in mind when using in real life.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91336 Visits: 41151
Hugo Kornelis (11/28/2008)


Yes, I agree... you cannot rely on anything other than an OrderBy on Select statements.

And, yes... I believe that everyone knows that "deprecation" comes with plenty of warnings over at least 1 version and frequently more.

I'll have to look for the deprecation warning on the alias = expression thing, but I do remember it being in the MS documentation for 2k5... I just can't remember where. Might have been in the "what's new" bit of BOL, but I don't know for sure. I'll look for it when I get a bit of time.

All of the things you say are mostly true... everything you say about a change, could actually happen. But, the "quirky" update and the way it operates has been in existance since before the beginning and hasn't changed because it's at the root of the engine. You can certainly post your warnings about the risk, but I do think they are a bit on the over cautious side... psuedo-cursors of this nature have been around for a very long time.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91336 Visits: 41151
Ninja's_RGR'us (11/28/2008)
I must admit I was wrong on this one....
That 1M rows query only processes 95MB of data (from stats in actual plan). I was expecting some sort of triangular join here, but the speed is really not all that bad. My statement returns the 1M rows in les than 8 sec (download included)... so that version might very well make it to production.

Thanks pp.pragnesh


Actually, triangular joins are present... the thing is that they're very very small... limited to 4 or 5 rows each and they "stop" when the rules of distinction have been met in this case. Like I said in the article I wrote about triangular joins and just like most anything else... they're not ALL bad... "It Depends".

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30553 Visits: 9671
Thanks for the correction Jeff... you clearly said what I meant silently Tongue.


A 5 row triangular join won't kill many servers.... but what if the report needs to return the running total for @X days... that could quickly because a huge bottleneck for the server. I have no time to test now, but my guess is that going from 5 to even only 15 days could really hurt that query!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91336 Visits: 41151
Heh... Read it? I wrote it! Wink

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30553 Visits: 9671
Jeff Moden (11/28/2008)
Heh... Read it? I wrote it! Wink


Somebody deleted some posts because that message is not making any sense now!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

Group: General Forum Members
Points: 91336 Visits: 41151
That would be true. Some good fellow had the link to my Triangular join article with the comment that folks should take a gander. I didn't mean to scare him away with my comment. Blush

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30553 Visits: 9671
I know what you mean, I used to take all those comments as they weredirected straight at me some time... forgetting the nature of the boeard Smile... but that's makes you so god Wink.
arkhan
arkhan
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 44
One line statement:-

SELECT ID,TransactionDate,Balance,CASE WHEN id<5 THEN 0 ELSE(select SUM(balance) FROM Accounts where ID between acc.id-4 and acc.id) end as RunningTotal from Accounts acc

The Network rtraffic would be lesser, as per below statistics:-

Network Statistics
Number of server roundtrips 1 1.0000
TDS packets sent from client 1 1.0000
TDS packets received from server 1 1.0000
Bytes sent from client 378 378.0000
Bytes received from server 771 771.0000
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search