Calling stored procedure in MySQL takes forever to execute

Question

I have a stored procedure which I'm trying to call, and it takes forever to execute. I have no idea what's wrong. A similar stored procedure in another database executes perfectly. I'm not well-versed with MySQL Workbench, so I don't know if the database settings are different or something.

Following is my stored procedure:

CREATE
DEFINER = `admin`@`%`
PROCEDURE `calculate_daily_coil_moved_by_crane_data`()

BEGIN
    set @curr_date = curdate();
    set @pre_date = date_add(curdate(), interval -1 day);
    set @a_shift_start_ts = concat(@pre_date, ' 06:00:00');
    set @a_shift_end_ts = concat(@pre_date, ' 13:59:59');
    set @b_shift_start_ts = concat(@pre_date, ' 14:00:00');
    set @b_shift_end_ts = concat(@pre_date, ' 21:59:59');
    set @c_shift_start_ts = concat(@pre_date, ' 22:00:00');
    set @c_shift_end_ts = concat(@curr_date, ' 05:59:59');

    SELECT  @curr_date,
            @pre_date,
            @a_shift_start_ts,
            @a_shift_end_ts,
            @b_shift_start_ts,
            @b_shift_end_ts,
            @c_shift_start_ts,
            @c_shift_end_ts;


    #SET DATA
    insert into daily_coil_move_by_crane_data_for_report (crane_id, crane_name, date, a_shift, b_shift, c_shift)
    select cr.id, cr.name, @pre_date, 0, 0, 0
    from yms_phase3.crane cr
    where active = 1
    order by cr.name;

    #----------------------------------------------------------------------------------------------------
    #--> COILS MOVED BY CRANE A Shift <--
    #----------------------------------------------------------------------------------------------------
    SET @shift = 'A';

    #FETCH ROW DATA
    update daily_coil_move_by_crane_data_for_report
    set a_shift = ifnull((select COUNT(*)
                            FROM yms_phase3.workorder_history in_data
                            where in_data.crane_id = daily_coil_move_by_crane_data_for_report.crane_id
                                and current_execution_status IN (6 , 7)
                                and in_data.pick_ts between @a_shift_start_ts and @a_shift_end_ts
                            group by in_data.crane_name), 0)
    where (a_shift is null or a_shift = 0);

    #----------------------------------------------------------------------------------------------------
    #--> COILS MOVED BY CRANE B Shift <--
    #----------------------------------------------------------------------------------------------------
    SET @shift = 'B';

    #FETCH ROW DATA
    update daily_coil_move_by_crane_data_for_report
    set b_shift = ifnull((select COUNT(*)
                            FROM yms_phase3.workorder_history in_data
                            where in_data.crane_id = daily_coil_move_by_crane_data_for_report.crane_id
                                and current_execution_status IN (6 , 7)
                                and in_data.pick_ts between @b_shift_start_ts and @b_shift_end_ts
                            group by in_data.crane_name), 0)
    where (b_shift is null or b_shift = 0);

    #----------------------------------------------------------------------------------------------------
    #--> COILS MOVED BY CRANE C Shift <--
    #----------------------------------------------------------------------------------------------------
    SET @shift = 'C';

    #FETCH ROW DATA
    update daily_coil_move_by_crane_data_for_report
    set c_shift = ifnull((select COUNT(*)
                            FROM yms_phase3.workorder_history in_data
                            where in_data.crane_id = daily_coil_move_by_crane_data_for_report.crane_id
                                and current_execution_status IN (6 , 7)
                                and in_data.pick_ts between @c_shift_start_ts and @c_shift_end_ts
                            group by in_data.crane_name), 0)
    where (c_shift is null or c_shift = 0);

    #----------------------------------------------------------------------------------------------------
    #INSERT ALL CRANE ENTRY
    insert into daily_coil_move_by_crane_data_for_report (crane_id, crane_name, date, a_shift, b_shift, c_shift)
    select -1, 'ALL', @pre_date, SUM(a_shift), sum(b_shift), sum(c_shift)
    from daily_coil_move_by_crane_data_for_report
    where date = @pre_date
    group by date;

    #UPDATE TOTAL
    update daily_coil_move_by_crane_data_for_report
    set total_coils_moved = (a_shift + b_shift + c_shift)
    where date = @pre_date;
END

Also tried to execute the query from Java using the following:

jdbcTemplate.execute("CALL calculate_daily_coil_moved_by_crane_data;");

But it gives me the following Exception:

java.sql.SQLException: Lock wait timeout exceeded

Any workaround I can do to solve this?

Can you edit your question with `daily_coil_move_by_crane_data_for_report` table definition — Mihai, Nov 09 '15 at 09:20
How big are the tables? Add `SELECT SYSDATE();` before/after each SQL to see which one(s) are the slowest. Then let's debug that one. — Rick James, Dec 01 '15 at 21:42

Kaushal B · Answer 1 · 2018-07-05T09:13:55.583

Please try and edit the configuration file, also search for the same here on stack. There are certain possibilities while checking this out,

Check and edit the config file on Hard drive for MySQL increase the cache capacity and default values as the default values are in KB's the memory allocated is very less and to execute such a big procedure it should at least be some MB.
Increase the connection String timeout, that is by setting up right time in seconds. by default it is 60 seconds, which is very less for executing such a procedure, I think in c# at least we set it to '0' seconds which means that it shall not get timed-out till the query is executed.
If Any left Joins/ inner query please try and check whether the same output is produced in inner joins ? as inner joins are faster than left or right joins.
Add Indexes, have foreign key references properly mapped for faster execution of query. Hope it works.

Calling stored procedure in MySQL takes forever to execute

1 Answers1