Codeigniter batch insert performance

Question

Does $this->db->insert_batch(); insert with 1 table connection or does it insert each row separately incurring overhead of opening connections?

Database Connection should be always one, whether batch or not. — Muhammad Usman
– Muhammad Usman, Commented Dec 26, 2011 at 14:01

RageZ · Accepted Answer · 2011-12-26 14:02:17Z

11

From the documentation of code igniter insert_batch do this kind of things

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

$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')

So it would produce only one query with all the values, normally this way faster then doing separate inserts.

answered Dec 26, 2011 at 14:02

RageZ

27.3k12 gold badges72 silver badges76 bronze badges

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

Comments

phirschybar · Accepted Answer · 2011-12-26 14:04:09Z

6

To answer your question: It uses one connection.

answered Dec 26, 2011 at 14:04

phirschybar

8,62912 gold badges53 silver badges67 bronze badges

Comments

Sobhan Atar · Accepted Answer · 2014-02-13 18:15:40Z

1

Actually @RageZ answer based on document is not always correct. Because it's totally based on the number of items you want to insert. When looking at codeigniter insert_batch() code, you can see that they slice batch inserts into 100 items.

// Batch this baby (Around line number 1077 in codeigniter 2.x)
for ($i = 0, $total = count($this->ar_set); $i < $total; $i = $i + 100)
{
    $sql = $this->_insert_batch($this->_protect_identifiers($table, TRUE, NULL, FALSE), $this->ar_keys, array_slice($this->ar_set, $i, 100));

    //echo $sql;

    $this->query($sql);
}

It means that your values will be slice to 100s inserts and if you uncomment the echo $sql part you can see what it's look like when you use insert batch for 101 items. So based on your connection preferences there can be more than one connection needed for inserting in db.

answered Feb 13, 2014 at 18:15

Sobhan Atar

5001 gold badge5 silver badges14 bronze badges

3 Comments

el_pup_le Over a year ago

@Ragez's answer may have been true when this question was asked, I think I was using CI 1.xx

Sobhan Atar Over a year ago

@amiawizard That can be right and the only way to ensure is to look at the code. As I know they haven't changed documents of this part from 1.x but it's possible that code had changed from 1.x to 2.x

Sobhan Atar Over a year ago

@amiawizard I checked 1.7.1 and 1.7.3 and there is no insert_batch function in them and it seems that this function introduced in version 2.x github.com/EllisLab/CodeIgniter/blob/develop/user_guide_src/…

Collectives™ on Stack Overflow

Codeigniter batch insert performance

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related