Long running curson logic

1

Hi Below cursor logic takes 2 hrs to run can you please let me know suggest alternate logic to replace while loop.

declare
@col1
@col2
@col3
@colsum
@colsum1
@col4
@col5
@col6

Declare Cursor1 cursor for

select col1,col2, col3, colsum = (select count(*) from table2 where table2.col1 = table1.col1) from table1

open Cursor1

Fetch next from Cursor1 into
@col1
@col2
@col3
@colsum

while (@@ fetch_status = 0)
Begin
SET @colsum1 = @col2 + @col3

if @colsum = 0
Begin
Insert into Table2 (col1, col2, col3 ,col4,Ind)
values
(@Col1,@col1+@col2,@col3,@colsum1,1)
End

Else

Begin
select @col4 = col1,
@col5 = col2
from table2
where col3 = @ col3 and col4 = @colsum1 and Ind = 1

set @col1 = @col4 + col1
set @col2 = @col5 + col2

update table2 set Ind = 0
where col3 = @ col3 and col4 = @colsum1 and Ind = 1

Insert into tabl2 (col1, col2, col3 ,col4,Ind)
values
(@Col1,@col1+@col2,@col3,@colsum1,1)

End

Fetch next from Cursor1 into
@col1
@col2
@col3
@colsum

End

Close Cursor1

Deallocate cursor1

2

Are you using Microsoft SQL Server, or another RDBMS? On MS SQL, the code you posted does not parse. In MS SQL each variable declaration must include a data type (such as int, float etc.), and multiple variables declared using a single declare statement must be separated by commas etc.

3

this is just prototype of actual stored procedure , I have created. this is not actual code.

4

Not knowing your data:

Create insert and update staging tables -- two queries. then use those staging tables to update the final table -- two more queries. No cursors. No RBAR

There may be an easier way, but you can start there.

Perhaps post some sample input and output data

5

The UPDATE is reasonably straight forward.

UPDATE t2
SET Ind = 0
FROM dbo.table2 t2
INNER JOIN dbo.table1 t1 ON t1.col1 = t2.col1 and t1.col2 + t1.col3 <> 0
WHERE t2.col3 = t1.col3 and t2.col4 = (t1.col2 + t1.col3) and Ind = 1

But I can't tell what the INSERT should be when a row already exists in table2 because the logic in your code is not valid:
"
set @col1 = @col4 + col1
set @col2 = @col5 + col2
"
Is that supposed to be "+ @col1" and "+ @col2"? If so, why not just do that in the INSERT statement itself rather than corrupt the base values in those variables?

6

Yes Update is straight forward.

For insert I miss @col1 and @col2, so it is

set @col1 = @col4 + @col1
set @col2 = @col5 + @col2

Yes we move these to insert but still it will be a while loop and row by row processing

7

why? show some before and after examples.