Geek Answers Handbook

MySQL - SQL code optimization

I am new to writing SQL and have just created some procedures to add data to my MySQL database. The problem is that it is extremely slow due to the large number of requests. Now I loop through each record in the table containing the unsorted raw data, and then take this data point and add it to the database. This gets complicated as I have a number of FCs that I have to deal with.

Can you help me optimize this?

As an example, add the specified table: CALL add_table1(112,15);

Data Addition Procedure
- Primary Procedure

CREATE PROCEDURE `add_table1`(
    IN c_id INT UNSIGNED;
    IN t_id INT UNSIGNED;
)
BEGIN
    -- Table variables
    DECLARE r_id INT UNSIGNED;
    DECLARE dh_name VARCHAR(50);
    DECLARE d_value DECIMAL(20,10);

    -- Loop variables
    DECLARE done BOOLEAN;

    -- Cursor for measurement table
    DECLARE m_cur CURSOR FOR 
        SELECT Run_ID, DataHeader_Name, Data_Value 
        FROM `measurements`.`measurement_20131029_152902`;

    -- Handlers for exceptions
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Set start time
    UPDATE `measurements`.`queue` 
        SET Start_Time = NOW() 
        WHERE Experiment_ID = 112 AND Procedure_Name = 'add_table1';

    -- Loop through measurement table
    OPEN m_cur;
    m_loop: LOOP
        FETCH m_cur INTO r_id, dh_name, d_value;
        IF done THEN
            CLOSE m_cur;
            LEAVE m_loop;
        END IF;
        CALL add_measurement(dh_name, d_value, t_id, c_id, r_id);
    END LOOP m_loop;
END

Procedure for adding a dimension
- Secondary procedure called fromadd_table1

CREATE PROCEDURE `add_measurement`(
    IN measurement_header VARCHAR(50),
    IN measurement_value DECIMAL(20,10),
    IN tool_id_var INT UNSIGNED,
    IN config_id_var INT UNSIGNED,
    IN run_id_var INT UNSIGNED
)
BEGIN
    -- Variables representing FKs
    DECLARE data_header_id INT UNSIGNED;
    DECLARE tool_header_link_id INT UNSIGNED;
    DECLARE tool_data_id INT UNSIGNED;
    DECLARE tool_data_link_id INT UNSIGNED;

    -- Add header
    INSERT IGNORE INTO data_headers(DataHeader_Name) 
        VALUES(measurement_header);
    SET data_header_id = (SELECT DataHeader_ID 
        FROM data_headers WHERE DataHeader_Name = measurement_header);

    -- Link header to tool
    INSERT IGNORE INTO tool_header_link(DataHeader_ID, Tool_ID)
        VALUES(data_header_id, tool_id_var);
    SET tool_header_link_id = (SELECT ToolHeaderLink_ID 
        FROM tool_header_link
        WHERE DataHeader_ID = data_header_id AND Tool_ID = tool_id_var);

    -- Add measurement
    INSERT IGNORE INTO tool_data(Data_Value) VALUES(measurement_value);
    SET tool_data_id = (SELECT ToolData_ID
        FROM tool_data WHERE Data_Value = measurement_value);

    -- Link measurement to header and configuration
    INSERT IGNORE INTO 
        tool_data_link(ToolHeaderLink_ID, ToolData_ID, Run_ID)
        VALUES(tool_header_link_id, tool_data_id, run_id_var);
    SET tool_data_link_id = (SELECT ToolDataLink_ID FROM tool_data_link
        WHERE ToolHeaderLink_ID = tool_header_link_id
        AND ToolData_ID = tool_data_id AND Run_ID = run_id_var);

    -- Link measurement to experiment configuration
    INSERT IGNORE INTO tool_link(ToolDataLink_ID, Config_ID)
        VALUES(tool_data_link_id, config_id_var);
END


. TRANSACTION . , 36 , 5 ! FK. - , . , .

:

    START TRANSACTION;

    -- Loop through measurement table
    OPEN m_cur;
    m_loop: LOOP
        FETCH m_cur INTO r_id, dh_name, d_value;
        IF done THEN
            CLOSE m_cur;
            LEAVE m_loop;
        END IF;
        CALL add_measurement(dh_name, d_value, t_id, c_id, r_id);
    END LOOP m_loop;

    COMMIT;


, . , . , . , 2,5 !

!

CREATE PROCEDURE `add_table`(
    IN config_id_var INT UNSIGNED
)
BEGIN
    START TRANSACTION;

    -- Add header
    INSERT IGNORE INTO data_headers(DataHeader_Name)
        SELECT DataHeader_Name
        FROM `measurements`.`measurement_20131114_142402`;

    -- Add measurement
    INSERT IGNORE INTO tool_data(Data_Value)
        SELECT Data_Value
        FROM `measurements`.`measurement_20131114_142402`;

    -- Link measurement to header and configuration
        -- INSERT Non-Unique Values
    INSERT IGNORE INTO tool_data_link(DataHeader_ID, ToolData_ID, Run_ID)
        SELECT h.DataHeader_ID, d.ToolData_ID, m.Run_ID
        FROM `measurements`.`measurement_20131114_142402` AS m
        JOIN data_headers AS h ON h.DataHeader_Name = m.DataHeader_Name
        JOIN tool_data AS d ON d.Data_Value = m.Data_Value;
        -- INSERT Unique Values
    INSERT IGNORE INTO tool_data_link(DataHeader_ID, ToolData_ID, Run_ID)
        SELECT h.DataHeader_ID, d.ToolData_ID, m.Run_ID
        FROM `measurements`.`measurement_20131114_142402` AS m
        LEFT OUTER JOIN data_headers AS h ON h.DataHeader_Name = m.DataHeader_Name
        LEFT OUTER JOIN tool_data AS d ON d.Data_Value = m.Data_Value
        WHERE ((h.DataHeader_Name IS NULL) OR (d.Data_Value IS NULL));

    -- Link measurement to experiment configuration
        -- INSERT Non-Unique Values
    INSERT IGNORE INTO tool_link(ToolDataLink_ID, Config_ID)
        SELECT tdl.ToolDataLink_ID, config_id_var
        FROM tool_data_link AS tdl
        JOIN data_headers AS h ON h.DataHeader_ID = tdl.DataHeader_ID
        JOIN tool_data AS d ON d.ToolData_ID = tdl.ToolData_ID;
        -- INSERT Unique Values
    INSERT IGNORE INTO tool_link(ToolDataLink_ID, Config_ID)
        SELECT tdl.ToolDataLink_ID, config_id_var
        FROM tool_data_link AS tdl
        LEFT OUTER JOIN data_headers AS h ON h.DataHeader_ID = tdl.DataHeader_ID
        LEFT OUTER JOIN tool_data AS d ON d.ToolData_ID = tdl.ToolData_ID
        WHERE ((h.DataHeader_ID IS NULL) OR (d.ToolData_ID IS NULL));

    COMMIT;
END


, . , ; , , .

. . 400 , . , , , , , .

, , .

+4
2

, :

-- Link measurement to header and configuration
INSERT IGNORE INTO tool_data_link(DataHeader_ID, ToolData_ID, Run_ID)
    SELECT t1.DataHeader_ID, t2.ToolData_ID, t3.Run_ID
    FROM data_headers t1, tool_data t2, runs t3
    WHERE (( t1.DataHeader_Name in (
        SELECT DataHeader_Name
        FROM `measurements`.`measurement_20131114_110059`)
    ) AND ( t2.Data_Value in (
        SELECT Data_Value
        FROM `measurements`.`measurement_20131114_110059`)
    ) AND ( t3.Run_ID in (
        SELECT Run_ID
        FROM `measurements`.`measurement_20131114_110059`)));

SELECT :

    SELECT h.DataHeader_ID, d.ToolData_ID, m.Run_ID
    FROM measurements.measurement_20131114_110059 AS m
    JOIN data_headers AS h ON h.DataHeader_Name = m.DataHeader_Name
    JOIN tool_data AS d ON d.Data_Value = m.Data_Value
    ;

. DataHeader_Name Data_Value . (: )

, _ *. , .

+1

, . .

What your code does:
-1: Reads the records from measurement_xxxx.... table and for each one it executes 5 insert.

How this can be writen with set logic: 
-1: make the first insert for all the records from measurement_XXXX....
-2: make the second insert for all the records from measurement_XXX....
.......

This will change the code like the following: ( I did not test the code, so it is a guideline ) 

CREATE PROCEDURE `using_set_logic`(
    IN c_id INT UNSIGNED;
    IN t_id INT UNSIGNED;
)
begin
-- the first insert 
insert into data_headers(DataHeader_Name) 
select DataHeader_Name FROM `measurements`.`measurement_20131029_152902
-- the second insert if the dataheadername is uniqueu
insert into tool_header_link(DataHeader_ID, Tool_ID)
select DataHeader_ID, t_id from data_headers where DataHeader_Name in (
    select DataHeader_Name FROM `measurements`.`measurement_20131029_152902
)
--the second insert if the dataheadername isnot unique.
--take the last id for the dataheaders before the first insert 
--take the last id for the dataheaders after the insert
--use those values to bound the data header id.
insert into tool_header_link(DataHeader_ID, Tool_ID)
select DataHeader_ID, @t_id from data_headers 
where DataHeader_id between @beforeFirstInsert and @afterFirstInsert

end
+2

More articles:


All Articles