I have created the following stored proc in MySQL:
BEGIN
DECLARE temp_mold VARCHAR(30);
DECLARE temp_time DATETIME;
DECLARE temp_shot_ct INT;
DECLARE temp_shot_count INT;
DECLARE temp_shot_mold VARCHAR(30);
DECLARE temp_shot_cavity_count INT;
DECLARE temp_job_count INT;
DECLARE temp_row_count INT;
DECLARE done INT DEFAULT 0;
DECLARE temp_shot_counter INT DEFAULT 1;
DECLARE setup_time INT;
DECLARE cursor1 CURSOR FOR
SELECT
(schedule_run_time*3600)/(schedule_qty/schedule_cavity_count) as shot_ct,
ROUND((schedule_qty/schedule_cavity_count)) as shot_count,
schedule_mold as shot_mold, schedule_cavity_count as shot_cavity_count
FROM `schedule`
WHERE `schedule`.schedule_cell = start_cell AND `schedule`.schedule_date = DATE(start_time) AND deleted = 0
ORDER BY schedule_order ASC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SELECT COUNT(schedule_id) INTO temp_job_count FROM `schedule` WHERE schedule_cell = start_cell AND schedule_date = DATE(start_time) AND deleted = 0;
SELECT cell_setup_standard INTO setup_time FROM cells WHERE cell_name = start_cell;
DROP TEMPORARY TABLE IF EXISTS rst;
CREATE TEMPORARY TABLE rst (
shot_id INT NOT NULL AUTO_INCREMENT,
shot_datetime datetime,
shot_qty INT,
PRIMARY KEY (shot_id)
);
SET temp_time = start_time;
SET temp_mold = "";
SET temp_row_count = 0;
INSERT INTO rst (shot_datetime) VALUES (temp_time);
#Loop through cursor1 recordset
OPEN cursor1;
start_loop: LOOP
FETCH cursor1 INTO temp_shot_ct, temp_shot_count, temp_shot_mold, temp_shot_cavity_count;
IF done THEN
LEAVE start_loop;
END IF;
IF temp_mold <> temp_shot_mold THEN
SET temp_time = TIMESTAMPADD(MINUTE,setup_time,temp_time); #Add standard setup time whenever the mold changes
INSERT INTO rst (shot_datetime) VALUES (temp_time);
SET temp_mold = temp_shot_mold;
END IF;
SET temp_shot_counter = 1;
REPEAT
SET temp_time = TIMESTAMPADD(SECOND,temp_shot_ct,temp_time);
INSERT INTO rst (shot_datetime,shot_qty) VALUES (temp_time,temp_shot_cavity_count);
SET temp_shot_counter = temp_shot_counter + 1;
UNTIL temp_shot_counter > temp_shot_count
END REPEAT;
END LOOP;
CLOSE cursor1;
#Return records. Will be used in ORM to loop through records on the PHP side
SELECT SUM(shot_qty) AS qty, HOUR(shot_datetime) AS shot_hour FROM rst GROUP BY HOUR(shot_datetime);
DROP TEMPORARY TABLE IF EXISTS rst;
END
I'm not the greatest at using stored procedures. This one is running very slow - it takes about 6 seconds to run. The queries individually run in milliseconds, so I was expecting this to take 100 milliseconds at most. I can't figure out what I'm doing wrong that's causing this to run so slowly. Does anyone have any ideas on how to make this run quicker? I can create a PHP script to do the same thing, but I'm trying to get better at using stored procs and functions in MySQL - and this is far less code. I welcome any and all advice.
