3
$array = Array(
  [a001] => Array(
    [0] => Array(
      [id] => 1789823
      [start] => 2014-09-29
      [end] => 2014-10-02
      [status] => 1
    )
  )
)

function to insert reservation:

    foreach ($array as $room_id => $item) {
      foreach ($item as $reservation) {
        db_insert('reservation')->fields(
          array(
            'room_id' => $room_id, 
            'id' => $reservation['id'],
            'start' => $reservation['start'],
            'end' => $reservation['end'],
            'status' => $reservation['status']
          )
        )->execute;
      }
    }

I have an multidimensional array $array and it has about 30000 records of $reservation index. Using looping method above in my personal computer, it takes about 30 minutes to insert all of reservation, my PC spec is (dual core, memory 2GB).

Is any better implementation to do insert job?

0

3 Answers 3

1

You should avoid executing insert many times. It is enough to call ->fields multiple times, like this:

$query = db_insert('reservation');
foreach ($array as $room_id => $item) {
  foreach ($item as $reservation) {
    $query->fields(
      array(
        'room_id' => $room_id, 
        'id' => $reservation['id'],
        'start' => $reservation['start'],
        'end' => $reservation['end'],
        'status' => $reservation['status']
      )
    );
  }
}
$query->execute();

This will speed up things, allowing database to prepare space for new data in one step, but with 30 thousands of records it still may take time. Be sure to tune your database accordingly - but that's out of the scope of Drupal Answers.

If you can't optimize DB enough, consider using Batch API for the job. It will make things a bit slower than the code above, but by importing a hundred or two each step you will avoid HTTP connection timeouts, PHP timeouts etc. Just make sure you only execute once per step with hundred records - even with Batch API it is good approach to group rows in as few inserts as practical.

0
4

Correct mass insert in Drupal is:

$values = [
   [ 'intvar' => 5,
    'stringvar' => 'hello world',
    'floatvar' => 3.14, ],
   [ 'intvar' => 6,
    'stringvar' => 'hi anybody',
    'floatvar' => 2.28, ],
   [ 'intvar' => 7,
    'stringvar' => 'good bye',
    'floatvar' => 7.62, ],
];

$q = db_insert('mytable')
  ->fields([ 'intvar', 'stringvar', 'floatvar', ] );
foreach ($values as $record) {
  $q->values($record);
}

$q->execute();

In your case it needed to insert by array parts (using "for" and "array_slice"), because 30000 is too much for one insert.

$step = 3000;
for ($i=0; $i<30000; $i+=$step) {
    $ins_part_values = array_slice($total_values, $i, $step);
    ...insert procedure
}
1
  • This method worked for me.
    – donut
    Apr 21, 2021 at 23:13
1

Here is a complete example with Drupal 8 and later (inserting into message table provided by message module):

$database = \Drupal::database();
$uuid = \Drupal::service('uuid');

$fields = [
  'template',
  'uuid',
  'langcode',
];

$values[] = [
  'template' => 'some_template',
  'uuid' => $uuid->generate(),
  'langcode' => 'en'
];
$values[] = [
  'template' => 'some_template',
  'uuid' => $uuid->generate(),
  'langcode' => 'en'
];

$query = $database->insert('message')->fields($fields);

foreach ($values as $record) {
  $query->values($record);
}

$query->execute();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.