当前位置: 动力学知识库 > 问答 > 编程问答 >

database - 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;

网友答案:

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(....)
    
网友答案:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: