I have the following procedure:
drop procedure if exists deletebatches;
create procedure deletebatches()
BEGIN
declare acount int default 0;
declare done INT default 0;
declare abatchno varchar(10);
declare abatchpart varchar(20);
declare cursor1 cursor for select batchpart, batchno from batchestodelete;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1 ;
SET DONE = 0;
open cursor1;
readloop: loop
fetch cursor1 into abatchpart, abatchno;
if done = 1 then leave readloop;
end if;
delete from stockbatches where batch_batch = abatchno
and batch_part = abatchpart;
select curtime();
end loop readloop;
close cursor1;
end;
call deletebatches()
This query runs for so long that I have not had the patience to let it finish. I cancel it after an hour or so.
I also created a query to delete from stockbatches where batch_batch + batch_part in (select batch_batch + batch_part from batchestodelete)
and it took 1,278 seconds (over 21 minutes!).
There are only 776 records in the batchestodelete
table and about 1 million in the stockbatches
table which has indexes on batch_part
, batch_batch
and both fields combined.
Can anybody offer an explanation as to why this is taking so long for what is a relatively simple query?
Here are some pieces of information various people have asked from me.
Show full process list shows:
2614 root localhost:4785 Sleep 4786
2621 root localhost:4792 new_web_files Sleep 4789
2967 root localhost:1283 new_web_files Query 3 updating
delete from stockbatches where batch_batch = abatchno
and batch_part = abatchpart 3014 root localhost:1336 new_web_files
Query 0 show full processlist
Show create table stockbatches
shows:
STOCKBATCHES CREATE TABLE stockbatches (
batch_part varchar(20) collate latin1_general_ci NOT NULL,
batch_batch varchar(10) collate latin1_general_ci NOT NULL,
batch_qty decimal(18,2) default NULL,
batch_cond varchar(2) collate latin1_general_ci default NULL,
batch_release varchar(3) collate latin1_general_ci default NULL,
batch_curedate varchar(10) collate latin1_general_ci default NULL,
batch_lastmod datetime default NULL,
batch_lastaction varchar(1) collate latin1_general_ci default NULL,
PRIMARY KEY (batch_part,batch_batch),
KEY partno (batch_part),
KEY batchno (batch_batch),
KEY batchandpart (batch_part,batch_batch)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
Show create table batchestodelete
shows:
batchestodelete CREATE TABLE batchestodelete (
batchpart varchar(20) NOT NULL,
batchno varchar(10) character set latin1 collate latin1_general_ci default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Best Answer
Why are you using a cursor for this type of operation?
Most RDBMS are optimised for set-based operation, a cursor or a loop are iterative operations which perform row-by-row operations.
Whenever possible, you want to use declarative set-based code.
Try this: