[求助]MySQL存储过程执行效率问题

zhangxu_010

DELIMITER $$

USE `upc`$$

DROP PROCEDURE IF EXISTS `test3`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test3`()
BEGIN
  /*定义游标取值*/
    DECLARE v_offering_rel_id VARCHAR(10);
    DECLARE v_offering_id   VARCHAR(10);
    DECLARE v_rel_offering_id  VARCHAR(100);
    DECLARE v_offering_rel_type    VARCHAR(100);
    DECLARE v_commitment_opmode   VARCHAR(10);
    DECLARE v_count INT(4);
    DECLARE v_i INT DEFAULT 0;
    DECLARE v_no_more_products INT DEFAULT 0;
    DECLARE t_error INTEGER DEFAULT 0;
  /*定义主键取值*/
    DECLARE k_prod_offering_rel    VARCHAR(10);
    DECLARE k_offer_rel_char       VARCHAR(10);
    DECLARE k_offer_rel_char_value VARCHAR(10);
    DECLARE k_prod_offering_rel_ext VARCHAR(10);
  /*定义游标*/
  DECLARE cur_offer_rel CURSOR FOR SELECT offering_rel_id , offering_id , rel_offering_id , offering_rel_type , commitment_opmode FROM hu_main_addon_rel_old WHERE offering_rel_type = 'COMPAT'
  AND commitment_opmode = 100;
  /*Not Found时处理方式*/
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_no_more_products = 1;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1;
  SET autocommit = 0;
  OPEN cur_offer_rel ;
  REPEAT
    FETCH cur_offer_rel INTO                
                v_offering_rel_id,
                v_offering_id,
                v_rel_offering_id,
                v_offering_rel_type,
                v_commitment_opmode;               
      
    IF v_no_more_products <> 1 THEN   
    BEGIN
    START TRANSACTION;
    SELECT COUNT(*) INTO v_count FROM hu_main_addon_rel_by_b2c_0429 WHERE rel_type = 'optional' AND offer_id_main = v_offering_id AND offer_id_addon = v_rel_offering_id;   
    UPDATE  hu_main_addon_rel_old SET commitment_opmode = v_count WHERE offering_rel_id = v_offering_rel_id;
    END;   
    IF t_error = 1 THEN  
       ROLLBACK;
    ELSE
    SET v_i = v_i + 1;
    IF MOD(v_i , 1000) = 0 THEN
      COMMIT;
    END IF;
    END IF;
    END IF;
    UNTIL v_no_more_products = 1
  END REPEAT ;  
  CLOSE cur_offer_rel ;

END$$

DELIMITER ;

两张表里的数据分别都是6~7万左右，尝试过直接Update，也非常慢。现在执行过程大概每秒4~5条件数据，也太慢了，请帮忙看看哪里是可以优化一下，提高效率。
在线等，感谢