PDO Bind Value vs Insert

Besides the advantage of escaping value when using bind value in PDO, is there any difference in performance when using bind value with multiple values (prepare the statement on开发者_高级运维ce, but execute multiple time with different values) instead of a single insert statement like INSERT INTO table_name VALUES (value1, value2, value3),(value1, value2, value3),(value1, value2, value3)

Did some tests myself on 100,000 records. For a simpler scenario I did not use INSERT INTO but REPLACE INTO to avoid having to come up with new keys every time.

REPLACE INTO

A raw replace into of 3 columns example REPLACE INTO table_name VALUES (value1, value2, value3),(value1, value2, value3),(value1, value2, value3)...... on 100,000 rows took approx 14sec.

NORMAL BIND

Using a preparing the statement, binding the value and executing the prepared statement took around 33 seconds

foreach ($vars as $var) {
    $stmt->bindValue(':a' . $var["value1"], $var["value2"]);
    $stmt->bindValue(':b' . $var["value3"], $var["value4"]);
    $stmt->bindValue(':c' . $var["value5"], $var["value6"]);
    $stmt->execute();
}

BIND BUT 1 EXECUTE

Creating a long statement before preparing it, binding all the paramaters and executing once took around 22 seconds

REPLACE INTO clientSettings(clientId, settingName, settingValue) VALUES (:a1,:b1,:c1)
(:a2,:b2,:c2)
(:a3,:b3,:c3)
(:a4,:b4,:c4)
.......

Note that these are rough numbers and used creating REPLACE INTO (where fields where deleted and insert) on 100,000 records.

It's faster (for MySQL) if you use prepared statements. That way, the actual SQL is parsed once and data is sent multiple times - so the actual SQL layer for transforming your INSERT INTO ... isn't invoked every time you want to execute that particular insert, it's parsed only once and then you just send different parameters (different data) that you want to insert (or execute any other operation).

So not only does it reduce overhead, it increases security as well (if you use PDO::bindValue/param due to proper escaping based on driver / charset being used).

In short - yes, your insert will be faster and safer. But by what margin - it's hard to tell.