Why does this simple MySQL procedure take way too long to complete?
Posted
by
Howard Guo
on Stack Overflow
See other posts from Stack Overflow
or by Howard Guo
Published on 2012-09-11T03:27:53Z
Indexed on
2012/09/11
3:38 UTC
Read the original article
Hit count: 208
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;
© Stack Overflow or respective owner