Time out error using cursor over 100k records

Question

I using following code to calculate the account bill , but when my records turn to 100k my app return time out error, let me know how could I rewrite it to increase performance ???

NOTE: I have debit and credit records and my result is BALANCE and STATUS .

declare @lbalance decimal=0 , @credit decimal=0 , @debit decimal=0 , @new_balance decimal=0,@balance decimal=0
DECLARE q1_cursor CURSOR DYNAMIC  for select debit,credit,balance,[status] from #t1
open q1_cursor 
fetch next from q1_cursor into @debit,@credit,@lbalance,@st


while @@fetch_status=0
begin

    set @new_balance=@new_balance+isnull(@credit,0)-isnull(@debit,0)
    if(@new_balance>1)
    begin
    set @st='creditor'
    set @balance=@new_balance
    end
    else
    begin
    set @st='debtor'
    set @balance=@new_balance*-1
    end

    update  #t1 set balance=@balance,[status]=@st where current of q1_cursor


    fetch next from q1_cursor into @debit,@credit,@lbalance,@st
end
close q1_cursor
deallocate q1_cursor

select * from #t1

EDIT:

I rewrite your code like following but , I get ERROR ! "Types don't match between the anchor and the recursive part in column "Balance" of recursive query "HierarchicalCTE"." what should I do??

with HierarchicalCTE (ID,Date, Debit, Credit, Balance, [Status]) As
(
    select ID,Date, Debit, Credit, Balance, [Status]
        from #t1 
        where ID = 1
    union all
    select tbl.ID,tbl.Date, tbl.Debit, tbl.Credit
            , Balance=(case when ((tbl.Debit > 0 and cte.[Status] = 'debtor') or (tbl.Credit > 0 and cte.[Status] = 'creditor')) then abs(cte.Balance + tbl.Debit)
                when tbl.Debit > 0 and cte.[Status] = 'creditor' then abs(cte.Balance - tbl.Debit)
                else abs(cte.Balance - tbl.Credit)
                end) 
            , cast(case when ((tbl.Debit > 0 and cte.[Status] = 'debtor') or (tbl.Debit > 0 and tbl.Debit > cte.Balance)) then 'debtor'
                else 'creditor'
                end as char(20)) as [Status]
        from #t1 tbl
            inner join HierarchicalCTE cte
            on tbl.ID = cte.ID+1 

)

select * from HierarchicalCTE

Answer 1

Assumption is your starting row has debit = 200, credit = 0, balance = 200, status = debtor and you want to enter values into balance and status for the remainder of the rows.

example of using Common Table Expression (CTE) based on assumption and there is an ID column in the table ..

declare @tbl table (id int, debit money, credit money, balance money, [status] varchar(20))

insert into @tbl (id, debit, credit, balance, [status])
values (1, 200, 0, 200, 'debtor')
, (2, 0, 300, 0, '')
, (3, 50, 0, 0, '')
, (4, 100, 0, 0, '')


; with cte (id, debit, credit, balance, [status])
as
(
    select id, debit, credit, balance, [status]
        from @tbl
        where id = 1
    union all
    select tbl.id, tbl.debit, tbl.credit
            , case when ((tbl.debit > 0 and cte.[status] = 'debtor') or (tbl.credit > 0 and cte.[status] = 'creditor')) then abs(cte.balance + tbl.debit)
                when tbl.debit > 0 and cte.[status] = 'creditor' then abs(cte.balance - tbl.debit)
                else abs(cte.balance - tbl.credit)
                end as balance
            , cast(case when ((tbl.debit > 0 and cte.[status] = 'debtor') or (tbl.debit > 0 and tbl.debit > cte.balance)) then 'debtor'
                else 'creditor'
                end as varchar(20)) as [status]
        from @tbl tbl
            inner join cte cte
            on tbl.id = cte.id + 1

)

select * from cte

initial data..

id debit credit balance status 1 200.00 0.00 200.00 debtor 2 0.00 300.00 0.00 3 50.00 0.00 0.00 4 100.00 0.00 0.00

result ..

id debit credit balance status 1 200.00 0.00 200.00 debtor 2 0.00 300.00 100.00 creditor 3 50.00 0.00 50.00 creditor 4 100.00 0.00 50.00 debtor