Re: looping

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I hit up several 'softies at the PASS Summit recently begging for a more
complete implementation of windowing functions to at LEAST include the
ability to do efficient running totals. It is SUCH a common application
need!

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Tony Rogerson" <tonyrogerson@xxxxxxxxxx> wrote in message
news:E45A970B-1570-45AE-8D2F-80219BEC32CE@xxxxxxxxxxxxxxxx
If he does answer it will be some off topic rant aimed at distracting us
away from the facts in the case in point also - it won't come anywhere
near addressing the point I'm making that for running totals cursors rock
and set based utterly sucks.

Its a real world problem, the OVER clause on SUM gives us no benefit here
either.

Rebuffing --celko-- posts these days are like shooting fish in a barrel -
it was a lot more difficult 10 years ago, but it looks like the guy is
tired and even more out of touch than he was 10 years ago.

I'll be blogging this thing tomorrow or Friday - got some time for some
research.

Tony.

"Uri Dimant" <urid@xxxxxxxxxxx> wrote in message
news:#Gt0OqsYKHA.5544@xxxxxxxxxxxxxxxxxxxxxxx
Hi Tony
Before you answer - think for a minute - I'm not interested in what
theoretically is right and I'm not interested in what works on DB2 or
Oracle - this is a problem requiring an answer that works on SQL Server.

He is going to ignore your question as well as he has been ingonerd
Aaron's






"Tony Rogerson" <tonyrogerson@xxxxxxxxxx> wrote in message
news:OgkP8bsYKHA.2184@xxxxxxxxxxxxxxxxxxxxxxx
parallelism, a set-oriented approach to problem solving will run order
of magnitude faster, give shorter code, be easier to maintain, etc.
Your mindset is still working with punch cards.

I'm suprised you've never had to do rolling totals on any realistically
production volume of data.

CTE approach....


with Trans ( row_no, id, salesperson_id, tran_date, clear_date, amount,
transaction_types_id, account_id )
as (
select row_no = row_number() over( order by account_id, tran_date,
id ), id, salesperson_id, tran_date, clear_date, amount,
transaction_types_id, account_id
from Transactions
where account_id between 1000 and 5000
)
select t1.*,
running_amount = coalesce(
( select sum( amount )
from Trans t2
where t2.row_no < t1.row_no
and t2.account_id = t1.account_id ), 0 )
+ amount
from Trans t1
order by account_id, row_no

Ran for 3.5 hours after which I cancelled it - in that time it had taken
15447110 cpu's and 80600104 logical reads (equates to 614GB of data
having to be processed).

Now, the cursor on the other hand (below) ran in 84 seconds took 15926
cycles, 726413 logical reads (equates to 5.5GB of data having to be
processed).

Before you answer - think for a minute - I'm not interested in what
theoretically is right and I'm not interested in what works on DB2 or
Oracle - this is a problem requiring an answer that works on SQL Server.

If you want the test data yourself I'll be happy to oblige - it will be
on the blog post that I will write once you've have completed your
retort/rant.

-- Cursor - INSERT method
set nocount on

create table #Trans(
account_id int not null,
id int not null,
amount decimal(28,2) not null,
rolling_total decimal(28,2) not null,
salesperson_id int not null,
tran_date smalldatetime not null,
clear_date smalldatetime not null,
transaction_types_id int not null
)

declare trans_cur cursor fast_forward for
select account_id,
row_no = row_number() over( partition by account_id order by
tran_date, id ),
id,
amount,
salesperson_id,
tran_date,
clear_date,
transaction_types_id
from Transactions
where account_id between 1000 and 5000
order by account_id, row_no

declare @account_id int,
@row_no int,
@id int,
@amount decimal(28,2),
@salesperson_id int,
@tran_date smalldatetime,
@clear_date smalldatetime,
@transaction_types_id int,
@rolling_total decimal(28,2)

open trans_cur

fetch next from trans_cur
into @account_id, @row_no, @id, @amount,
@salesperson_id, @tran_date, @clear_date, @transaction_types_id

begin tran

while @@fetch_status = 0
begin
if @row_no = 1 -- initialise on each partition
set @rolling_total = @amount

else
set @rolling_total = @rolling_total + @amount

insert #Trans ( account_id, id, amount, rolling_total,
salesperson_id, tran_date, clear_date, transaction_types_id )
values( @account_id, @id, @amount, @rolling_total,
@salesperson_id, @tran_date, @clear_date, @transaction_types_id )

fetch next from trans_cur
into @account_id, @row_no, @id, @amount,
@salesperson_id, @tran_date, @clear_date,
@transaction_types_id

end
deallocate trans_cur

commit tran

select id, salesperson_id, tran_date, clear_date, amount,
transaction_types_id, amount, rolling_total
from #Trans
order by account_id, tran_date, id

drop table #Trans
go



"--CELKO--" <jcelko212@xxxxxxxxxxxxx> wrote in message
news:0ecfffa8-b66e-44e4-81a2-a17c0d62e181@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
thanks for your help.. btw.. the code was not the actual code... just
what I was thinking about in my head... I've not done cursors in MS
SQL before, but have in Oracle. You seem to have a problem with
using cursors, would you like to explain more?

The goal of SQL is to be a declarative language. All the optimizers
and SQL engines are designed for that goal. Cursors were meant to be
a way to get set-oriented data into a sequential file structure that
would work with a 3GL (Cobol, Fortran, C, etc.) and not for the
database side of the enterprise.

Get a copy of my THINKING IN SETS book for details. Thanks to
parallelism, a set-oriented approach to problem solving will run order
of magnitude faster, give shorter code, be easier to maintain, etc.
Your mindset is still working with punch cards.

[sloan] I've written one cursor in 8 years. And that is because the
db designer violated 1NF. <<

I am at five cursors, but I am old and we did not have CASE
expressions and other things in the old days. I know I could have
avoided three of them easily.





.

Relevant Pages

  • Re: looping
    ... If he does answer it will be some off topic rant aimed at distracting us away from the facts in the case in point also - it won't come anywhere near addressing the point I'm making that for running totals cursors rock and set based utterly sucks. ... (select sum(amount) ... from Trans t2 ... salesperson_id int not null, ...
    (microsoft.public.sqlserver.programming)
  • Re: looping
    ... id), id, salesperson_id, tran_date, clear_date, amount, ... from Trans t2 ... salesperson_id int not null, ... I've not done cursors in MS ...
    (microsoft.public.sqlserver.programming)
  • Re: looping
    ... (select sum(amount) ... from Trans t2 ... Now, the cursor on the other hand ran in 84 seconds took 15926 cycles, 726413 logical reads. ... salesperson_id int not null, ...
    (microsoft.public.sqlserver.programming)
  • Re: looping
    ... SQLBits, the recording and presentation is here: But, I will focus specifically on this comparison cursor v set for rolling totals. ... amount, transaction_types_id, account_id) ... from Trans t2 ... salesperson_id int not null, ...
    (microsoft.public.sqlserver.programming)
  • Re: looping
    ... I will focus specifically on this comparison cursor v set for rolling totals. ... amount, transaction_types_id, account_id) ... from Trans t2 ... salesperson_id int not null, ...
    (microsoft.public.sqlserver.programming)