My question is simple. In what situations is it better to deallocate the prepared statement and thus removing it from memory and in what situations is it better to keep it in memory?

I have written a stored procedure that uses 2 prepared statements. One of the first few lines is to prepare the statement like so:

PREPARE selectStatementByJournalEntryAndLanguageQuery FROM "INSERT INTO results(id, content, title, language_id, journalentry_id) SELECT * FROM JournalEntryTitleAndContent jetc WHERE jetc.language_id = ? AND jetc.journalentry_id = ? LIMIT 1";
PREPARE selectStatementByJournalEntryQuery FROM "INSERT INTO results(id, content, title, language_id, journalentry_id) SELECT * FROM JournalEntryTitleAndContent jetc WHERE jetc.journalentry_id = ? LIMIT 1";

Now this stored procedure and thus these prepared statements could be used many times a day by many users. In this case it is better to not call DEALLOCATE on both prepared statements right? Because then the prepared statements remain in memory and do not have to be recompiled when someone else uses the stored procedure. But that raises a question about the 2 PREPARE statement lines. If i do not deallocate the prepared statements at the end and someone else starts the stored procedure, will the database then try to PREPARE the prepared statements again or will it ignore these 2 code lines when it detects that those prepared statements have already been prepared during another database stored procedure query session.

Thank you


[https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html] says:

A prepared statement is specific to the session in which it was created. If you terminate a session without deallocating a previously prepared statement, the server deallocates it automatically.

A prepared statement is also global to the session. If you create a prepared statement within a stored routine, it is not deallocated when the stored routine ends.

To guard against too many prepared statements being created simultaneously, set the max_prepared_stmt_count system variable. To prevent the use of prepared statements, set the value to 0.

I would note run DEALLOCATE unless I have a large number of prepared statements close to the maximum (by default 16382 in MySQL 8.0).

  • and what happens when the stored procedure runs again because of another database request? Will the 2 PREPARE statement lines be executed again causing duplicate prepare statements in the database? – Maurice Mar 3 '20 at 16:37

https://dev.mysql.com/doc/refman/8.0/en/prepare.html: says:

If a prepared statement with the given name already exists, it is deallocated implicitly before the new statement is prepared.

So it will be executed again but with a DEALLOCATE run before: so you won't have any duplicate prepared statements in the same session.

  • ok so for me it doesn't really matter whether i explicity call DEALLOCATE in the code, the statements get deallocated either way, am i correct? Would it be better to keep the prepared statements in memory instead? If so, where do i place the PREPARE statement definitions so that these 2 lines of code are only executed once during the first compilation of the stored procedure? I can image it would be more efficient to keep the statements in memory at all times – Maurice Mar 3 '20 at 16:56

Yes, you are correct you don't need to explicitely deallocate the prepared statements.

Yes, you need to place the PREPARE statements ideally in a procedure only called once at database session creation (otherwise there is no benefit from performance point of view) or to use a flag in the current session to know that you have already run the PREPARE statements.

  • how would i set such a flag, and how would I be able to use a prepared statement that was defined in another stored procedure? How do you load a reference to that prepared statement into the current stored procedure? Thank you – Maurice Mar 3 '20 at 18:15
  • just a sidenote, could you please use the comment section for answering the questions i put in the comments? It makes the thread look a bit cleaner :) – Maurice Mar 3 '20 at 18:16

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.