CODEWITHSUNDEEP
phpmysqlperformanceexecution-time
C. Ovidiu
C. Ovidiu

Reputation: 1134

PHP: insert 10.000 rows to mysql in one go performance

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.

Upvotes: 3

Views: 1914

Answers (2)

user1454117
user1454117

Reputation:

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).

Upvotes: 2

nl-x
nl-x

Reputation: 11832

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

Upvotes: 2

Related Questions