July 10, 2012 at 2:13 am
Yes, i believe i provided wrong set of data for example, i wanted to update a table for a newly added column "Balance"
I wanted a single update query which can update all rows in cumulative ways, like bank balance statement.
Means second row (Balance) should have total of 1st and 2nd amount in deposit column
3rd row should have total of 1st + 2nd + 3rd amount deposit column and so on.
pls let me know possible way for it
Thanks alot
Ravi
July 10, 2012 at 2:14 am
Thanks for your replies
Yes, i believe i provided wrong set of data for example, i wanted to update a table for a newly added column "Balance"
I wanted a single update query which can update all rows in cumulative ways, like bank balance statement.
Means second row (Balance) should have total of 1st and 2nd amount in deposit column
3rd row should have total of 1st + 2nd + 3rd amount deposit column and so on.
pls let me know possible way for it
Thanks alot
Ravi
July 10, 2012 at 2:36 am
Thanks Rookie it worked. 🙂
July 10, 2012 at 5:23 am
ravigour2000 (7/10/2012)
Thanks Rookie it worked. 🙂
Hmm... So, because it worked on a set of 3 records you will accept it? Do you even know what that is doing? Do you know what a cartesian product is? Do you understand triangular joins? Did you even bother to read the article I linked to?
Jared
CE - Microsoft
July 10, 2012 at 6:32 am
SQLKnowItAll (7/9/2012)
Jeff Moden (7/9/2012)
In 2008 or less, I'd REALLY like to know how to create a view that does a running total without forming a Triangular Join. I don't believe it's possible but sure am willing to learn a new trick.And there you go bringing up the actual topic of the OP's question... 🙂
The original question isn't about a view. It's about storing a running total in a table, using an update. Doesn't even have the word "view" in it.
And you can do wicked fast running totals in SQL 2005 and later with CLR functions. No views needed.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 10, 2012 at 6:53 am
Jeff Moden (7/9/2012)
In 2008 or less, I'd REALLY like to know how to create a view that does a running total without forming a Triangular Join. I don't believe it's possible but sure am willing to learn a new trick.
USE tempdb
GO
DROP TABLE Simpletable
SELECT id, aNumber = ABS(CHECKSUM(NEWID()))%50
INTO Simpletable
FROM (SELECT TOP 100 id = ROW_NUMBER() OVER(ORDER BY name) FROM sys.columns) d
GO
CREATE VIEW [dbo].[vwRunningTotals]
AS
WITH Calculator AS (
SELECT id, aNumber, RunningTotal = aNumber
FROM Simpletable
WHERE id = 1
UNION ALL
SELECT tr.id, tr.aNumber, RunningTotal = lr.RunningTotal + tr.aNumber
FROM Calculator lr
INNER JOIN Simpletable tr ON tr.id = lr.id + 1
) SELECT id, aNumber, RunningTotal
FROM Calculator
--OPTION (MAXRECURSION 0) -- plan guide
GO
SELECT * FROM [dbo].[vwRunningTotals] WHERE id = 32
😉
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 10, 2012 at 7:22 am
GSquared (7/10/2012)
SQLKnowItAll (7/9/2012)
Jeff Moden (7/9/2012)
In 2008 or less, I'd REALLY like to know how to create a view that does a running total without forming a Triangular Join. I don't believe it's possible but sure am willing to learn a new trick.And there you go bringing up the actual topic of the OP's question... 🙂
The original question isn't about a view. It's about storing a running total in a table, using an update. Doesn't even have the word "view" in it.
And you can do wicked fast running totals in SQL 2005 and later with CLR functions. No views needed.
True 🙂 but it was about a running total, and Lowell's next response (no offence Lowell, just tracking events) was about using views to do the calculation and not updating a static table.
I think the real issue here is he running total and how to most efficiently execute it during an update. That being said, I refer the OP to the original post I cited by Jeff. http://www.sqlservercentral.com/articles/T-SQL/68467/
Jared
CE - Microsoft
July 10, 2012 at 7:54 am
ChrisM@Work (7/10/2012)
Jeff Moden (7/9/2012)
In 2008 or less, I'd REALLY like to know how to create a view that does a running total without forming a Triangular Join. I don't believe it's possible but sure am willing to learn a new trick.
USE tempdbGO
DROP TABLE Simpletable
SELECT id, aNumber = ABS(CHECKSUM(NEWID()))%50
INTO Simpletable
FROM (SELECT TOP 100 id = ROW_NUMBER() OVER(ORDER BY name) FROM sys.columns) d
GO
CREATE VIEW [dbo].[vwRunningTotals]
AS
WITH Calculator AS (
SELECT id, aNumber, RunningTotal = aNumber
FROM Simpletable
WHERE id = 1
UNION ALL
SELECT tr.id, tr.aNumber, RunningTotal = lr.RunningTotal + tr.aNumber
FROM Calculator lr
INNER JOIN Simpletable tr ON tr.id = lr.id + 1
) SELECT id, aNumber, RunningTotal
FROM Calculator
--OPTION (MAXRECURSION 0) -- plan guide
GO
SELECT * FROM [dbo].[vwRunningTotals] WHERE id = 32
😉
Works so long as your table has 101 or less rows in it. You can move the MaxRecursion option to the final query (won't work in creating a View, but will work in querying the View), to make it work on larger datasets, but watch out for really poor performance.
Try creating your Simpletable with 1-million rows, and try the final query as:
SELECT *
FROM [dbo].[vwRunningTotals]
WHERE id <= 10000
OPTION (MAXRECURSION 0) ;
I've got a pretty high-power PC, would be a server for most small businesses, and I gave up after 2 minutes on the final query, hit "cancel", and then had to wait a while for it to roll back. It's still rolling back 5 minutes later.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 10, 2012 at 8:02 am
GSquared (7/10/2012)
ChrisM@Work (7/10/2012)
Jeff Moden (7/9/2012)
In 2008 or less, I'd REALLY like to know how to create a view that does a running total without forming a Triangular Join. I don't believe it's possible but sure am willing to learn a new trick.
USE tempdbGO
DROP TABLE Simpletable
SELECT id, aNumber = ABS(CHECKSUM(NEWID()))%50
INTO Simpletable
FROM (SELECT TOP 100 id = ROW_NUMBER() OVER(ORDER BY name) FROM sys.columns) d
GO
CREATE VIEW [dbo].[vwRunningTotals]
AS
WITH Calculator AS (
SELECT id, aNumber, RunningTotal = aNumber
FROM Simpletable
WHERE id = 1
UNION ALL
SELECT tr.id, tr.aNumber, RunningTotal = lr.RunningTotal + tr.aNumber
FROM Calculator lr
INNER JOIN Simpletable tr ON tr.id = lr.id + 1
) SELECT id, aNumber, RunningTotal
FROM Calculator
--OPTION (MAXRECURSION 0) -- plan guide
GO
SELECT * FROM [dbo].[vwRunningTotals] WHERE id = 32
😉
Works so long as your table has 101 or less rows in it. You can move the MaxRecursion option to the final query (won't work in creating a View, but will work in querying the View), to make it work on larger datasets, but watch out for really poor performance.
Try creating your Simpletable with 1-million rows, and try the final query as:
SELECT *FROM [dbo].[vwRunningTotals]
WHERE id <= 10000
OPTION (MAXRECURSION 0) ;
I've got a pretty high-power PC, would be a server for most small businesses, and I gave up after 2 minutes on the final query, hit "cancel", and then had to wait a while for it to roll back. It's still rolling back 5 minutes later.
A unique clustered index on id dramatically changes the performance, and IIRC the MAXRECURSION option can be attached to a plan guide.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 10, 2012 at 8:28 am
The prior attempt is still rolling back, so I modified the script as follows:
USE tempdb
GO
IF OBJECT_ID(N'tempdb..Simpletable2') IS NOT NULL
DROP TABLE Simpletable2 ;
IF OBJECT_ID(N'tempdb..vwRunningTotals2') IS NOT NULL
DROP VIEW vwRunningTotals2 ;
SELECT id,
aNumber = ABS(CHECKSUM(NEWID())) % 50
INTO Simpletable2
FROM (SELECT TOP 1000000
id = ROW_NUMBER() OVER (ORDER BY t1.name)
FROM sys.columns AS t1
CROSS JOIN sys.columns AS t2
CROSS JOIN sys.columns AS t3) d ;
GO
ALTER TABLE Simpletable2
ALTER COLUMN id BIGINT NOT NULL;
GO
ALTER TABLE Simpletable2
ADD CONSTRAINT PK_Simpletable2 PRIMARY KEY (id);
GO
CREATE VIEW [dbo].[vwRunningTotals2]
AS
WITH Calculator
AS (SELECT id,
aNumber,
RunningTotal = aNumber
FROM Simpletable2
WHERE id = 1
UNION ALL
SELECT tr.id,
tr.aNumber,
RunningTotal = lr.RunningTotal + tr.aNumber
FROM Calculator lr
INNER JOIN Simpletable2 tr
ON tr.id = lr.id + 1)
SELECT id,
aNumber,
RunningTotal
FROM Calculator ;
-- plan guide
GO
SELECT *
FROM [dbo].[vwRunningTotals2]
WHERE id BETWEEN 50 AND 60
OPTION (MAXRECURSION 0) ;
Quering 10 rows out of a million-row table takes 10 seconds on my PC. (Quad-core i7 CPU, 16 Gig RAM, 64-bit Windows 7.)
Interestingly, querying rows 1,000 through 10,000 takes the same amount of time. Makes sense when you realize that any query of this view requires running the view on the whole dataset. Same result for rows 100,000 through 110,000, of course. Same again for rows 900,000 through 910,000.
A CLR function can do the same thing in a small fraction of a second on the same machine. Last time I tested like this, it took about 12 milliseconds to run a 100-thousand row running total on a million-row table.
So, it works (which satisfies Jeff's original challenge, even if the challenge was misinformed as to what was being asked for), but in production code, I'll still use the option that's almost 100 times faster than this.
Still, well done for a creative solution. Wouldn't have thought of this as an option. Definitely worth testing.
(And the original test, without a clustered index, is still rolling back.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 10, 2012 at 8:41 am
Just looked in Jeff's old article on running totals, and on his PC, the quirky-update running total on a million rows of data was about 6 seconds. Assuming margin-of-error for hardware differences, the recursive CTE method seems to be in the same performance-range (multiple seconds) as the quirky update, and doesn't depend on the complex rules that the quirky update requires in order to work at all reliably. Plus, it's all documented features and code, so no problems with possible code-breakage due to SQL updates.
Jeff would need to test it on the same hardware to confirm the speed and behavior.
Of course, part of the reason to use the quirky-update (pseudo-cursor) method is that it works on versions of SQL Server that CLR won't work on, and CTEs won't work on those versions either.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 10, 2012 at 8:47 am
GSquared (7/10/2012)
Just looked in Jeff's old article on running totals, and on his PC, the quirky-update running total on a million rows of data was about 6 seconds. Assuming margin-of-error for hardware differences, the recursive CTE method seems to be in the same performance-range (multiple seconds) as the quirky update, and doesn't depend on the complex rules that the quirky update requires in order to work at all reliably. Plus, it's all documented features and code, so no problems with possible code-breakage due to SQL updates.Jeff would need to test it on the same hardware to confirm the speed and behavior.
Of course, part of the reason to use the quirky-update (pseudo-cursor) method is that it works on versions of SQL Server that CLR won't work on, and CTEs won't work on those versions either.
Gus - your observations are consistent. Quite a lot of work has been done on the timings. At best the rCTE running total runs around 3.5 times slower than the QU, working on the same data set on the same machine. I'll try to pick out some of those posts if you're interested.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 10, 2012 at 8:55 am
ChrisM@Work (7/10/2012)
GSquared (7/10/2012)
Just looked in Jeff's old article on running totals, and on his PC, the quirky-update running total on a million rows of data was about 6 seconds. Assuming margin-of-error for hardware differences, the recursive CTE method seems to be in the same performance-range (multiple seconds) as the quirky update, and doesn't depend on the complex rules that the quirky update requires in order to work at all reliably. Plus, it's all documented features and code, so no problems with possible code-breakage due to SQL updates.Jeff would need to test it on the same hardware to confirm the speed and behavior.
Of course, part of the reason to use the quirky-update (pseudo-cursor) method is that it works on versions of SQL Server that CLR won't work on, and CTEs won't work on those versions either.
Gus - your observations are consistent. Quite a lot of work has been done on the timings. At best the rCTE running total runs around 3.5 times slower than the QU, working on the same data set on the same machine. I'll try to pick out some of those posts if you're interested.
Nah. My curiosity on it is satisfied.
I'm using CLR for both string parsing and running totals these days. No reason to adopt other methods. But playing around with something new (to me) is educational and fun, so I had some fun with it.
And the prior try is still rolling back. :w00t:
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 10, 2012 at 8:56 am
SQLKnowItAll (7/10/2012)
ravigour2000 (7/10/2012)
Thanks Rookie it worked. 🙂Hmm... So, because it worked on a set of 3 records you will accept it? Do you even know what that is doing? Do you know what a cartesian product is? Do you understand triangular joins? Did you even bother to read the article I linked to?
Perhaps that's a matter of semantics. A VIEW would require a "single query" whether it's an actual view or an "Inline view" such as a CTE or Derived Table. The OP's original question specifically stated...
i wanted[font="Arial Black"] a single UPDATE query[/font] which result cumulative data in last column
--Jeff Moden
Change is inevitable... Change for the better is not.
July 10, 2012 at 9:09 am
subbareddy542 (7/9/2012)
pls try below code declare @test-2 table (id varchar(10),name varchar(10),sal int,cum_sal int)insert into @test-2(id,name,sal)
select 'C001','Jack',100
union
select 'C002','Mick', 500
union
select 'C003','Rock', 200
--update stmt
update t1 set cum_sal=(select SUM(t.sal) from @test-2 t where t.id<=t1.id)
from @test-2 t1
select * from @test-2
Heh... YOU try it! Put it up against just 20,000 rows and see how long it takes and the incrredible number of resources that it will use. To see why that's such a bad idea, please read the following article. You'll be amazed.
http://www.sqlservercentral.com/articles/T-SQL/61539/
There are ways to index such a "Triangular Join" so that it actually does run fast but you'd better have plenty of memory and I/O had better be in really good shape because no matter what you do, it's still going to have to read 200,030,000 {(200002+20000)/2+20000} rows internally to get the job done on just 20,000 rows.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply