Inserting values from multidim. array using PDO takes a long time. Is there a better way?

Question

I got an array of 650 rows. Inserting this using PDO takes between 10-15 seconds on my local computer. That's very slow. Is this because of disk read/write? Or could it be something else?

This is my array (first 4 rows):

Array
(
[0] => Array
    (
        [0] => 3
        [1] => 1
    )

[1] => Array
    (
        [0] => 3
        [1] => 2
    )

[2] => Array
    (
        [0] => 3
        [1] => 5
    )

[3] => Array
    (
        [0] => 8
        [1] => 1
    )
)

And this is my code:

$stmt = $this->db->prepare("INSERT INTO sl_link_store_category (item_a_ID, item_b_ID) VALUES (:item_a_ID, :item_b_ID)");

foreach($my_array as $row) {
    $stmt->execute(array(':item_a_ID' => $row[0], ':item_b_ID' => $row[1]));
}

SOLUTION

For those who is wondering, her eis my solution for inserting multiple rows
using only one $stmt->execute:

    $input_arr; // This array one has lots of values

    $sql = "INSERT INTO sl_link_store_category (field_a, field_b) VALUES ";

    $i = 0;
    // I create the query string with unique prepared values
    // I could probably have used a for loop since I'm not using any
    // values from $row
    foreach($input_arr as $row) {
      $i++;
      $sql .= "(:field_a_$i, :field_a_$i), ";
    }

    // Remove the last comma (and white space at the end)  
    $sql = substr(trim($sql), 0, -1);

    $stmt = $this->db->prepare($sql);

    // I need to create a new associative array with field name 
    // matching the prepared values in the SQL statement.
    $i = 0;
    $arr = array();

    foreach($input_arr as $row) {
      $i++;
      $arr[":field_a_$i"] = $row[0];
      $arr[":field_b_$i"] = $row[1];  
    }

    $stmt->execute($arr);
  }

then you may want to consider setting `innodb_flush_log_at_trx_commit=2` and `sync_binlog=0` in the MySQL configuration on your development machine. This will minimize disk flushing and can speed up your application by many factors. — Matthew, Apr 11 '11 at 17:10

score 3 · Accepted Answer · edited May 23 '17 at 11:47

3

The reason it might be that slow can vary based on alot of factors.

Consider using one query to insert multiple records PDO Prepared Inserts multiple rows in single query

edited May 23 '17 at 11:47

Community

1
1

answered Apr 11 '11 at 00:47

Sabeen Malik

10,816
4
33
50

Thanks. It got me on the right track. I actually tried it earlier, but didn't get it to work. But now I found the solution. See above. – Steven Apr 11 '11 at 10:27

Inserting values from multidim. array using PDO takes a long time. Is there a better way?

1 Answers1