0

I have a cache table which is rebuilt occasionally:

$Sql = 'INSERT INTO someTable (...fields...) VALUES (...values...)';
$stmt = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$items_to_insert[] = array();

foreach ($item as $i) {
    $details = array();

    // Lots of things here, such as:
    $details[':username'] = $['username'];

    $items_to_insert[] = $details
}

foreach ($items_to_insert as $i) {
    $stmt->execute($i);
}

What should I take into consideration to decide if it would be better off to run $stmt->execute() in the first foreach and to eliminate the second foreach and the $items_to_insert array? Is there any way to have the execute() run concurrently with the next foreach loop? This is for an application that will likely run on a variety of hardware so I am not interested in the specific case of benchmarking on my workstation, but rather I am interested in learning the intricacies of situation to make better use of PDO best practices.

dotancohen
  • 30,064
  • 36
  • 138
  • 197
  • 1
    your code as written will not work. name placeholders have to be UNIQUE within any given statement. you're trying to create MULTIPLE `:username` within that one query. If you want to do this sort of query, you'll need unique names, or use `?`-unamed ones. – Marc B Jul 15 '13 at 16:01

3 Answers3

1

This question has actually nothing to do with PDO but just with common sense. Running useless extra loop would be obviously worse than not running it at all.

However, it matters rather for the overall code quality, while performance-wise you hardly ever notice any difference.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
1

The best practice would be use none of foreach loops. Using sql queries in loop is a very bad idea, and kind of query (insert, select, update or delete) doesn't matter).

What you should to do is to make one query using a loop and execute it only once. Unfortunately, PDO doesn't provide any automatic way to do this, so you have to write it manually.

Look at the accepted answer of this question, it does exactly what you need: PDO Prepared inserts multiple rows single query

Community
  • 1
  • 1
ozahorulia
  • 9,798
  • 8
  • 48
  • 72
  • Thank you, but any of those solutions would preclude the use of prepared statements. – dotancohen Jul 15 '13 at 17:13
  • Yes. But there is no onther way. Unless you'll collect all arguments and a lot of placeholders in a loop and again execute it once. – ozahorulia Jul 15 '13 at 17:49
  • Why is best practice to not use a SQL query in a loop? I hear this repeated often, but I am not convinced that it applies to a prepared query. – dotancohen Jul 18 '13 at 09:54
  • Prepared query executes each time just like not prepared. So if you have a loop with 100 iterations, query will be executed 100 times. And this may cause database overloading. – ozahorulia Jul 18 '13 at 11:51
  • I don't think that is valid for prepared statements. The preparation takes time, but each run of the statement takes no longer than having multiple INSERTS in the same query. – dotancohen Jul 18 '13 at 12:45
  • @dotancohen I've made a benchmark for you. Here is the result: http://joxi.ru/6OnnUdg5CbCgSfnf7bo and here is the code: http://pastebin.com/G5xYdFYR As you can see, using prepared statements in loop is very-very slow. So yes, this is valid for prepared statements. – ozahorulia Jul 18 '13 at 13:14
  • Thanks, Hast. I just ran my own benchmarks with slightly modified versions of your code. My results are (much) closer, but the conclusion is the same: don't loop over the `execute()` statement. Thank you. Also, I did not realize that `execute($someArray)` would pop only the amount of values that it needs (as you have done in Case 3), so that one need not have $someArray be an array of arrays. This is a nice trick. Thanks! – dotancohen Jul 18 '13 at 13:49
0

It sounds like you want an all or nothing situation where either they all insert or they all fail.

Consider using Database Transactions for this. Begin your Transaction, move the insert up to the other loop and then do a commit at the end.