Except from helping avoid SQL injection, what are the benefits - if any - of using prepared statements inside a stored procedure (assume query is only executed once in the procedure)?

Does the engine have any way to keep the prepared statement in memory or is it a wash?

If the answer is depending upon environment, I interested in mysql with innodb.


So yes as you have mentioned about SQL injection the other advantage is what you guessed.

Quoting from documentation:

Using prepared statements with placeholders for parameter values has the following benefits:

  • Less overhead for parsing the statement each time it is executed. Typically, database applications process large volumes of almost-identical statements, with only changes to literal or variable values in clauses such as WHERE for queries and deletes, SET for updates, and VALUES for inserts.

  • Protection against SQL injection attacks. The parameter values can contain unescaped SQL quote and delimiter characters.

So yes it does cache the prepared statements and routines.

  • Well, first one should note that the caching appears to be per-session but moreover, it says that for stored procedures "the server converts and caches the entire program body" which leaves open the question as to whether or not prepared statements therein are cached separately (on top of already being part of the cached body) and, more to the point of the question, if it therefore does any good to use PREPARE inside the procedure. – chell Aug 11 '15 at 12:10
  • 2
    IIRC you can only prepare a statement stored in a variable, so it CANNOT be "compiled" with the procedure at all. So it might have a performance benefit only in case the procedure executes the same statement in a loop. It might be cached as other prepares statements per session, but it is not probable you would use the same statement in a procedure and out of it multiple times in the same session. – jkavalik Aug 11 '15 at 12:18
  • 1
    @jkavalik You should post an answer – chell Aug 11 '15 at 21:48

Does the engine have any way to keep the prepared statement in memory or is it a wash?

Prepared statement is stored in memory till explicit DEALLOCATE statement or connection termination and can be reused in the same connection if it was not destroyed - including from within another simple or compound statement.

Query result obtained by SELECT type prepared statement is stored in query cache on a universal basis independent of the source type of query and can be reused both by another execution of this or another prepared statement and by direct query when its text is identical to the SQL text of the first prepared statement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.