Why is MySQL insertion slower than JDBC?

Question

Table

  + ----------- + -------------- + ------ + ----- + --------- ---------- + ---------------- +
 |  Field |  Type |  Null |  Key |  Default |  Extra |
 + ----------- + -------------- + ------ + ----- + --------- ---------- + ---------------- +
 |  id |  int (11) |  NO |  PRI |  NULL |  auto_increment |
 |  name |  varchar (32) |  NO |  |  NULL |  |
 |  pid |  varchar (32) |  NO |  |  NULL |  |
 + ----------- + -------------- + ------ + ----- + --------- ---------- + ---------------- +

Procedure: Test

begin set @i=0; while @i<200 do insert into test.table_test (name, pid) values ('1', '1'); set @ i=@i +1; end while; end 

mileage:

 mysql> call test(); Query OK, 0 rows affected (17.24 sec) 

but when I insert data using jdbc, it should cost about 9 seconds to insert 10 ^ 5 rows (if I use batch insert, it only costs about 2.4 s to insert 10 ^ 5 rows).

java code:

 static String mySqlUrl="jdbc:mysql://127.0.0.1:3306/test?rewriteBatchedStatements=true"; static String sql = "insert into bael_test(name, pid) values(?, ?)"; public static void test_mysql_batch(int point){ Connection conn=getConn("mysql"); clear(conn); try { PreparedStatement prest = conn.prepareStatement(sql); long a=System.currentTimeMillis(); for(int x = 1; x <= 100000; x++){ prest.setString(1, "name"); prest.setString(2, "pid"); prest.addBatch(); if(x%point==0){ prest.executeBatch(); conn.commit(); } } long b=System.currentTimeMillis(); print("MySql batch insert 10^5 rows",a,b,point); } catch (Exception ex) { ex.printStackTrace(); }finally{ close(conn); } } 

So why is MySQL insertion slower than JDBC?

Answer 1

JDBC code performs an extended insert.

(We don’t see the actual SQL statement here, and we don’t see the binding of the first positional parameter in the SQL text, only the 2nd and 3rd parameters. I am going to make the assumption (possibly incorrect) that JDBC is null for an unbound parameter.)

Thus, the actual SQL statement sent by MySQL from the JDBC driver is:

 INSERT INTO test.table_test (id,name, pid) VALUES (NULL,'name','pid') ,(NULL,'name','pid') ,(NULL,'name','pid') ,(NULL,'name','pid') ,... 

In your MySQL procedure, you execute separate instructions:

 INSERT INTO test.table_test (id,name,pid) VALUES (NULL,'name','pid'); INSERT INTO test.table_test (id,name,pid) VALUES (NULL,'name','pid'); INSERT INTO test.table_test (id,name,pid) VALUES (NULL,'name','pid'); INSERT INTO test.table_test (id,name,pid) VALUES (NULL,'name','pid'); 

An expanded form of insertion will be much more efficient than separate insertion instructions. (There is a lot of hard work that MySQL does to execute each statement.)

To get the best performance from a MySQL procedure, you need to do something like:

 BEGIN SET @sqltext= 'insert into test.table_test (name,pid) values (''1'',''1'')'; SET @i=1; WHILE @i<200 DO SET @sqltext = CONCAT(@sqltext,',(''1'', ''1'')'); SET @ i=@i +1; END WHILE; PREPARE stmt FROM @sqltext; EXECUTE stmt; DEALLOCATE PREPARE stmt; END 

And to get the same degraded performance from JDBC, you will need to execute separate INSERT statements.

This is the biggest contribution to the big difference in performance that you see.

This does not mean that the only difference is, but it explains most of this 17 second elapsed time that you see when performing your procedure. A procedure with a single extended insert is likely to be 100 times faster than running 200 individual inserts.

You still have a performance difference, but at least you'll be closer to comparing apples to apples.

Answer 2

The Java code uses prepared statements, while your MySQL code does not work.

In other words, your procedure should check at each step of the cycle if the syntax of your query is correct, and also find the table on disk, while the prepared statement does this only and will simply paste the values ​​into the right place without further processing.

If you want to compare, you can use PREPARE http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html

Just writing it from memory so it could be wrong:

 PREPARE stmt1 FROM 'INSERT INTO test.table_test (name,pid) values(?,?)'; while @i<200 do EXECUTE stmt1 USING @i, @i;; set @ i=@i +1; end while; DEALLOCATE PREPARE stmt1;