11

Should PDO prepared statements be freed up after use? And if so, how? Specifically I'm asking about MySQL - how can you, and should you, call DEALLOCATE PREPARE though PDO. (Edit: To clarify, this question is not referring to emulated prepares, but real prepares. )

Also - will this free the results set (when large)?

Explanation:

I have seen code along the lines of 

$stmnt = $db->prepare($sql);
$stmnt->execute($aParams);
$stmnt = null;

which led me to wondering what this does, when, and if f unset($stmnt); would be different?

The manual indicates that

When the query is prepared, the database will analyze, compile and optimize its plan for executing the query. [...] By using a prepared statement the application avoids repeating the analyze/compile/optimize cycle.

which tends to suggest you should unallocate the statement, and MySQL has the capability. So,

  1. Can you call DEALLOCATE PREPARE, and how
  2. Should you do it?
  3. And can anyone confirm that setting statement to null (or unsetting the statement) will do the same as "free_result" for mysql_ and mysqli_?
  4. Does it happen immediately, or does it wait for garbage collector to kick in?

For completeness, another SO question referring to "free_result" and "close" functions for mysqli_() suggests that freeing the statement actually adds time (unless you have large memory usage and need the space). But "free_result" is different from freeing the SQL server from having the prepared statment cached.

Community
  • 1
  • 1
Robbie
  • 17,605
  • 4
  • 35
  • 72
  • Are you specifically turning off emulated prepares? By default the prepared statements are emulated in the driver, and are freed at the end of the request (when everything is GCd). What would be the point of deliberately freeing them anyway? The performance gain is afforded by re-using prepared statements where possible. To address point 3, [free_result is called when a statement is destroyed](http://lxr.php.net/xref/PHP_5_4/ext/pdo_mysql/mysql_statement.c#55) (unset, end of request, etc) - Without testing, I believe dtors are called immediately. – Leigh Dec 07 '12 at 13:43
  • Yes, I'm not emulating prepares - sorry, should have made that clear in the question (but otherwise the question is pointless). Why free them? Because there are several singly called statements that are not used again for that connection - so they might as well be freed up (the optimisations are not cached). Those that do get re-used I hold on to (until no longer needed, then the same question applies as to how to free them). – Robbie Dec 08 '12 at 11:29

2 Answers2

4

Should PDO prepared statements be freed up after use? And if so, how?

In the context of MySQL? No. Why?

PDO emulates prepared statements by default. This means that PDO itself does the parameter replacement, escaping, etc, and sends chunks of SQL down the line instead of using native prepared statements.

While you can turn it on, you still don't need to expressly close the handle unless you are also using unbuffered queries. Merely letting the statement handle go out of scope or setting it to null will not close the cursor. Again, this only matters if you're using unbuffered queries. If you are not, then letting it go out of scope or setting it to null is enough to close the handle cleanly.

You also linked to DEALLOCATE PREPARE. That syntax is only needed when manually calling PREPARE with an SQL string. This is a completely and totally separate action than MySQL C-level API-based prepared statements, which is what PDO_MYSQL is using. (Okay, maybe you're using mysqlnd, but it's effectively the same thing.)

Community
  • 1
  • 1
Charles
  • 50,943
  • 13
  • 104
  • 142
  • Sorry - I should have clarified that I'm referring to using real prepared statements. I can't find the source, but I remember reading that calling "prepare()" does call "PREAPRE" to the database - and that leads to the question of should I call DEALLOCATE PREPARE. I'm going to test that the PREPARE isn't called as you claim - I have all queries being logged at work, so I'll get back to you). I'm not buffering queries. Thanks for the response - I'll follow up in a day. – Robbie Dec 08 '12 at 11:23
  • If you see the actual `PREPARE` SQL *anywhere* when dealing with the wire-level API that the C code (and mysqlnd) uses, I'll eat a hat. That'd be so ... *wrong* ... that my mind would be blown for *weeks* about how wrong it was. – Charles Dec 08 '12 at 11:34
  • 3
    No hat eating required - I just checked the logs and it's exactly as you described. PREPARE doesn't appear, but a call to prepare() does - fitting with the C-Level API you linked. And mysql_stmt_close(stmt)) also appears to be called immediately as well, presumably when setting the stmnt to "null" in PHP (limited testing obviously, but "stmt close" always following in the log). Thanks for the pointers - I'll rest east that I'm doing it right. – Robbie Dec 08 '12 at 11:54
1

Yes. When you are done with the prepare statement you can set it to NULL or use unset().

For a script with multiple queries and large databases, this makes a difference. You can test with:

$before = memory_get_usage();
$stmt = NULL;
die(memory_get_usage() - $before);

For me, this saved 20MB of memory, which was crashing the script later.

William Entriken
  • 37,208
  • 23
  • 149
  • 195
  • The question is not about pdo statement but about prepared statement. While to save your 20MB you should have been using an unbuffered query – Your Common Sense May 18 '16 at 17:12
  • This is related `PDOStatement` is a prepared statement and also a result set. Maybe the language would be more elegant to separate those. Unbuffered queries introduces additional design constraints. Specifically you may not have multiple active unbuffered queries open on a connection, which was an unacceptable constraint in my application. – William Entriken Dec 16 '18 at 21:20