While loop in Procedure for a batch delete

JamesAvery22 · #1 (**permalink**) 02-09-06, 19:02

Is there any way something like this would work?

Code:

create procedure myschema.myproc(in invar integer)
	modifies sql data
	not deterministic
	language sql
begin
	declare rowcount integer; 
	set rowcount = select count(*) from myschema.mytable where var = invar;
	while ( rowcount > 0 ) do
		delete
			from myschema.mytable
				where id_col in
				(select id_col
					from myschema.mytable
					where var = invar
					fetch first 250000 rows only
				);
		commit;
		set rowcount = select count(*) from myschema.mytable where var = invar;
	end while;
end
;

Marcus_A · #2 (**permalink**) 02-09-06, 20:09

What you should do is:

1. Delecare a cursor with the appropriate where clause (use hold option so it will not close if you do commits)

2. Open the cursor

3. In a while loop:

a. fetch the next row into variables
b. If return code = 100 (no more rows in cursor) exit the loop
c. If you have reached the maximum number of rows you want to update, then exit the loop
d. If you want to delete the row, then delete where current of cursor
e. Every 1000 rows do a commit

4. Commit one more time at the end.
5. Close the cursor.

JamesAvery22 · #3 (**permalink**) 02-10-06, 12:57

Is this correct? Any chance that'd cause an infinite loop?

Code:

create procedure myschema.myproc (in in_col1,
                                  in in_commitcount integer)
    modifies sql data
    not deterministic
    language sql

begin

	declare sqlcode integer default 0;
	declare var_key bigint;
	declare var_col1 integer;
	declare deletedcount integer;
	
	
	declare mycursor cursor with hold for
	 select key,col1
	 from myschema.mytable
	 where col1 = in_col1;
	 
	open mycursor;
	  
	set deletedcount = 0; 
	while ( sqlcode = 0 ) do
		fetch mycursor into var_key, var_col1;
		if sqlcode <> 100 then
			delete from myschema.mytable where current of mycursor;
			set deletedcount = deletedcount + 1; 
			if deletedcount = in_commitcount then
				commit;
				set deletedcount = 0; 
			end if;
		end if;
	end while;
	
	commit;
	
	close mycursor;

end
;

n_i · #4 (**permalink**) 02-10-06, 14:52

Quote:

Originally Posted by JamesAvery22

Is this correct? Any chance that'd cause an infinite loop?

There's actually a pretty good chance of that. SQLCODE is set by each statement. You should save it to a local variable after each statement in whose status you're interested.

Code:

...
declare sqlcode int;
declare mysqlcode int;
...
open ...
set mysqlcode = sqlcode;
...
while (mysqlcode = 0) do
   fetch ...
   set mysqlcode = sqlcode;
   ...
end while;
...

JamesAvery22 · #5 (**permalink**) 02-10-06, 17:54

Quote:

Originally Posted by n_i

There's actually a pretty good chance of that. SQLCODE is set by each statement. You should save it to a local variable after each statement in whose status you're interested.

Code:

...
declare sqlcode int;
declare mysqlcode int;
...
open ...
set mysqlcode = sqlcode;
...
while (mysqlcode = 0) do
   fetch ...
   set mysqlcode = sqlcode;
   ...
end while;
...

Thought I remembered hearing that somewhere =) Thanks

JamesAvery22 · #6 (**permalink**) 02-10-06, 18:03

One more question,
One of the tables this query will be running on has referential integrity turned on and deleting from it is very slow. It has around 8mil rows and it takes longer to delete it than another table with 30mil. Will this slow it down any more?

As apposed to running this a bunch of times

Code:

delete
	from myschema.mytable
		where id_col in
		(select id_col
			from myschema.mytable
			where var = invar
			fetch first 250000 rows only
		);
commit;

n_i · #7 (**permalink**) 02-10-06, 20:09

I don't think this will affect performance. Make sure there are indexes on the columns involved in the foreign key relationship on all tables. You may want to try dropping the FK temporarily and deleting child rows yourself.

umayer · #8 (**permalink**) 02-13-06, 01:16

also consider using the
LOCK TABLE myschema.mytable IN EXCLUSIVE MODE

for both (the main and the dependent) tables
to avoid a lot of page locks

Internet Services

Web Development

Digital Marketing

Consumer Tech