Should prepared statement be put inside function?

Question

This question is on performance and recommended program structure: if a prepared statement be put inside a function, each time the function is called, will the prepared statement be prepared again? For example:

function check($x)
{
    global $DB;
    $stmt = $DB->prepare(
        "SELECT id FROM table WHERE code = :code;"
    );

    $stmt->bindparam(':code', $x);

    try {
        $stmt->execute();
        return $stmt->fetchColumn();
    } catch (Exception $e) {
        ...
    }
}

Performance-wise, will pull the $DB->prepare() outside of the function be better? What is the recommended structure?

global $DB;
$stmt = $DB->prepare(
    "SELECT id FROM table WHERE code = :code;"
);
        
function check($stmt, $x)
{
    $stmt->bindparam(':code', $x);
    try {
        $stmt->execute();
        return $stmt->fetchColumn();
    } catch (Exception $e) {
        ...
    }
}

every time you call prepare, it prepares a statement. whether it's inside or outside a function is absolutely irrelevant. Your second variant is bad for the program structure: in the first variant you just have to call check($x) and in the second one you have to prepare first. — Your Common Sense, Dec 25 '20 at 15:09
the are other problems tho, such as `global $DB;` and `try...catch` — Your Common Sense, Dec 25 '20 at 15:10
@YourCommonSense I've been told that in the first approach, the statement would be prepared every time the function is called. In the second one, it only needs to be "prepare" once and run many times. Is that true? — Nick, Dec 26 '20 at 05:02
Yes, but how many times you are going to call it? What's the use case? — Your Common Sense, Dec 26 '20 at 05:04
Thanks for the reply. For now, I have three queries, two select to check by unique code — would be called about 500 times, one to insert — would be called about 1500 times. These three queries doesn't have table JOIN. Perhaps later some queries would have. — Nick, Dec 26 '20 at 07:08
In this case I doubt you need a function at all. Given you are going to check the code inside of some loop, you can just prepare outside of the loop and the rest just inside the loop. Especially given the code inside the function is too excessive and should consist of just two lines, `$stmt->execute(['code' => $x]); $result = $stmt->fetchColumn();` for which having a function would be overkill. — Your Common Sense, Dec 26 '20 at 07:24
why global is bad you can read [here](https://stackoverflow.com/questions/1557787/are-global-variables-in-php-considered-bad-practice-if-so-why). and try catch is just useless. You shouldn't write try-catch every time running a query - it makes absolutely no sense. — Your Common Sense, Dec 26 '20 at 07:26
@YourCommonSense Thank you very much. The purpose of creating function is (1) code reuse (2) code organization. — Nick, Dec 26 '20 at 08:24
A loop is much, much better for the code reuse than a function. When running things 500 times in a loop, you should use a loop, not a function. a function that consists of just **two lines** doesn't make your code any better. but yes, if you are so much inclined to your function, you can keep it. With two lines. But the best solution would be to [get all codes in **one** query](https://phpdelusions.net/pdo#in), then [store them into array](https://phpdelusions.net/pdo/fetch_modes#FETCH_KEY_PAIR) and then use this array to see whether a code exists. — Your Common Sense, Dec 26 '20 at 08:31
@YourCommonSense Thanks for the link, I am glad to realize that you are the author of the https://phpdelusions.net/pdo This is the first tutorial that let me understand what PDO is, and I come back to it from time to time. — Nick, Dec 26 '20 at 08:52

loydg · Answer 1 · 2020-12-27T02:24:21.137

For performance questions similar to this, it is also important to consider the scale, and cost of the operation in question, in this case, DB::prepare(), among other things.

If the function check() will only be called once, or a few times per request, page load, etc., would it really matter?

However, if the function check() will be called several 10s of times (or worse, 100s of times or more), then you should check what does DB::prepare() cost. You should benchmark. You can do it for a single operation, though in this case, I doubt (no concrete basis :P) it'll yield a 1ms (millisecond) difference. Instead do a worse-case scenario. If you think the function could be called 50 times in some cases, then benchmark on that count.

Honestly? Considering that that function calls a DB operation, network latency and DB server operation would cost you a lot more than that single DB::prepare() would.

Again, consider the scale and cost, and perform some benchmarks.

Update 2020-12-27 2:21 UTC

In response to @Nick's comment

Forgot to mention the type of performance to consider, eg, CPU, memory, storage, network roundtrip time, etc. The type will dictate the kind of benchmarks you can perform. For now, I'm going to assume CPU usage, specifically, how fast the operation can complete.

Let's say your average number of calls to check() function is 30 in a request. If it's a range, eg, 20~30, choose the upper limit.

We can start with just the mysqli::prepare() function in isolation:

$db = new mysqli(...);
$start = microtime(true);
for ($i = 0; $i < 30; $i++) {
    $db->prepare('SELECT id FROM table WHERE code = ?');
}
$time = microtime(true) - $start;
var_dump($time);  // eg, 0.004544...s = 4.5ms

If we get something like >10ms, or worse >100ms, (arbitrary) then perhaps it might be worth improving.

Next we need to look at the check() function itself. Calling mysqli::prepare() 30 times might take, say, 30ms, but if calling check() 30 times takes, say, 300ms, then you might want to look at else where first for improvement, eg, using caching instead of always calling DB query.

As for whether keeping mysqli::prepare() in the function, or out, you need to consider several things other than performance. One is code readability, and in general, code maintainability. I'm sure you've heard the phrase (paraphrasing here) Developer time is more valuable than CPU time.

Also, try to consider other solutions. Say calling mysqli::prepare() many times does add up, and is worth improving. Instead of moving it out of the function, maybe try keeping it in the function, but still calling it once by, say, storing the result in a static variable, eg:

function check($db, $code) {
    static $stmt = null;
    if (!$stmt) {
        $stmt = $db->prepare(...);
    }

    $stmt->bind_param(...);

    ...
}

I'm sure there are better solutions, but in this specific case, I would prefer this than moving the mysqli::prepare() out of the function.

Thanks. Can you give any advice on how to benchmark on `DB::prepare()`? What would be your advice, what you usually do, put statement preparation inside or outside of function? — Nick, Dec 26 '20 at 05:05
[why microbenchmarking is bad](https://stackoverflow.com/questions/2842695/what-is-microbenchmarking). Also, as it famously said, premature optimization is the root of all evil: you should benchmark what's already slow, not just benchmark things left and right just on a whim. It will lead you to doing weird things, introducing bugs and making your application overall slow — Your Common Sense, Dec 27 '20 at 07:11

Should prepared statement be put inside function?

1 Answers1

Update 2020-12-27 2:21 UTC