0
$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?

  • Please use Drupal Coding Standards here. Like, 2 spaces as tab, consistent indentations etc. It'll make things easier to read for us, as we all got used to these standards. – Mołot Dec 10 '14 at 9:29
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

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
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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