Mysql – How to create a stored procedure with several delete in(select) statements

MySQLstored-procedures

Our database needs to be periodically cleared of certain items that receive a specific flag (A4) on their id. Our application manages stores and all tables are myISAM. The items can be either in the bought, sold, exchanged, etc tables and they will also exist in the main table that contains all items (the only information the tables share is the id and I can't control that at the moment).

My initial idea was to create the individual delete statements and then place them together in a stored procedure. The stored procedure I created contains:

delimiter $$

create procedure clear_A4(in period char(10))

BEGIN

delete from mov_pedido where pednot in(select distinct titnum from mov_titulo where titnum like 'A4%' and titemi <='2012-01-01' and titvalpag >0);

delete from mov_orcame where orcnot in(select distinct titnum from mov_titulo where titnum like 'A4%' and titemi <='2012-01-01' and titvalpag >0);

delete from mov_nfsven where nfsnum in(select distinct titnum from mov_titulo where titnum like 'A4%' and titemi <='2012-01-01' and titvalpag >0);

delete from mov_movime where movdoc in (select distinct titnum from mov_titulo where titnum like 'A4%' and titemi <='2012-01-01' and titvalpag >0);

delete from mov_titulo where titnum like 'A4%' and titemi<='2011-12-01' and titvalpag >0;

end $$

delimiter ;

These lines refer to items sold. A sale starts with a order, then a quotation, then the actual sale, and finally it gets throw in the main record. The first table mov_pedido has the orders, mov_orcame has the quotations, mov_nfsven has the actual sales and mov_movime records all the transactions. These tables can only be cleared of items that have been paid for by customers but this information can only be found on mov_titulo so I decide to use the select statements with the IN operator. The problem is I also need to clear the mov_titulo table. If I delete the items from mov_titulo first then the information used in the select statements is lost.

Reading up on mySQL,multithreaded databases it seems that if the statements are inside the stored procedure together, they will be processed simultaneously and because the tables are related that way it will cause a problem.

My question is: how can I improve the logic of my delete statement to make them work together? Can I improve my stored procedure to deal with this?

Right now I am executing one statement at a time manually, and that works, but I can see problems down the line, such as entering the wrong date in one of the existing 20 some delete statements for example so I am looking for a better way to do this.

Thank you.

Best Answer

if the statements are inside the stored procedure together, they will be processed simultaneously and because the tables are related that way it will cause a problem.

No, the statements in a stored procedure are processed sequentially, not in parallel. A stored procedure executes in a single thread, just as if you had issued the queries one after another... not simultaneously.

There are some optimizations you could make, however.

If the `titnum` table doesn't have a multi-column index on the three columns (titvalpag, titemi, titnum) together, you should probably add one.

The delete statements can be rewritten without the IN().

Use caution with this, because doing it incorrectly will remove the wrong data.

delete from mov_pedido 
where pednot in(select distinct titnum 
                  from mov_titulo 
                 where titnum like 'A4%' 
                   and titemi <='2012-01-01' and titvalpag >0);

...becomes...

DELETE p
  FROM mov_pedido p
  JOIN mov_titulo m ON m.titnum = p.pednot
 WHERE m.titnum like 'A4%' and m.titemi <='2012-01-01' and m.titvalpag >0;

This is the multi-table delete syntax. "DELETE p" will only remove the rows from "p" (an alias for mov_pedido) that match rows in "m" (mov_titulo). It won't remove the rows from "m" unless you say "DELETE p, m" -- so don't do that.

The mov_pedido table (and the others) should have an index on the column you're using to join (pednot, etc.) if they don't already.

TRY THIS #1

Give the delimiter on the command line itself

mysql -u root -pmypass --delimiter="//" test < myproc.sql

It is a command line option for the mysql client program

[root@****]# mysql --help | grep -i delimiter
  --delimiter=name    Delimiter to be used.
delimiter                         ;

TRY THIS #2

You can write the code into a text file and execute against the text file as @altmannmarcelo suggested

echo "DELIMITER //" > myproc.sql
echo "create procedure test_pro()" >> myproc.sql
echo "begin" >> myproc.sql
echo "select 'hello pro';" >> myproc.sql
echo "end" >> myproc.sql
echo "//" >> myproc.sql
mysql -u root -pmypass  test < myproc.sql

GIVE IT A TRY !!!

Mysql – Stored Procedure Performance using Temporary Tables

To answer a question that arises from the comments, temporary tables in MySQL are owned by and only available to the client connection (session, thread) that created them, so there's not an isolation issue, but there is the potential issue of your connection having left something there with that name before, either with stale data or a similarly-named table with a different structure, either of which would be less than ideal.

The CREATE TEMPORARY TABLE IF NOT EXISTS statement creates the temporary table if there isn't one of the same name, but is a non-blocking no-op if the table is already there, which, as noted, is still bad. The better alternative is probably to drop the temporary table at the top of the proc, if it's there, before creating it, so you always begin with a clean environment. This is similarly a no-op if the temporary table isn't there:

   DROP TEMPORARY TABLE IF EXISTS BATCH_ITEMS_UOW;

Important: if you do not have an index on the STATUS column of the BATCH_ITEMS table, this will be one of the two major reasons this procedure is slower than it needs to be. That's going to be essential for performance, either with this code or the existing code.

It would probably also be best to index the temporary table, to make life as easy as possible for the optimizer. Note that the column declared must be the same as the name or alias of the column in the SELECT, otherwise MySQL assumes the SELECT is supposed to generate additional columns for the temporary table using the column names in the SELECT:

   CREATE TEMPORARY TABLE BATCH_ITEMS_UOW (
     ID INT NOT NULL PRIMARY KEY
   ) ENGINE=MEMORY AS (
    SELECT ID FROM BATCH_ITEMS 
     WHERE STATUS = 'UNPROCESSED' 
       AND BATCH_JOB_ID = batchId 
     LIMIT size
   );

Then instead of an update that use a subquery, this should be a join, since those are generally optimized better in any version of MySQL. This is the other likely reason this procedure may be slower than you expect.

UPDATE BATCH_ITEMS BI
  JOIN BATCH_ITEMS_UOW UOW ON UOW.ID = BI.ID
   SET BI.STATUS = 'PROCESSING';

Then return the result-set...

SELECT * FROM BATCH_ITEMS_UOW;

Optionally, you can drop the temporary table... it will be dropped automatically when your session disconnects, or the next time you run this procedure, since we're now explicitly dropping it at the start if you run this again in the same session.

DROP TEMPORARY TABLE BATCH_ITEMS_UOW;

Best Answer

Related Solutions

Mysql – How to create a thesql stored procedure through linux terminal

TRY THIS #1

TRY THIS #2

GIVE IT A TRY !!!

Mysql – Stored Procedure Performance using Temporary Tables

Related Question