php - Enhance csv file database import -

php - Enhance csv file database import -

i'm using script below import big csv file database.

if table empty process takes 5 minutes finish on local machine.

if i'm using file update existing values on same table takes more 15 minutes finish.

my csv file contains 35,000 rows.

how can speed 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 $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 produse alias '%" . $alias . "%'" ); ( $i = 0; $i < $database->countrows(); $i++ ) { if ( $alias == $verificare['alias'][$i] ) { $alias = $this->increment_string( $alias, '_', 1 ); } else { $alias = $alias; } } $database->insert( sprintf( "insert 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++; } } }

and here incrementing function

function increment_string($str, $separator = '-', $first = 1){ preg_match('/(.+)'.$separator.'([0-9]+)$/', $str, $match); homecoming isset($match[2]) ? $match[1].$separator.($match[2] + 1) : $str.$separator.$first; }

first off, less - faster is. however, many of database-imports slow because of hard drive. not because of cpu, not because of insufficient ram - it's hard drive.

here's why: hard disk operates in terms of input output operations per second - i'll refer i/o. that's number manufacturers don't advertise. advertise things bandwith , burst read, useless numbers - dpi mouses.

a mechanical disk has relatively low number of i/os available. number varies depending on drive, can between 100 , 400 i/os. ssd has much higher number of i/os available, 5000 80k (and more).

that means mechanical disk can perform, say, 400 writes in 1 sec while ssd can 5000. problem database queries little in terms of info (about 4kb).

if simple math - 400 i/os * 4kb - number of ~1.6 mb/sec. indicates spending of i/os nut capacity of disk's bandwith.

that hints issue larger info writes per i/o. in mortal language, means should start transaction, issue several insert queries (say, 50 inserts) , commit transaction.

that way spent 1 i/o 50 inserts. in turn, it's literally 50 times faster. if utilize prepared statements, becomes more efficient because mysql doesn't have lex query every time send it.

i won't send code because should able prepare on own. also, code open sql injections. have few things modify, , if not sure prepared statements - shout back.

php mysql csv pdo