1

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
4
  • Welcome to DBA stackexchange, Ron. Can you provide the SHOW FULL PROCESSLIST while the stored proc is running? Also, can you give SHOW CREATE TABLE for both tables involved? Feb 21, 2012 at 16:55
  • Also, not part of a performance issue, but I notice you are doing SET DONE = 0;, but everywhere else it is 'done'. Feb 21, 2012 at 16:57
  • I have just seen your later post with the delete with join. It worked brilliantly in 5 seconds. Thank you for your help, I am learning all the time. :)
    – user6880
    Feb 22, 2012 at 7:29
  • Would it be possible to utilise the code you gave me from an MSSQL query to a linked MySql server such as this? DELETE FROM OPENQUERY(ronsmysql, 'SELECT * FROM stockbatches') WHERE batch_part = @batchpart and batch_batch = @batchno but using your version of the code. I cannot get my head around the syntax for the openquery.
    – user6885
    Feb 22, 2012 at 11:24

2 Answers 2

3

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:

DELETE sb
FROM stockbatches sb
INNER JOIN batchestodelete btd
ON sb.batch_batch = btd.batchno
AND sb.batch_part = btd.batchpart
3
  • Very good points, Vince, and I do prefer the set operation over a cursor. +1 Feb 21, 2012 at 17:23
  • 2
    The (first line) DELETE FROM stockbatches should be: DELETE sb Feb 21, 2012 at 22:12
  • +1 for coming up with the DELETE JOIN concept first Feb 21, 2012 at 22:26
1

Just a stab in the dark at the moment, but this query throws a red flag to me:

delete from stockbatches where batch_batch = abatchno and batch_part = abatchpart;

If abatchno and/or abatchpart are ever empty (such as might be the case on the last iteration of the loop prior to done=1), your query would end up looking something like this:

DELETE FROM stockbatches WHERE batch_batch AND batch_part;

Which would likely delete a lot more than you are intending.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.