Geek asks and answers

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

I am importing a csv file into mysql db. I haven’t looked at the bulk insert yet, but I wondered if it’s more efficient to build a massive INSERT statement (using PHP) by looping through the values ​​OR is it more efficient to make a separate insert of CSV strings?

+3
source share
3 answers

It is difficult to answer without knowing at least two other elements:

1) Does your database work on the same server that the PHP code is running on?

2) How big is the file? That is an average of 20 csv records? 200? 20,000?

, csv insert (, , ) "" , PHP .

, csv 20 , , , SQL.

+1

. - , 100 ( 100 ).

$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();
    }
}

.

+4

I don’t know the specifics of how PHP makes connections to mySQL, but each insert request will have some overhead outside the data for the insert itself. Therefore, I would suggest that bulk insertion would be much more efficient than database callbacks.

+3
source

More articles:


All Articles