1
\$\begingroup\$

I have this procedure which is used for bulk upload data from a temporary table (temp_rules_master) into two other related tables.

It has 5 million records. The actual 5 million records to temp table upload take less than 10 mins using this query:

LOAD DATA LOCAL INFILE 'my_data.txt'
INTO TABLE temp_rules_master
COLUMNS TERMINATED BY '|' 
LINES TERMINATED BY '\n' IGNORE 1 LINES

But later the following procs takes around 10 hrs for running through all those 5 million records and do the processing.

Code Logic:

  • It iterates the master records one by one (5+ million)
  • And insert into 2 related tables compatibility_error (ce) and compatibility_rule (cr)
  • it does the below validations during the process
  • It checks if already a record exists in ce for the groupname. If exists, use that record id (@ERROR_ID) in the cr table. If it does not exist, creates a new ce record and use that id (@ERROR_ID) in the cr table.
  • Then checks if a record exists in the starting _material table for the starting_material_code. If exists use that starting_material_id (@SM_ID) in the cr table else set @SM_ID=null for it.
  • Generates a uuid (@CODE) for every cr record.
  • Insert a new cr record with the above-found values @CODE, @ERROR_ID, @SM_ID
  • Continues the iteration for all 5+ million records

Is there any way to improve the speed?

Stored Procedure:

DELIMITER $$
DROP PROCEDURE IF EXISTS load_rules$$

CREATE PROCEDURE load_rules()
BEGIN
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE groupname,starting_material_code,lower_limit,higher_limit,description,severity,active,create_date,created_by VARCHAR(255);
    DECLARE cur CURSOR FOR SELECT r.groupname,r.starting_material_code,r.lower_limit,r.higher_limit,r.description,r.severity,r.active,r.create_date,r.created_by from temp_rules_master r;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO groupname,starting_material_code,lower_limit,higher_limit,description,severity,active,create_date,created_by;

        IF finished = 1 THEN 
            LEAVE read_loop;
        END IF;

        /* Checks if an error already exists for the groupname */
        IF (SELECT ce.id FROM compatibility_error ce WHERE ce.code=groupname) THEN
            /* if exists use the existing error */
            BEGIN
                SELECT ce.id INTO @ERROR_ID FROM compatibility_error ce WHERE ce.code=groupname;
            END;
        ELSE
            /* If no error exists for the groupname create a new error record */
            BEGIN
                insert into compatibility_error(active,create_date,created_by,modified_by,modified_date,code,description,severity) 
                    values(true,create_date,0,0,NOW(),groupname,description,severity);
                SET @ERROR_ID=LAST_INSERT_ID();
            END;
        END IF;

        /* Checks if starting material exists */
        IF (select id from starting_material sm where sm.code=starting_material_code) THEN
            BEGIN
                select id INTO @SM_ID from starting_material sm where sm.code=starting_material_code;
            END;
        ELSE
            BEGIN
                SET @SM_ID = null;
            END;
        END IF;

        /* Insert rule */
        select UPPER(md5(UUID())) INTO @CODE;
        insert into compatibility_rule(active,create_date,created_by,modified_by,modified_date,code,description,groupname,higher_limit,lower_limit,compatibility_error_id,starting_material_id) 
            values(true,create_date,0,0,NOW(),@CODE,description,groupname,higher_limit,lower_limit,@ERROR_ID,@SM_ID);   

    END LOOP read_loop;

    CLOSE cur;

    select 'done...';
End$$

DELIMITER ;
\$\endgroup\$
  • \$\begingroup\$ Can you be more specific about the purpose of the code? "Processing" could mean almost anything... \$\endgroup\$ – Toby Speight Jul 3 at 15:02
  • \$\begingroup\$ @TobySpeight thanks I have updated now. \$\endgroup\$ – Jay Jul 4 at 5:49
  • \$\begingroup\$ In general, try avoiding cursors when you can. Since you need to insert into multiple tables, this can be tricky. A possible solution is to use a view which internally inserts into multiple tables. Check some of the interesting answers here: sqlservercentral.com/forums/topic/… \$\endgroup\$ – dfhwze Jul 20 at 5:53
  • \$\begingroup\$ Thx I was able to fix this by SET autocommit=0; before fetching the cursor and again reset to 1 after completing the loop. It gave a drastic improvement \$\endgroup\$ – Jay Jul 22 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.