Why does this simple MySQL procedure take way too long to complete?
- by Howard Guo
This is a very simple MySQL stored procedure. Cursor "commission" has only 3000 records, but the procedure call takes more than 30 seconds to run. Why is that?
DELIMITER //
DROP PROCEDURE IF EXISTS apply_credit//
CREATE PROCEDURE apply_credit()
BEGIN
DECLARE done tinyint DEFAULT 0;
DECLARE _pk_id INT;
DECLARE _eid, _source VARCHAR(255);
DECLARE _lh_revenue, _acc_revenue, _project_carrier_expense, _carrier_lh, _carrier_acc, _gross_margin, _fsc_revenue, _revenue, _load_count DECIMAL;
DECLARE commission CURSOR FOR
SELECT pk_id, eid, source, lh_revenue, acc_revenue, project_carrier_expense, carrier_lh, carrier_acc, gross_margin, fsc_revenue, revenue, load_count FROM ct_sales_commission;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DELETE FROM debug;
OPEN commission;
REPEAT
FETCH commission INTO
_pk_id, _eid, _source, _lh_revenue, _acc_revenue, _project_carrier_expense, _carrier_lh, _carrier_acc, _gross_margin, _fsc_revenue, _revenue, _load_count;
INSERT INTO debug VALUES(concat('row ', _pk_id));
UNTIL done = 1 END REPEAT;
CLOSE commission;
END//
DELIMITER ;
CALL apply_credit();
SELECT * FROM debug;
