Optimize master-detail insert statements

Question

Quest

After a day of running (against nearly 1 GB of data), a set of statements are tumbling down to 40 inserts per second. I am looking to increase that by an order of magnitude or two.

SQL Code

The code to insert the information comes in two parts: a master record and detail records. The master record:

INSERT INTO MONTH_REF (DISTRICT_ID, STATION_ID, CATEGORY_ID, YEAR, MONTH) VALUES
('101', '0066', '010', 1984, 07);

The detail records:

INSERT INTO DAILY (MONTH_REF_ID, AMOUNT, DAILY_FLAG_ID, DAY) VALUES ((SELECT ID
FROM MONTH_REF M WHERE M.DISTRICT_ID = '101' AND M.STATION_ID = '0066' AND M.CAT
EGORY_ID = '010' AND M.YEAR = 1984 AND M.MONTH = 07), 0, ' ', 1);

INSERT INTO DAILY (MONTH_REF_ID, AMOUNT, DAILY_FLAG_ID, DAY) VALUES ((SELECT ID
FROM MONTH_REF M WHERE M.DISTRICT_ID = '101' AND M.STATION_ID = '0066' AND M.CAT
EGORY_ID = '010' AND M.YEAR = 1984 AND M.MONTH = 07), 0.5, ' ', 2);

INSERT INTO DAILY (MONTH_REF_ID, AMOUNT, DAILY_FLAG_ID, DAY) VALUES ((SELECT ID
FROM MONTH_REF M WHERE M.DISTRICT_ID = '101' AND M.STATION_ID = '0066' AND M.CAT
EGORY_ID = '010' AND M.YEAR = 1984 AND M.MONTH = 07), 0, 'T', 3);

Proposed Solution

The proposed solution eliminates looking up each MONTH_REF_ID by storing it in a local variable, as follows:

INSERT INTO MONTH_REF (DISTRICT_ID, STATION_ID, CATEGORY_ID, YEAR, MONTH) VALUES
('101', '0066', '010', 1984, 07);

SET @month_ref_id := (SELECT LAST_INSERT_ID());

The detail statements then become:

INSERT INTO DAILY (MONTH_REF_ID, AMOUNT, DAILY_FLAG_ID, DAY) VALUES (@month_ref_id, 0, ' ', 1);
INSERT INTO DAILY (MONTH_REF_ID, AMOUNT, DAILY_FLAG_ID, DAY) VALUES (@month_ref_id, 0.5, ' ', 2);
INSERT INTO DAILY (MONTH_REF_ID, AMOUNT, DAILY_FLAG_ID, DAY) VALUES (@month_ref_id, 0, 'T', 3);

Constraints

The MONTH_REF table has an AUTO_INCREMENT primary key and is indexed on it. The DAILY table has no index and no primary key. A primary key can be added to the DAILY table, if it would help.

Question

What is a more efficient way to execute the (billion or so) insert statements than the proposed solution?

Thank you!

Answer 1

This solution works:

INSERT INTO MONTH_REF (DISTRICT_ID,STATION_ID,CATEGORY_ID,YEAR,MONTH) VALUES('101','QFEG','012',1973,08);
SET @month_ref_id := (SELECT LAST_INSERT_ID());
INSERT INTO DAILY (MONTH_REF_ID,AMOUNT,DAILY_FLAG_ID,DAY) VALUES(@month_ref_id,0,' ',1),(@month_ref_id,0,' ',2),(@month_ref_id,0,' ',3);

Inserts went up about four orders of magnitude.