CODEWITHSUNDEEP
mysqldatabase
user972946
user972946

Reputation:

Why does this simple MySQL procedure take way too long to complete?

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;

Upvotes: 1

Views: 1167

Answers (2)

user972946
user972946

Reputation:

The database is hosted in a data centre very far away from my MySQL client.

Connected to a MySQL client which is closely located with the MySQL server makes execution time almost 60 times faster (it takes less than one second for the procedure to complete).

I suspect that MySQL client CLI has an issue handling a remote data connection like that.

Upvotes: 0

mikespook
mikespook

Reputation: 796

If you select some datas, and insert into another table, you can do this:

INSERT INTO debug 
SELECT concat('row ', _pk_id)
FROM ct_sales_commission;

It's faster than using a cursor.

Some minor turning:

  • Remove all indexes on the table debug.

  • Replace the DELETE FROM into TRUNCATE TABLE.

  • Add DELAYED to the insert statement.

    INSERT DELAYED INTO ... VALUES(....)

Upvotes: 1

Related Questions