Insert sql query in loop a good practice or bad?

Question

I have a list of users which needs to be iterated using a foreach loop and inserted in to a table for every new row in db table.

$data['entity_classid'] = $classid;
    $data['notification_context_id'] = $context_id;
    $data['entity_id'] = $entity_id;
    $data['notification_by'] = $userid;
    $data['actionid'] = $actionid;
    $data['is_read'] = 0;
    $data['createdtime'] = time();
    foreach($classassocusers as $users){
            $data['notification_to'] = $users->userid;
            $DB->insert_record('homework.comments',$data,false);
        }

so using the insert query as given above is

1. A good practice or bad practice,
2. Shall i place any delay after every insert query execution?
3. what are the pros and cons of doing so?

Thanks

Answer 1

Using the query like that is a good practice in your case. You will have to insert a list of users anyway, so you will have to process many queries. No way around this!

I have no idea why you would want to place a delay after each insert. These methods are synchronous calls, so your code will be "paused" anyway during the execution of your query. So delaying it will just delay your code while nothing is progressing.

So your loop will not continue while executing a query. So don't delay your code even more on purpose.

Another way to do this is by executing one query though.

$user_data = "";
foreach($classassocusers as $users) {
   $user_data .= "('" . $users->userid . "', '" . $users->name . "'), ";
}

$user_data = substr($user_data, 0, strlen($user_data) - 2);

$query = "INSERT INTO `homework.comments` ( `id`, `name` )
          VALUES " . $user_data;

That's supposed to make a query like:

INSERT INTO `homework.comments` ( `id`, `name` )
VALUES ('1', 'John'),
       ('2', 'Jeffrey'),
       ('3', 'Kate');

(By the way, I made some assumptions regarding your $users object and your table structure. But I'm sure you catch the idea)

Answer 2

It all depends on your requirements.

If you run 500.000 of these updates in 5 minutes - every 15 minutes, your database will have a hard time. If you do this for 1.000 users every 15 minutes - this is a great approach.

When performance is demanded, concider the following:

1. Combine INSERT using the VALUES syntax, process every 500/1000.
2. Add a small timeout after the query.

Otherwise, this is an excellent approach!