Read a csv file and insert into DB table

Question

I've found some resources about PHP performance optimization here and there but I don't know how to start. I send 130k CSV rows in approximatively 7min. My script read CSV file and send $batch_size row per query, while doing some string operations such as changing date to the correct format or replacing , with . for the floats.

$db = new PDO("mysql:host=$db_host;port=$db_port;charset=utf8","$db_username", "$db_password"); $csv_path = "path/to/myfile.csv"; $table = "TableName"; //Start of the query $query="USE ".$db_name.";"; $FilePath = $csv_path; $is_not_title_row=FALSE;//Usefull not to take the first row $row_batch_count=0;//Counter for batches //1 data = 1 part of the row $number_of_data=0; $data=''; // Open file in read-only mode $handle = fopen($FilePath, "r"); while (($data = fgetcsv($handle, 2000, ";" , chr(8))) !== FALSE) { $number_of_data = count($data)-1; // Generate MySQL query if($is_not_title_row) { $query .= "INSERT INTO ".$table." VALUES ("; //We read all the data of one row for ($c=0; $c <= $number_of_data; $c++) { $data[$c]=utf8_decode($data[$c]); $data[$c]=str_replace('/', '-', $data[$c]); $data[$c]=str_replace(',', '.', $data[$c]); //If it's a date, we convert it to the right date format if (DateTime::createFromFormat('d-m-Y', $data[$c]) !== FALSE) { $data[$c] = date("Y-m-d", strtotime($data[$c])); $query .= "'" . mres($data[$c]) ."',"; //If there is nothing, we send NULL value else if(mres($data[$c])==NULL){ $query .= "NULL,"; } else{ $query .= "'" . mres($data[$c]) ."',"; } //If this is the end of the INSERT INTO we remove the comma if($c==$number_of_data) { $query=substr_replace($query ,"", -1); } } $query .= ");"; $row_batch_count++; //If we are at the end of the batch, we send the query then start the creation of another if($row_batch_count==$batch_size) { echo "There is ".$row_batch_count." row sent<br/>"; echo "<br/><br/>"; $query_to_execute = $db->prepare($query); $query_to_execute->execute(); $query="USE ".$db_name.";"; $row_batch_count=0; } } //Usefull to remove the first row if(!$is_not_title_row){ $is_not_title_row=TRUE; } } //Without more batches, we execute the query(=we are at the end of the file) $query_to_execute = $db->prepare($query); $query_to_execute->execute(); $query_to_execute = NULL; 

Solution

Instead of doing one INSERT INTO for each row, I've simply inserted multiple row in a single INSERT INTO. The output query looks like that :

INSERT INTO Table VALUES (x,x,x),(x,x,x),(x,x,x),(... 

The csv insertion is now 30 times faster 🚀

Answer 1

What I found to greatly improves the speed of insert queries, is inserting multiple rows using a single query. You seems to have found the same solution, but it is implemented in a very roundabout way.

Your code could also benefit a lot from using functions. So, let's start with the most important one: How to insert multiple rows. It could look something like this:

function insertRows($db, $table, $rows) { $values = '(' . implode(',', array_fill(0, count($rows[0]), '?')) . ')'; $query = 'INSERT INTO '.$table . ' VALUES ' . implode(',', array_fill(0, count($rows), $values)); $statement = $db->prepare($query); $index = 1; foreach($rows as $row) { foreach($row as $value) { $statement->bindValue($index++, $value); } } $statement->execute(); } 

This bit of code has only one function: Insert a certain number of rows into the database as quickly as possible. This function doesn't read data, change it in any way, but it does use parameter binding for extra security. That's probably not needed in this case, but it also doesn't hurt. No more problems with quotes.

You can insert quite a few rows using this function, somewhere around 50 to 150 rows, depending on what your input looks like. Above a certain number the speed simply won't increase anymore, so there's no point in going to extremely large number of rows.

I would create another function for changing the row data from the format you received to the format you want to insert:

function reformatRow($row) { // <...put your code here...> return $row; } 

We can now also implement a function that does the above for multiple rows:

function reformatRows($rows) { foreach ($rows as $key => $row) { $rows[$key] = reformatRow($row); } return $rows; } 

Functions don't have to be complex to be useful. I actually will not use this function, but I thought it was nice to show it.

All that's now left to do is to read the input, so let's do that. I will use parts of your code:

const MAX_ROWS = 100; fgetcsv($handle, 2000, ";", chr(8)); // read header and dump it $rows = []; while (($row = fgetcsv($handle, 2000, ";", chr(8))) !== FALSE) { $rows[] = reformatRow($row); if (count($rows) >= MAX_ROWS) { insertRows($db, $table[$i], $rows); $rows = []; } } if (count($rows) > 0) { insertRows($db, $table[$i], $rows); } 

I haven't fully tested all this code.

I also don't think this code will be any quicker than yours. There's also no way for me to test any optimization, since I don't have your data, so I'm afraid I can't help you there. Reading data into a database just takes time. It is possible that fgetcsv() is not the optimal way to read a big CSV file, but I haven't tested that.

What I have tried to make clear is that by splitting your code into functional parts it become easier to understand, and more reusable. The insertRows() is, for instance, is, partly, reused from my own code. Once you've written a function like that, and it works, you can reuse it everywhere.

Answer 2

Initial thoughts

I agree with the ideas in the answer by KIKOSoftware (which is deleted - hopefully only termporarily). Creating functions can not only clean up the code but also allow for better testing. Using bound parameters is always a great idea. And using arrays to hold the strings and imploding them will eliminate the need to remove trailing commas.

Standards for readability

Consider adhering to PHP Standards Recommendations - especially PSR-12. The biggest concern I have for readability with this code is whitespace - e.g. around operators like =:

6.2. Binary operators

All binary arithmetic, comparison, assignment, bitwise, logical, string, and type operators MUST be preceded and followed by at least one space:

if ($a === $b) { $foo = $bar ?? $a ?? $b; } elseif ($a > $b) { $foo = $a + $b * $c; } 

¹

replacing characters

Instead of calling str_replace() multiple times - e.g.

$data[$c]=str_replace('/', '-', $data[$c]); $data[$c]=str_replace(',', '.', $data[$c]); 

Consider passing arrays for the search and replace arguments:

$data[$c]=str_replace(['/', ','], ['-', '.'], $data[$c]); 

You might also explore using strtr() instead of str_replace() to see if it performs better - in some cases it does, others it doesn't.².

Comparison operators

There are two comparisons that use loose equality comparisons:

 else if(mres($data[$c])==NULL){ 

and

//If this is the end of the INSERT INTO we remove the comma if($c==$number_of_data) 

There are other places where strict equality comparisons are used - i.e. === and !== and it is advisable to always use those unless you are sure you want to allow types to be coerced ³.

bonus - an external webpage

I happened to search the web for "CSV to SQL" and found this tool that could replace the script...