PHP: insert 10.000 rows to mysql in one go performance

Question

I need to read an excel file with ~10.000 rows and save them into a table in MySQL. The approach I used is to create a foreach() loop and inside of it prepare, bind and execute each row.

The execution time is circa 130 second and I think that is pretty bad. That is on localhost, so when the script will be running live ( shared hosting ), the execution time will surely be higher.

This is the code

ini_set('max_execution_time', 300);

$time_start = microtime(true);

$user = 'root';
$pass = '';
$driver_options = array(
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'",
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
);
try {
    $dbh = new PDO('mysql:host=127.0.0.1;dbname=excel', $user, $pass, $driver_options);
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

set_include_path(get_include_path() . PATH_SEPARATOR . 'Classes/');

/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';
$inputFileName = 'original.xls';
try {
    $objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
} catch(Exception $e) {
    die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}

/*
    cap [X] - loc [AK]
    targa [D]
    fabbrica [F]
    provincia di residenza [V] - loc [AI]
    comune di residenza [W] - loc [AJ]
    data prima immatricolazione [AB]
    dati anagrafici [T] - loc [AG]
*/

$xls = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
$headers = $xls[1];
$loops = 0;
$rows = array_slice($xls, 1);

foreach ( $rows as $row ) {

    $excelData = array(
        'targa'                 => $row['D'],
        'fabbrica'              => $row['F'],
        'immatricolazione'      => $row['AB'],
        'cap'                   => $row['AK'] == '' ? $row['X'] : $row['AK'],
        'datiAnagrafici'        => $row['AG'] == '' ? $row['T'] : $row['AG'],
        'comuneResidenza'       => $row['AJ'] == '' ? $row['W'] : $row['AJ'],
        'provinciaResidenza'    => $row['AI'] == '' ? $row['V'] : $row['AI']
    );

    $insert = $dbh->prepare("
        INSERT INTO 
            data(targa, fabbrica, immatricolazione, cap, datiAnagrafici, comuneResidenza, provinciaResidenza)
            VALUES(:targa, :fabbrica, :immatricolazione, :cap, :datiAnagrafici, :comuneResidenza, :provinciaResidenza)
    ");
    $insert->execute($excelData);
    if ( $insert->rowCount() != 1 ) {
        echo 'Script interrupted at loop nr. '.$loops;
        break;
    }

    ++$loops;

}

$time_end = microtime(true);

$execution_time = ($time_end - $time_start);

echo '<b>Total Execution Time:</b> '.$execution_time.' s';

Is there any way I can optimize the code performance wise ? Is there something wrong with the loop ?

Thank you.

You could start by preparing outside of the loop - nothing changes in the query's structure, does it? — kero
– kero, Commented May 12, 2014 at 22:57
@kingkero That is stunning. I moved the prepare outside of the loop and the execution time went down to 8.40s — C. Ovidiu
– C. Ovidiu, Commented May 12, 2014 at 23:05
10,000 * 30+ columns = high RAM usage. I'm not particularly familiar with PHPexcel, but you might want to find a way to iterate over it row-by row rather than loading the entire file into memory. If your input file keeps growing you will eventually exhaust your available memory. — Sammitch
– Sammitch, Commented May 12, 2014 at 23:08

Community · Accepted Answer · 2017-05-23 12:23:21Z

2

If you can easily convert the XLS to CSV, you can use the LOAD DATA INFILE statement in mysql. That will be much faster.

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered May 12, 2014 at 23:00

nl-x

11.9k7 gold badges36 silver badges67 bronze badges

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

6 Comments

C. Ovidiu Over a year ago

Thank you for the suggestion, I will surely do so. Just a quick question, I am not a big fan of excel, but the conversion can be done without php and directly from excel itself ?

nl-x Over a year ago

@C.Ovidiu Yes. In Excel, use menu File -> Save as... and choose a csv format. Also you don't even need PHP to run the load data infile statement. This can also be done from your shell/workbench/phpmyadmin.

C. Ovidiu Over a year ago

Ok, I went to see about CVS and LOAD DATA INFILE, the problem is that the excel file has 20 columns, but I only need to store 7 of them and by what I understand, CVS are saved as they are in the database, meaning that all the columns in the file must also be columns in the database. Am i wrong ?

Mark Baker Over a year ago

LOAD DATA INFILE allows you to specify the columns that you actually want to load

nl-x Over a year ago

@C.Ovidiu LOAD DATA INFILE 'file.txt' INTO TABLE t1 (column1, @ignore, column2, @ignore, column3); `

|

user1454117 · Accepted Answer · 2014-05-12 23:08:05Z

2

You can send multiple values statements in a single query. I'd recommend batching your inserts in that way.

INSERT INTO table (...) VALUES (...), (...), (...);

You can collect your values for each row into an array, and then "flush" those values after a certain number have been collected (say 1000, for example).

answered May 12, 2014 at 23:08

user1454117

Collectives™ on Stack Overflow

PHP: insert 10.000 rows to mysql in one go performance

2 Answers 2

6 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Linked

Related