Using stored procedures to bulk insert data into a database

< a > Preface

Recently there is a need to bulk insert data into the database concurrently.

The first use of stored procedures, like this:

Delimiter//CREATE PROCEDURE Load_part_tab () BEGIN declare v int default 0; While v < 8000000 does insert into part_tab values (V, ' testing partitions ', adddate (' 1995-01-01 ', (Rand          (v) *36520) mod 3652));     Set V = v + 1; End while; End//delimiter;

This is a single multiple insert. Time consumption completely can not endure, 20,000 records, time-consuming nearly half an hour.

Instead of using stored procedures, BULK INSERT, 10 concurrent, each inserting 20,000 records, a total of 119s time.

Check out some of the information on the Internet, first organized as follows. My own code, because the company business problems are not published.

< two > process 2.1 build Database tables:

CREATE TABLE Song (id int (one) not NULL auto_increment COMMENT ' autoincreament element ', name text not  NULL, datetime timestamp not NULL DEFAULT current_timestamp on UPDATE current_timestamp, rank int (11) Not NULL, PRIMARY KEY (ID)) Engine=myisam auto_increment=8102001 DEFAULT CHARSET=GBK

2.2 Create a processed stored procedure:

delimiter //drop procedure if exists sp_insert_batch; Create  procedure sp_insert_batch (In number int (one)) begin  declare i  int (one);   set i = 1;  while i <= number do     if mod (i,2000) =1 then       set @ Sqltext =concat (' ('), concat (' t ', I), ', ', ' Now (), ' ', ', ' Ceil (10*rand ()), ') ');     Elseif mod (i,2000) =0 then       set  @sqltext =concat (@sqltext , ', (' ', concat (' t ', i), ' ', ' ', Now (), ' ', ', Ceil (10*rand ()), ') ');        set   @sqltext =concat (' insert into song  (name,datetime,rank)  values ', @sqltext);        prepare stmt from  @sqltext;        execute stmt;       deallocate prepare stmt;       set  @sqltext = ";     else        set  @sqltext =concat (@sqltext, ', (' ', concat (' t ', I), ', ', ' Now (), ' ', ', ' Ceil (10*rand ()) ') ');    end if;     set i = i + 1;  END WHILE;  if  @sqltext < > '  then     set  @sqltext =concat (' insert into song  (name, Datetime,rank)  values ', @sqltext);     prepare stmt from  @sqltext;      execute stmt;     DEALLOCATE PREPARE  stmt;     set  @sqltext = ';  end if; end //delimiter ;

Explain: 2000 a batch is inserted, by stitching the outer values, then using INSERT into values (), (),... This is a bulk insert.

3. Use MYSQLSLAP tests such as:

Mysqlslap-uroot-p--concurrency=10,20,30,40,50,60--engine=innodb--create-schema= ' scmtg_dev '--query= ' call sp_ Insert_batch (2000); '

4. Test results:

Each client inserts 20,000 records, 10, 20, 30, 40 (of course, this test amount is very small), the trend is as follows:

< three > analysis

1. The efficiency of bulk inserts is significantly higher than a single multiple insert, which is beyond doubt. A single insert, a client 20,000 will take nearly half an hour, and BULK insert 10 clients, each 20,000 bar, only 120s.

2. stored procedures, SQL stitching is still more troublesome. To test more, avoid errors.
   

Using stored procedures to bulk insert data into a database