Why MySQL insert is 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

run:

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

but when I insert data with jdbc, it only costs about 9 sec to insert 10^5 rows (If I use batch insert, it only costs about 2.4 sec 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 MySQL insert is slower than JDBC?

Answer 1

Your Java code uses prepared statements while your MySQL code does not.

In other words, your procedure has to check at each step of the loop if the syntax of your query is correct, as well as locate the table on disk while the prepared statement does this only this and will just insert the values at the correct place without further processing.

If you want to compare, you could 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;

Answer 2

The JDBC code is doing an extended insert.

(We don't see the actual SQL statement here, and we don't see a bind of the first positional parameter in the SQL text, only the 2nd and 3rd parameters. I'm going to make an assumption (possibly wrong) that JDBC is supplying a NULL value for the unbound parameter.)

So the actual SQL statement being sent to MySQL from the JDBC driver is of the form:

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, however, you are executing separate statements:

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');

The extended insert form is going to going to be much more efficient than individual insert statements. (There's a lot of heavy lifting that MySQL does for each statement execution.)

To get better performance out of the MySQL procedure, you would 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 kind of degraded performance out of the JDBC, you would need to execute separate INSERT statements.

That's the biggest contributor to the big performance difference you see.

That's not to say that's the only difference, but that explains most of that 17 second elapsed time you see on the execution of your procedure. The procedure with the single extended insert will likely be 100 times faster than running 200 separate inserts.

You're still left with a performance difference, but at least with that you'd be closer to comparing apples with apples.