Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»

Calculate the Running Total for the last five Transactions Expand / Collapse
Author
Message
Posted Friday, November 28, 2008 4:48 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:52 AM
Points: 21,385, Visits: 9,601
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
Post #610254
Posted Friday, November 28, 2008 4:51 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:52 AM
Points: 21,385, Visits: 9,601
... 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.
Post #610260
Posted Friday, November 28, 2008 9:21 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #610447
Posted Friday, November 28, 2008 9:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #610449
Posted Friday, November 28, 2008 9:34 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:52 AM
Points: 21,385, Visits: 9,601
Thanks for the correction Jeff... you clearly said what I meant silently :P.


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!
Post #610453
Posted Friday, November 28, 2008 8:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
Heh... Read it? I wrote it! ;)

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #610628
Posted Saturday, November 29, 2008 5:13 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:52 AM
Points: 21,385, Visits: 9,601
Jeff Moden (11/28/2008)
Heh... Read it? I wrote it! ;)


Somebody deleted some posts because that message is not making any sense now!
Post #610682
Posted Saturday, November 29, 2008 7:48 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
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.

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #610699
Posted Saturday, November 29, 2008 10:30 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:52 AM
Points: 21,385, Visits: 9,601
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 :)... but that's makes you so god ;).
Post #610741
Posted Monday, December 1, 2008 5:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 16, 2010 5:44 AM
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
Post #611191
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse