Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
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? –  Derek Downey Feb 21 '12 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'. –  Derek Downey Feb 21 '12 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 '12 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 '12 at 11:24

2 Answers 2

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
share|improve this answer
    
Very good points, Vince, and I do prefer the set operation over a cursor. +1 –  Derek Downey Feb 21 '12 at 17:23
2  
The (first line) DELETE FROM stockbatches should be: DELETE sb –  ypercube Feb 21 '12 at 22:12
    
+1 for coming up with the DELETE JOIN concept first –  RolandoMySQLDBA Feb 21 '12 at 22:26

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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