Mysql – Stored procedure getting delayed more

MySQLmysql-5.5mysql-5.6mysql-workbench

Stored procedure and it is getting delayed. Taking nearly more than half an hour if I call the stored procedure in MySQL command line client. This procedure is to update values in two tables

drop procedure IF EXISTS ALTERTABLEVALUES;
delimiter $$

CREATE PROCEDURE ALTERTABLEVALUES()
BEGIN
DECLARE finished INT DEFAULT 1;
DECLARE total_count INT DEFAULT 1;
DECLARE val varchar(15);
DECLARE i INT DEFAULT 1;
DECLARE table_num INT;
DECLARE table_tab varchar(5);
DECLARE number_cursor CURSOR FOR select outpassNo,tab from tempnumbers;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 0;

drop temporary table if exists tempnumbers;
CREATE TEMPORARY TABLE tempnumbers as
    select  num,'temp' as tab
        from  table1
        where  month(inTime)=2
          and  year(inTime)=2018
        union 
     select  num,'vary' as tab
        from  table2
        where  month(inTime)=2
          and  year(inTime)=2018
        order by  outpassNo;

SET val = (select num from tempnumbers limit 1);
SET total_count = (select count(*) from tempnumbers);

OPEN number_cursor;
get_values: LOOP
FETCH number_cursor INTO table_num,table_tab;
IF finished = 0 THEN 
 LEAVE get_values;
 END IF;
IF table_tab = 'temp' THEN 
    update table1 set table1.num= val where table1.num= table_num;
ELSE
    update table2 set table2.num= val where table2.num = table_num;
END IF;
SET val = val +1;
END LOOP get_values;
CLOSE number_cursor;
DROP TEMPORARY TABLE tempnumbers;
END;
$$
delimiter ;

table1 has the newly inserted data and table2 has the processing values came from table1. A business need is there, where few data from table2 are moved to some other table on a monthly basis, based on some criteria. The expectation is to reorder the numbers column of table1 and table2 as a sequence with the missing values of table2.

Can anyone help on handling resultset without a cursor in stored procedure?

Best Answer

since you are going back to a temporary table and updating rows one by one, you should put an index on the table so the updates go faster. alter table tempnumbers add index(num);

Related Solutions

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

What you presented is essentially the correct way for interrupting triggers in MySQL 5.1

I wrote about this before

Dec 23, 2011 : check constraint does not work?
Apr 25, 2011 : Trigger in MySQL to prevent insertion

You cannot do Dynamic SQL in triggers.

You may have to resort to coding the my_signal like this:

CREATE PROCEDURE `my_signal`(error_type INT)
BEGIN
    IF error_type = 1 THEN
        UPDATE `Error: can not insert new OWNER !` set x=1;
    END IF;
    IF error_type = 2 THEN
        UPDATE `Error: Row can not reference itself!` set x=1;
    END IF;
    .
    .
END//

then change the trigger to look like this:

DELIMITER $$
CREATE
  TRIGGER `employee_before_insert` BEFORE INSERT
    ON `Employee`
    FOR EACH ROW BEGIN
      CASE
       WHEN NEW.designation = 'OWNER'  THEN
          CALL my_signal(1);
       WHEN NEW.SSN = NEW.MSSN THEN
          CALL my_signal(2);
      END CASE; 
  END$$   
DELIMITER ;

Sorry for the clumsy programming but Signal Processing is MySQL 5.1 is horrible.

MySQL – How Long Will a Temporary MEMORY Table Persist if Not Dropped?

What is funny about temporary tables in a stored procedure is not so much the transient existence of the table (which gets dropped upon the DB connection's termination), but the scope of the stored procedure.

Someone asked this question on StackOverflow : Scope of temp tables created in MySQL stored procedure. It has been over a year and nobody answered the question? Let me set the record straight. The fact is: The temp table exists inside and outside of the Stored Procedure, but you can do things with the temporary table only inside the scope of a running Stored Procedure.

According to the Book

kdsjx

Chapter 5 has a subheading Returning Result Sets to Another Stored Procedure.

It says in paragraph 2 on Page 117:

Unfortunately, the only way to pass a result set from one stored procedure to another is to pass the results via a temporary table. This is an awkward solution b, and -- because the temporary table has scope throughout the entire session -- it creates many of the same maintainability issues raised by the use of global variables. but if one stored program needs to supply another stored program with results, then a temporary table can be the best solution.

Looking back at the StackOverflow question, I can see someone called the Stored Procedure from the mysql client. Since the mysql client is not a Stored Procedure, the results cannot be manipulated the mysql client level via DML other than doing a SELECT to see the results. Since you calling a recursive stored procedure, you can rest assured the temp table is fully accessible for the duration of the DB Connection.

I hope this answers your question.

UPDATE 2014-01-31 11:26 EST

In your last comment, you said

If we employ persistent connections, will the MEMORY table persist through multiple REQUESTS, and it seems it will, so for performance sake, I'm assuming that using this method will *REQUIRE us to explicitly DROP the temporary MEMORY table. Do I assume correctly?

Yes and No. I say Yes because it is one way to do it. I say no because another way to do it is:

CREATE TEMPORARY TABLE IF NOT EXISTS id_list (iid CHAR(32) NOT NULL) ENGINE=memory;
TRUNCATE TABLE id_list;

Whichever way you choose, the operation is still the same since TRUNCATE TABLE drops and recreates the table. This will not harm other DB Connections since each Connection has its own id_list table.

Best Answer

Related Solutions

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

MySQL – How Long Will a Temporary MEMORY Table Persist if Not Dropped?

UPDATE 2014-01-31 11:26 EST

Related Question