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?
DROP PROCEDURE IF EXISTS apply_credit//
CREATE PROCEDURE apply_credit()
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;
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;
SELECT * FROM debug;
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
DELETE FROM into
DELAYED to the insert statement.
INSERT DELAYED INTO ... VALUES(....)
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.