[求助]MySQL存储过程执行效率问题
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_idVARCHAR(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;
UPDATEhu_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条件数据，也太慢了，请帮忙看看哪里是可以优化一下，提高效率。
在线等，感谢
页:
[1]