If you have to import let’s say for example Country Name and County Code into a database from a CSV file like this: (source)
Code,English Name,French Name
AD,Andorra,Andorre
AE,United Arab Emirates,Émirats arabes unis
AF,Afghanistan,Afghanistan
AG,Antigua and Barbuda,Antigua-et-Barbuda
The database schema is as follows:
country_code {country_code,country_name}
In PHP, you could have done:
(Method 1)
$file_handle = fopen(dirname(__FILE__) .
"/countries.csv", "r");
while (!feof($file_handle) ) {
$line = fgetcsv($file_handle, 1024);
$line[1] = mysql_real_escape_string($line[1]);
$sql = "INSERT INTO country_code
(country_code,country_name)
VALUES ('{$line[0]}','{$line[1]}');";
$db->query($sql);
}
fclose($file_handle);
But it takes about 3 seconds to import the CSV attached. If you have a file with lets say with 50,000 records, it might take hours to run. It is really annoying to have Core i7 computers with lots of RAM and yet you run a process which takes hours to run using only 0 – 1% CPU.
I/O from the Hard Drive is limiting the speed tremendously as each time a record is being read, the HDD is accessed, MySQL then accesses the HDD to save and so on. Even a Solid State Drive (SSD) doesn’t solve the problem (I tried. Still takes hours).
I had to find a way to load a somewhat big chunk of the file into memory then execute the insert query for every 1000 records. I came up with the following code:
(Method 2)
$file_handle = fopen(dirname(__FILE__)
. "/countries.csv", "r");
$init_sql = "INSERT INTO country_code
(country_code,country_name) VALUES ";
$sql = $init_sql;
$count = 1;
$i = 0;
while (!feof($file_handle) ) {
$line = fgetcsv($file_handle, 1024);
$line[1] = mysql_real_escape_string($line[1]);
$sql .= "('{$line[0]}','{$line[1]}'),";
if($count++ >= 1000){
$i++;
$count = 0;
$sql = rtrim($sql , ',');
$sql .= ";";
$db->query($sql);
$sql = $init_sql;
}
}
$sql = rtrim($sql , ",");
$sql .= ";";
$db->query($sql);
fclose($file_handle);
Method 1 exec time: 3.0624158382416 s
Method 2 exec time: 0.010763883590698 s
Method 2 is 284 x faster
The import time is considerably improved. According to my benchmarks, it is 270-285x faster.
I was once importing US ZIP codes in a database. It contained between 43k records. The second method allows importing it in less than 5 seconds 😉
