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: 205

Filed under:
|

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

Related posts about mysql

Related posts about database