CODEWITHSUNDEEP
phpmysqlinsert
samxli
samxli

Reputation: 1596

Is it more efficient to construct a massive insert statement or many insert statements?

I'm importing a csv file to a mysql db. Haven't looked into bulk insert yet, but was wondering is it more efficient to construct a massive INSERT statement (using PHP) by looping through the values OR is it more efficient to do individual insert of the CSV rows?

Upvotes: 3

Views: 331

Answers (3)

p.marino
p.marino

Reputation: 6252

It is difficult to give an answer without knowing at least two more elements:

1) Is your DB running on the same server where the PHP code runs?

2) How "big" is the file? I.e. average 20 csv records? 200? 20000?

In general looping through the csv file and firing a insert statement for each row (please use prepared statements, though, or your DB will spend time parsing the same string every single time) would be the more "traditional" approach and would be efficient enough unless you have a really slow connectiong between PHP and the DB.

Even in that case, if the csv file is more than 20 records long you would probably start having problems with max statement length from the SQL parser.

Upvotes: 1

Donnie
Donnie

Reputation: 6351

Inserting in bulk is much faster. I'll typically do something like this which imports data 100 records at a time (The 100 record batch size is arbitrary).

$a_query_inserts = array();
$i_progress = 0;

foreach( $results as $a_row ) {

    $i_progress++;
    $a_query_inserts[] = "({$a_row['Column1']}, {$a_row['Column2']}, {$a_row['Column3']})";

    if( count($a_query_inserts) > 100 || $i_progress >= $results->rowCount() ) {

        $s_query = sprintf("INSERT INTO Table
            (Column1,
            Column2,
            Column3)
            VALUES
            %s",
            implode(', ', $a_query_inserts)
        );
        db::getInstance()->query($s_query);

        // Reset batch
        $a_query_inserts = array();
    }
}

There is also a way to load the file directly into the database.

Upvotes: 4

DJ Quimby
DJ Quimby

Reputation: 3699

I don't know the specifics of how PHP makes connections to mySQL, but every insert request is going to have some amount of overhead beyond the data for the insert itself. Therefore I would imagine a bulk insert would be much more efficient than repeated database calls.

Upvotes: 3

Related Questions