CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
1mr3yn
1mr3yn

Reputation: 74

mysql insert batch vs. insert loop (codeigniter)

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!

Upvotes: 0

Views: 5149

Answers (1)

Yorick de Wid
Yorick de Wid

Reputation: 929

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.

Upvotes: 4

Related Questions