quote:

---

Originally posted by nr

select columnA, columnB = (select sum(columnA) from tbl t2 where t2.columnA <= t.columnA)
from tbl t
order by columnA



==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---

select	t1.columnA,
	sum(t2.columnA) as columnB
from	tbl t1
	inner join tbl t2 on t1.columnA >= t2.columnA
group by t1.columnA
order by t1.columnA

quote:

---

Originally posted by mmarovicblindman: I don't have time to test now, but if i were you I wouldn't bet without testing. If I had to bet on one solution before testing (yours against cursor) I would bet on cursor.

---

quote:

---

Originally posted by X002548

Why don't you read the hint link in my sig and provide us with some specific details an what you need.

99.9% of the time a CURSOR is not the answer



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

---

quote:

---

Originally posted by blindman

quote:

---

Originally posted by mmarovicblindman: I don't have time to test now, but if i were you I wouldn't bet without testing. If I had to bet on one solution before testing (yours against cursor) I would bet on cursor.

---

Hey, if I knew the answer for certain, it wouldn't be gambling, would it? But I'll take the bet anyway. How about we play for a virtual Margarita?

---

create table tbl(
	columnA int
)
go
set noCount on
declare @i int
set @i = 1
while @i<=1000 begin
  insert into tbl(columnA) values(@i)
  set @i = @i+1
end
set noCount off
go

checkpoint
go
dbcc dropCleanBuffers
go
DBCC FREEPROCCACHE
go
declare @executionTime int
declare @start dateTime
set @start = getDate()
select columnA, columnB = (select sum(columnA) from tbl t2 where t2.columnA <= t.columnA)
from tbl t
order by columnA
select dateDiff(ms, @start, getDate())
--440
--330
--350
--440
--420
--440
--313
--303
--360
--390
--total=3786
go

checkpoint
go
dbcc dropCleanBuffers
go
DBCC FREEPROCCACHE
go
declare @executionTime int
declare @start dateTime
set @start = getDate()
select	t1.columnA,
	sum(t2.columnA) as columnB
from	tbl t1
	inner join tbl t2 on t1.columnA >= t2.columnA
group by t1.columnA
order by t1.columnA
select dateDiff(ms, @start, getDate())
--580
--290
--430
--440
--350
--380
--440
--433
--510
--410
--total=4263
go

checkpoint
go
dbcc dropCleanBuffers
go
DBCC FREEPROCCACHE
go
set noCount on
declare @executionTime int
declare @start dateTime
set @start = getDate()

declare @columnA int
declare @columnB int

declare @result table(columnA int primary key clustered, columnB int not null)

declare c cursor for select columnA from tbl order by columnA

open c

set @columnB = 0

while (1=1) begin
  fetch next from c into @columnA
  if @@fetch_status <> 0 break
  set @columnB = @columnB + @columnA
  insert into @result values(@columnA, @columnB)
end
close c
deallocate c

select * from @result order by columnA
select dateDiff(ms, @start, getDate())
set noCount off
--150
--150
--160
--160
--150
--100
--150
--170
--150
--153
--total=1503
go

SELECT columnA, columnA * (columnA + 1) / 2 AS columnB
FROM tbl
ORDER BY columnA

Horse                                              AvgMilliseconds 
-------------------------------------------------- --------------- 
nr                                                 317
blindman                                           398
cursor                                             1815

Horse                                              AvgMilliseconds 
-------------------------------------------------- --------------- 
nr                                                 283
blindman                                           392
cursor                                             5053

set nocount on

--1. Create and populate table:
CREATE TABLE [tbl]([RecordID] [int] IDENTITY (1, 1) NOT NULL , [columnA] [int] NOT NULL) 
go
set noCount on
declare @i int
set @i = 1
while @i<=1000 begin
  insert into tbl(columnA) values(@i % 17)
  set @i = @i+1
end
set noCount off
go

declare	@RaceResults table (Horse varchar(50), Milliseconds int)
declare @result table(RecorID int primary key clustered, columnA int not null, columnB int not null)
declare @executionTime int
declare @start dateTime

declare	@Races int
set	@Races = 1

--2. Test nr proposal (each script I run 10 times, commented numbers are execution times on my laptop)

while @Races > 0
begin
	checkpoint
	dbcc dropCleanBuffers
	DBCC FREEPROCCACHE
	set @start = getDate()
	select t.RecordID, t.columnA, columnB = (select sum(columnA) from tbl t2 where t2.RecordID <= t.RecordID)
	from tbl t
	insert into @RaceResults(Horse, Milliseconds) select 'nr', dateDiff(ms, @start, getDate())

	--3. The same for blindman's solution:
	checkpoint
	dbcc dropCleanBuffers
	DBCC FREEPROCCACHE
	set @start = getDate()
	select	t1.RecordID,
		t1.ColumnA,
		sum(t2.columnA) as columnB
	from	tbl t1
		inner join tbl t2 on t1.RecordID >= t2.RecordID
	group by t1.RecordID,
		t1.columnA
	insert into @RaceResults(Horse, Milliseconds) select 'blindman', dateDiff(ms, @start, getDate())
	
	--4. Finally cursor based solution:
	checkpoint
	dbcc dropCleanBuffers
	DBCC FREEPROCCACHE
	set noCount on
	set @start = getDate()
	declare	@RecordID int
	declare @columnA int
	declare @columnB int
	declare c cursor for select RecordID, columnA from tbl order by RecordID
	open c
	set @columnB = 0
	while (1=1) begin
	  fetch next from c into @RecordID, @columnA
	  if @@fetch_status <> 0 break
	  set @columnB = @columnB + @columnA
	  insert into @result values(@RecordID, @columnA, @columnB)
	end
	close c
	deallocate c
	
	select * from @result order by columnA
	insert into @RaceResults(Horse, Milliseconds) select 'cursor', dateDiff(ms, @start, getDate())
	set noCount off

	delete from @Result

	set	@Races = @Races - 1
end

select	Horse,
	avg(MilliSeconds) as AvgMilliseconds
from	@RaceResults
group by Horse
order by avg(MilliSeconds) asc

drop table tbl
go