MySQL overhead of calling a Stored Procedure

jdbcMySQLmysql-5.5stored-procedures

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!

Best Answer

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

So there are good reasons to use prepared statements:

  1. Save on query parsing

  2. Save on data conversion and copying

  3. Avoid SQL Injection

  4. Save memory on handling blobs

There are also drawbacks and chewats of using prepared statements:

  1. Query cache does not work

  2. Extra server round trip required if statement used only once

  3. 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

  4. 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

  5. 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

  6. Inconvenient list handling. Unlike in for example PEAR emulated prepared statements there is no nice way to pass list of values to IN

  7. 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.

Related Question