3
\$\begingroup\$

I'm using the script below to import a large .csv file to my database. If the table is empty the process takes about 5 minutes to finish on a local machine. If I'm using the file to update existing values on the same table it takes more than 15 minutes to finish. My .csv file contains about 35,000 rows.

How can I speed up the process?

    if ( $request->get( $_POST["action"] ) == "import" ) {

        $file = $upload->file_upload( "import", "media/import" );
        if ( file_exists( DIR_UPLOAD_PHOTO . "/media/import/" . $file ) ) {

            $file   = DIR_UPLOAD_PHOTO . "/media/import/" . $file;
            $handle = fopen( $file, "r" );

            if ( $handle ) {
                $lines = explode( "\r", fread( $handle, filesize( $file ) ) );
            }

            $total_array = count( $array );

            $x = 0;

            foreach ( $lines as $line ) {

                if ( $x >= 1 ) {
                    $data = explode( "|", $line );

                    $titlu          = trim( addslashes( $data[0] ) );
                    $alias          = $this->generate_seo_link( $titlu );
                    $gramaj         = trim( $data[1] );
                    $greutate       = trim( $data[2] );
                    $pret_total     = trim( $data[3] );
                    $pret_redus     = trim( $data[4] );
                    $poza           = trim( $data[5] );
                    $pret_unitar    = trim( $data[6] );
                    $categorie      = trim( $data[7] );
                    $brand          = trim( addslashes( $data[8] ) );
                    $descriere      = trim( addslashes( $data[9] ) );
                    $vizibil        = trim( $data[10] );
                    $cod            = trim( $data[11] );
                    $nou            = trim( $data[12] );
                    $cant_variabila = trim( $data[13] );
                    $congelat       = trim( $data[14] );
                    $tva            = trim( $data[15] );
                    $stoc           = trim( $data[16] );

                    if ( $cod != "" && $cod != " " ) {

                        $verificare = $database->select( "SELECT alias FROM produse WHERE alias LIKE '%" . $alias . "%'" );
                        for ( $i = 0; $i < $database->countRows(); $i++ ) {
                            if ( $alias == $verificare['alias'][$i] ) {
                                $alias = $this->increment_string( $alias, '_', 1 );
                            } else {
                                $alias = $alias;
                            }
                        }

                        $database->insert( sprintf( "insert into produse set
                            titlu='%s',
                            alias='%s',
                            gramaj='%s',
                            greutate='%s',
                            prettotal='%s',
                            pretredus='%s',
                            poza='%s',
                            pretunitar='%s',
                            categorie='%d',
                            brand='%s',
                            descriere='%s',
                            vizibil='%d',
                            cod='%s',
                            nou='%d',
                            cant_variabila='%d',
                            congelat = '%d',
                            tva = '%s',
                            stoc = '%d'

                            on duplicate key update

                            titlu='%s',
                            gramaj='%s',
                            greutate='%s',
                            prettotal='%s',
                            pretredus='%s',
                            poza='%s',
                            pretunitar='%s',
                            categorie='%d',
                            brand='%s',
                            descriere='%s',
                            vizibil='%d',
                            cod='%s',
                            nou='%d',
                            cant_variabila='%d',
                            congelat = '%d',
                            tva='%s',
                            stoc= '%d'",

                            $titlu, $alias,
                            $gramaj, $greutate, $pret_total, $pret_redus, $poza, $pret_unitar, $categorie,
                            $brand, $descriere, $vizibil, $cod, $nou, $cant_variabila, $congelat,
                            $tva, $stoc,

                            $titlu, $gramaj, $greutate,
                            $pret_total, $pret_redus, $poza, $pret_unitar, $categorie, $brand, $descriere,
                            $vizibil, $cod, $nou, $cant_variabila, $congelat, $tva, $stoc ) );

  }
  }
  $x++;
    }

   }
  }
\$\endgroup\$
  • \$\begingroup\$ That LIKE "% someVal %" query is very likely to result in a full-table scan, that's just evil. Learn to read the info MySQL EXPLAIN EXTENDED gives you, create the right indexes, use prepared statements, and use the LOAD DATA INFILE if you find yourself having to import large data sets. Lastly: do something about your formatting: there are standards \$\endgroup\$ – Elias Van Ootegem Nov 5 '14 at 15:14
  • \$\begingroup\$ Writing your code in English rather than Romanian might also help :P \$\endgroup\$ – user29120 Nov 5 '14 at 16:26
3
\$\begingroup\$

Well this might just be my shortest post to date:

Why? Why bother parsing, and manually inserting each and every row individually if you can use something like:

LOAD DATA INFILE "/path/to/data.csv"
INTO TABLE produse
(titlu, alias, gramaj) -- add all column names here
COLUMNS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r' -- \r or \n, seems to be \r in your case for some reason
IGNORE 1 LINES; -- if your CSV contains a line containing field names or something

Check the full syntax, and possibilities here. It contains references to how you can handle duplicate keys (hint: REPLACE is what you're probably after).


Update, some actual code review:

Having said all that, I think one thing about your code just looked too worrying not to mention: you're obviously using a DB abstraction layer of sorts, or some extension. Please, make sure to use prepared statements. It might also be worth looking into fgetcsv, to extract data from CSV files in the future. Instead of that pesky fread + explode mess, you can simply write:

$handle = fopen('/path/to/data.csv', 'r');
$delimiter = '|';//your delimiter
$db->beginTransaction();//use transactions if you want
//create a prepared statement for the inserts
$stmt = $db->prepare('INSERT INTO my.table (field1, field2, field3) VALUES (:val1, :val2, :val3)');
//set the bind keys we're going to use
$bindKeys = array(':val1', ':val2', ':val3');
while ($line = fgetcsv($handle, 1024, $delimiter))
{
    $line = array_map('trim', $line);//trim all of the values in one line
    $bind = array_combine($bindKeys, array_values($line));//set the keys for the bind
    $stmt->execute($bind);//insert the lot
    $stmt->closeCursor();//optional, depending on the db, this might be required
}
$db->commit();//save changes...
fclose($handle);//
$db = null;//close DB connection
echo 'All done!', PHP_EOL;

This snippet does, pretty much, the same thing as your code does, only it's a lot shorter, safer and cleaner... The DB extension I've used here is PDO, in case you were wondering, but you can do the same thing with mysqli_*, too.
IF one of the inserts fail, an exception should be thrown (unless you've set the errmode to silent). If this happens, catch the exception and call $db->rollBack(); to avoid storing partial data.

There's also the issue of a rather weird:

$cod = trim($data[11]);//no spaces possible

if ( $cod != "" && $cod != " " )
//second condition is IMPOSIBURU

You also have this weird way of adding else clauses where they really don't serve any purpose:

} else {
     $alias = $alias;//??
}

Just leave out the else, it's noise. It's code-smell.

You also increment $x to work out if you're processing the first line (first element of $lines) or not. Why bother, why not drop the first element in the array, and process the rest as a whole:

array_shift($lines);//removes $lines[0]
foreach ($lines as $line)
{
    //process rest
}
\$\endgroup\$
  • \$\begingroup\$ The LOAD DATA INFILE approach is definitely what you want here. You might note that if application server and MySQL server are different machines, that LOAD DATA LOCAL INFILE would actually be needed and would need to be enabled on MySQL server by setting local-infile=1. \$\endgroup\$ – Mike Brant Nov 6 '14 at 15:24
  • \$\begingroup\$ @MikeBrant: Yes, that's all true, but that's why I linked to the MySQL doc pages. If I wanted to be complete, I'd have to explain what to do in each and every case (DB is remote, local, if remote: Pipes, SSL and which key files are used,...) just too much to cover, that's up to the OP to figure out, IMHO. Anyway: I think we all agree that LOAD DATA [LOCAL] INFILE is the way to go, so I just decided to focus on what I feel are issues in the OP's code instead \$\endgroup\$ – Elias Van Ootegem Nov 6 '14 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.