MySQL overhead of calling a Stored Procedure Within Java, we access MySQL (v5.5.40) via JDBC -- a prepare statement, setting parameters then calling the stored procedure. It appears that every execute() has the overhead of 4 meta-calls (per MySQL query log) preceding the actual SP call:

SELECT name, type, comment FROM mysql.proc WHERE name like...
SHOW FUNCTION STATUS LIKE 'sp_one'
SHOW PROCEDURE STATUS LIKE 'sp_one'
SHOW CREATE PROCEDURE `db-name`.`sp_one`
CALL sp_one()


Is this the expected behavior on the server side? It just doesn't seem very efficient.

Here is the example of how we use the API:

  conn = DriverManager.getConnection(..);

  stmt = conn.prepareCall("{call sp_one(?,?)}");

  stmt.setString(1, "a");
  stmt.setString(2, "b");

  stmt.execute();


Please note that the following is true:


CallableStatement.getMetadata() is not used
Parameters are accessed by index, not by name


Thanks!
Percona wrote up a nice article MySQL Prepared Statements explaining pros and cons


  So there are good reasons to use prepared statements:
  
  
  Save on query parsing
  Save on data conversion and copying
  Avoid SQL Injection
  Save memory on handling blobs
  
  
  There are also drawbacks and chewats of using prepared statements:
  
  
  Query cache does not work
  Extra server round trip required if statement used only once
  Not all statements can be prepared. So you can’t use prepared API exclusively you’ll need to fall back to normal API for some statements
  Newer and sometimes buggy code. I had a lot of problems with PHP prepared statements. It is getting better but still it is less mature than standard API
  You can’t use placeholders in place of all identifiers. For example you can’t use them for table name. In certain version it even does not work for LIMIT boundaries
  Inconvenient list handling. Unlike in for example PEAR emulated
  prepared statements there is no nice way to pass list of values to IN
  Harder tracing. Logs were now fixed to include full statement text not
  only “Execute” but in SHOW INNODB STATUS you would still see
  statements without actual values – quite inconvenient for analyses
  


Since what you are experiencing is the expected behavior, you should not use Prepare Statements. You are making many calls to execute a single command. If you simply ran this

stmt = conn.createStatement();
rs = stmt.executeQuery("call sp_one('a','b')");


the complete parsing of the SQL, preparing for execution, and the parameters already being passed in through the SQL statement are all done on the server side with one call.

Please read that Percona blog for more details.