CSV import - too slow

[budimir]

Guys,

 

I'm using this code to import aorund 130.000 rows from CSV file. Code is working fine, but it's really slow. Do you have any ideas how to make it faster?

 

I appreciate all the help I can get!

 

//Upload File
if (isset($_POST['submit'])) {

if (is_uploaded_file($_FILES['filename']['tmp_name'])) {

echo "<h1>" . "Dokument ". $_FILES['filename']['name'] ." uspješno uvezen." . "</h1>";
echo "<h2>Prikaz sadržaja:</h2>";

readfile($_FILES['filename']['tmp_name']);
}


//Import uploaded file to Database
$handle = fopen($_FILES['filename']['tmp_name'], "r");


while (($data = fgetcsv($handle, 1000000, ";")) !== FALSE) {

$import="INSERT INTO kalkulacija_import_cjenik_stavke (id_cjenika,vrijeme,kataloski_broj,cijena_EUR) VALUES ('$id_cjenika','$vrijeme','$data[0]','$data[1]')";
mysql_query($import) or die(mysql_error());

}


fclose($handle);


print "Import završen!";


//view upload form

} else {


}
header("Location:import_hq_cjenik.php");
exit;

[kicken]

Don't use one insert per row.  Build up a multi-row insert query and use that instead.  How many rows you can do at once will depend on how big they are as there is a limit to how large the query can be (around 16megs by default I believe).  

[denno020]

I think this is what kicken suggested with the multi-row insert query: if you concatenate each new IMPORT to the end of the old one, in a big string with the appropriate seperators, then pass that to the mysql_query function. Could limit it to say 50 imports at a time or 100.. So have a counter that keeps track of how many IMPORTS you've added, then fire off the mysql_query function which the counter reaches 50, reset the string to nothing, and loop again.

$importString = ''; //empty string
$count = 0;
while(($data = fgetcsv($handle, 1000000, ";")) !== FALSE){
$importString .= "IMPORT etc";
$importString .= ","; //comma to separate between IMPORT lines
$count++;

if($count == 50){
mysql_query($importString, $databaseConnection) or die();
$importString = ''; //empty string again
$count = 0; //reset counter
}
}

 

I probably have the separator wrong, I always seem to mess that kinda thing up, but you get the general idea of what I thought you could do, and also what I think kicken is getting at, hopefully..

 

Hope that helps

 

Denno

[budimir]

Thanks guys!!! You helped me a lot.

 

I used the DELAYED option for now, although I will change soon as I see that option will be depriciated with new version of MySQL. But I have to finish the app now.

 

Denno, you're solution will be used next.

 

This is the best forum I have ever used! And you guys are the greatest!!!