[php] PDO's query vs execute

Gilean's answer is great, but I just wanted to add that sometimes there are rare exceptions to best practices, and you might want to test your environment both ways to see what will work best.

In one case, I found that query worked faster for my purposes because I was bulk transferring trusted data from an Ubuntu Linux box running PHP7 with the poorly supported Microsoft ODBC driver for MS SQL Server.

I arrived at this question because I had a long running script for an ETL that I was trying to squeeze for speed. It seemed intuitive to me that query could be faster than prepare & execute because it was calling only one function instead of two. The parameter binding operation provides excellent protection, but it might be expensive and possibly avoided if unnecessary.

Given a couple rare conditions:

1. If you can't reuse a prepared statement because it's not supported by the Microsoft ODBC driver.

2. If you're not worried about sanitizing input and simple escaping is acceptable. This may be the case because binding certain datatypes isn't supported by the Microsoft ODBC driver.

3. PDO::lastInsertId is not supported by the Microsoft ODBC driver.

Here's a method I used to test my environment, and hopefully you can replicate it or something better in yours:

To start, I've created a basic table in Microsoft SQL Server

CREATE TABLE performancetest (
    sid INT IDENTITY PRIMARY KEY,
    id INT,
    val VARCHAR(100)
);

And now a basic timed test for performance metrics.

$logs = [];

$test = function (String $type, Int $count = 3000) use ($pdo, &$logs) {
    $start = microtime(true);
    $i = 0;
    while ($i < $count) {
        $sql = "INSERT INTO performancetest (id, val) OUTPUT INSERTED.sid VALUES ($i,'value $i')";
        if ($type === 'query') {
            $smt = $pdo->query($sql);
        } else {
            $smt = $pdo->prepare($sql);
            $smt ->execute();
        }
        $sid = $smt->fetch(PDO::FETCH_ASSOC)['sid'];
        $i++;
    }
    $total = (microtime(true) - $start);
    $logs[$type] []= $total;
    echo "$total $type\n";
};

$trials = 15;
$i = 0;
while ($i < $trials) {
    if (random_int(0,1) === 0) {
        $test('query');
    } else {
        $test('prepare');
    }
    $i++;
}

foreach ($logs as $type => $log) {
    $total = 0;
    foreach ($log as $record) {
        $total += $record;
    }
    $count = count($log);
    echo "($count) $type Average: ".$total/$count.PHP_EOL;
}

I've played with multiple different trial and counts in my specific environment, and consistently get between 20-30% faster results with query than prepare/execute

5.8128969669342 prepare
5.8688418865204 prepare
4.2948560714722 query
4.9533629417419 query
5.9051351547241 prepare
4.332102060318 query
5.9672858715057 prepare
5.0667371749878 query
3.8260300159454 query
4.0791549682617 query
4.3775160312653 query
3.6910600662231 query
5.2708210945129 prepare
6.2671611309052 prepare
7.3791449069977 prepare
(7) prepare Average: 6.0673267160143
(8) query Average: 4.3276024162769

I'm curious to see how this test compares in other environments, like MySQL.