mysql insert batch vs. insert loop (codeigniter)

Question

kindly enlightened me, which is faster/better approach or just the same between CI batch insert and loop insert.

$data = array(
          array(
               'title' => 'My title' ,
               'name' => 'My Name' ,
               'date' => 'My date'
          ),
          array(
             'title' => 'Another title' ,
             'name' => 'Another Name' ,
             'date' => 'Another date'
          )
         );

batch insert:

   $this->db->insert_batch('mytable', $data); 

  /* produces: INSERT INTO mytable (title, name, date) 
          VALUES ('My title', 'My name', 'My date'), 
                 ('Another title', 'Another name', 'Another date'); */

loop insert (php):

 for( $i = 0; $ < count($data); $i++ )
 {
    INSERT INTO mytable (title, name, date) 
    VALUES ($data[$i]['title'], $data[$i]['name'], $data[$i]['date'])
 }

thanks!

I would recommend a batch insert, or an insert with transactions, where you run all your inserts before comitting. — Zarathuztra
– Zarathuztra, Commented Feb 5, 2014 at 3:12
The batch should be faster. Each call to insert has overhead associated with it. One insert with many rows is normally faster. — Gordon Linoff
– Gordon Linoff, Commented Feb 5, 2014 at 3:30

Yorick de Wid · Accepted Answer · 2015-09-20 15:19:51Z

4

Batch inserts are usually faster since they process the data in once, were as the INSERT has some overhead (eg, the SQL optimizer cannot deduct certain steps). That said, you need to process a tremendous number of rows to create a notable difference.

If your curious if it would matter anyway, then don't forget to also measure the time it costs the framework to map the classes to the database table(s). There's a good chance the ORM consumes more resources than a looped SQL INSERT.

answered Sep 20, 2015 at 15:19

Yorick de Wid

96912 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql insert batch vs. insert loop (codeigniter)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related