Acitve Record | How-To: Multirow inserts for a better performance?

[eluser]Unknown[/eluser]
Hi there,


In my web app, I have coded an Import-Tool which reads a CSV file and simply writes its data to the database.

Is there a way to tell CI, it should "collect" those inserts and perform a multirow insert rather than calling n-times $this->db->insert('table', $data); ??

Example:
CSV with 2.000 rows produces 2.000x the following code:

Code:

INSERT INTO `table` (`field_1`, `field_2`) VALUES ('content_1_field_1', 'content_1_field_2');
INSERT INTO `table` (`field_1`, `field_2`) VALUES ('content_2_field_1', 'content_2_field_2');
INSERT INTO `table` (`field_1`, `field_2`) VALUES ('content_3_field_1', 'content_3_field_2');
...
INSERT INTO `table` (`field_1`, `field_2`) VALUES ('content_1999_field_1', 'content_1999_field_2');
INSERT INTO `table` (`field_1`, `field_2`) VALUES ('content_2000_field_1', 'content_2000_field_2');

Is there a way to produce this code:

Code:

INSERT INTO `table` (`field_1`, `field_2`)
VALUES
('content_1_field_1', 'content_1_field_2'),
('content_2_field_1', 'content_2_field_2'),
('content_3_field_1', 'content_3_field_2'),
...
('content_1999_field_1', 'content_1999_field_2'),
('content_2000_field_1', 'content_2000_field_2');

I'm asking because of performance issues.



Thanks for your help


Greets,
Daigo

[eluser]Unknown[/eluser]
I am encountering the same problem again... are there any hints?

Thanks in advance


Greets,
Daigo

[eluser]Victor Michnowicz[/eluser]
I haven't tried it, but maybe a multi-dimensional array would work:

Instead of:

Code:

$data = array(
'name' => 'bob',
'age' => 20
);

$this->db->insert('people', $data);

This?:

Code:

$data = array(
array(
'name' => 'bob',
'age' => 20
),
array(
'name' => 'tom',
'age' => 22
),
array(
'name' => 'sue',
'age' => 24
)
);

$this->db->insert('people', $data);

I'm sure there is some totally obvious solution though that I can't think of.

[eluser]Unknown[/eluser]
Hello,

you are looking for LOAD DATA INFILE. Practically you send one query to the Database to tell it to grab your CSV file and import it into the target table.

Best regards!

[eluser]WanWizard[/eluser]
Very old post, and probably not relevant anymore, but CI 2.0 (and Reactor) has the database driver methods insert_batch() and update_batch() for exactly this purpose.

[eluser]marhnix[/eluser]
I can't find anything about insert_batch in the documentation

[eluser]Phil Sturgeon[/eluser]
It works exactly how you'd expect it. Just added some documentation which will be released as part of the next version (2.0.1)

http://d.pr/fnoK

[eluser]marhnix[/eluser]
Thanks for the link.