Running queries in For loop

Question

I am trying to remove running MySQL queries in for loops inside some code.

But I am not sure how best to achieve that.

using PHP PDO, with named parameters, how do I store the queries and then run them as a batch after the loop? so the only thing that happens in the for loop is the queries get built, but not executed until after the loop has finished?

here is example code:

for($i=$rowcount; $i>0; $i--){ 
    $id = $array[$i];
    $query  = "DELETE FROM table WHERE ID=:id AND Order=:order";
    $stmt = $conn->prepare($query);
    $stmt->execute(array('id' => $id, 'order' => $i));
}

I would recomend:-stackoverflow.com/a/4639672/4248328 . even this also can applied trough prepare statement — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Feb 1, 2017 at 20:08

Sven · Accepted Answer · 2017-02-01 20:21:11Z

2

My initial reaction would be: Why do this? What is your motivation? Simply avoiding SQL in loops, or do you have some sort of operational problems you want to avoid?

Your exact query would not be that easy to convert into a single query because you do have tuples of ID and Order values that have to match in order to be deleted. Also notice that prepared statements usually do not accept an arbitrary number of parameters, so even if you'd be able to transform your query into the form DELETE FROM table WHERE (ID=1 AND Order=1000) OR (ID=4 AND Order=1234)..., you'd have to somehow work out how to fill the first, second, third ... placeholder. Additionally, you'd be forced to generate that prepared statement dynamically, which is probably the opposite of how prepared statements should be done when it comes to security.

If you have performance problems because deleting 1000 entries one after the other has a big impact, there are alternatives: If you wrap the deletion inside one single transaction, then it probably doesn't matter that much how many entries you delete - ALL of them will be deleted once the transaction is committed. Also note that using prepared statements is one way to speed up database operations - but only if you prepare them only once before you loop, and inside the loop you'd only pass new parameters again and again.

So to wrap it up: Undoing SQL in loops is not the best thing if the programming problem you want to solve is better solved using a loop, and there is no other problem related to it. If however there is such a problem, it has to be analyzed and mentioned - removing the loops isn't an automatic success story.

answered Feb 1, 2017 at 20:21

Sven

71.2k10 gold badges113 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lawrence Cooke Over a year ago

Thanks for your response, my concern with it is that the code I have been given has a lot of these types of queries in them and I am concerned that under load , it will have a performance impact that can be avoided. Does something as simple as moving the prepare out of the loop and putting the query into a transaction make it more efficient?

Sven Over a year ago

More efficient? Yes. Prevent meltdown under load, guaranteed? No. Note that you are, in a way, doing premature optimization. You fear that the database will be the bottleneck, so you try to remove the most obvious problem in your code. What if loops are not the problem? What if, after having all loops removed, the database would still melt because of insufficient indexes? What if something entirely different will melt? Have you load-tested the code before you started your optimization? Even the most unrealistic load scenarion would give you hints where to start.

rulo4 · Accepted Answer · 2017-02-03 05:07:48Z

0

I suppose you need to ensure that all queries are executed, in other words you need a transaction:

$conn->beginTransaction();
try{
    $query  = "DELETE FROM table WHERE ID=:id AND Order=:order";
    $stmt = $conn->prepare($query);

    for($i=$rowcount;$i>0;$i--){ 
        $id = $array[$i];
        $stmt->execute(['id' => $id,'order'=>$i]);
    }

    $conn->commit();
}
catch(Exception $ex){
    $conn->rollBack();
}

edited Feb 3, 2017 at 5:07

answered Feb 1, 2017 at 20:17

rulo4

1051 gold badge1 silver badge10 bronze badges

Collectives™ on Stack Overflow

Running queries in For loop

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related