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

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

Thanks

I don't recognize your $DB object's class (homebrew?), but have a look at [batch inserts](http://stackoverflow.com/questions/1176352/pdo-prepared-inserts-multiple-rows-in-single-query) with pdo. Batch insert will probably lead to a major speedup. — Jacob, Aug 26 '11 at 09:22

Jules · Accepted Answer · 2011-08-26T09:57:09.150

9

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)

edited Aug 26 '11 at 09:57

answered Aug 26 '11 at 09:21

Jules

7,148
6
26
50

1

Well, you have to insert a list of users, but you don't have to do it one user at a time. – Jacob Aug 26 '11 at 09:23
True, let me add another way. – Jules Aug 26 '11 at 09:25
Hey @Jules There is something I Have in $data I have Updated My Question pls have a look again and rectify ur answer accordingly – OM The Eternity Aug 26 '11 at 09:43
@OM The Eternity Ah, you are using $data already. Then you can just change my variable name into anything else. I have no idea which $DB object class you are using, so I cannot help you with that. But I changed my answer not to duplicate $data. I hope you catch the idea! – Jules Aug 26 '11 at 09:58
@Jules My concern is not the name of variable my concern is the other values given above the forach loop to be used every time the loop is iterated... – OM The Eternity Aug 26 '11 at 12:36
Is the latter method, ie executing a single query, faster? – Marvzz Feb 22 '13 at 07:48
@Marvzz Yes it is more efficient and faster to use one query. – Jules Feb 24 '13 at 20:15

score 4 · Answer 2 · answered Aug 26 '11 at 09:25

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:

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

Otherwise, this is an excellent approach!

Insert sql query in loop a good practice or bad?

2 Answers2

Linked