Stored Procedure vs ETL Tool

By | February 13, 2016
0 Comment
Share the joy
  •  
  •  
  •  
  •  
  •  
  •  

This is a test for stored procedure and ETL tool kettle. The test is very easy: insert [1, 2, …, 10000] numbers to a table in Mysql(InnoDB). And I got below result.

kettle_sp2

kettle_sp3

kettle_sp

The conclusion is that performance between stored procedure and etl tool doesn’t differ more than 10 times. This is relatively fine if we are not too picky about performance.

———————————–

Code to insert rows in mysql:

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `insert_test`$$

CREATE DEFINER=`root`@`%` PROCEDURE `insert_test`()
BEGIN
DECLARE a INT DEFAULT 0;
SET a = 1;

WHILE a <= 10000 DO
INSERT INTO test VALUES(a);
SET a = a + 1;
END WHILE;

END$$

DELIMITER ;

SET autocommit = 0;
CALL test.`insert_test`();
COMMIT;