Best way to get the best performance of a large CSV upload in PHP I am trying to upload a CSV file to a database which has around 800,000 rows. I am able to do this on my server with the following specs:


RAM: 4GB
Rows: 800,000
Time taken: 305 seconds
Usage: 15% RAM
Database: Postgres (Amazon RDS)


My upload code looks like this:

   public function handle()
   {
      set_time_limit (0);
      // try {

            $time_start = microtime(true);
            $counter=0;
            $data = array();
            $file = flag_tables::where('imported','=','0')
                     -&gt;orderBy('created_at', 'DESC')
                     -&gt;first();
              $file_path = Config::get('filesystems.disks.local.root') . '/exceluploads/' .$file-&gt;file_name;

              $header = null;
              $totalRows=0;
              $in = fopen($file_path,"r");
              if($in){
                  while(!feof($in)){
                        $content = fgets($in);
                    if($content)    $totalRows++;
                  }
              }

              $totalRows = --$totalRows;
              $file-&gt;total_rows = $totalRows;
              $file-&gt;save();
              fclose($in);
              $in = fopen($file_path,"r");
              $c=0;

              while ($row = fgetcsv($in)){
                  if ($header === null) { $header = $row;continue;}
                  $data[] = array('data'=&gt; json_encode(array_combine($header, $row)),'created_at'=&gt;date('Y-m-d H:i:s'),'updated_at'=&gt; date('Y-m-d H:i:s'));
                  $counter++;

                   if(count($data) == 1400)
                   {  
                      DB::table('price_results')-&gt;insert($data);
                      $file = $file-&gt;fresh(); //reload from the database
                      $file-&gt;rows_imported = (int) $file-&gt;rows_imported + $counter;
                      $counter= 0;
                      $file-&gt;save();
                      $data = array();
                    }
                  }

              $time_end = microtime(true);
              $time = $time_end - $time_start;
              DB::table('price_results')-&gt;insert($data);
              $file = $file-&gt;fresh(); //reload from the database
              $file-&gt;rows_imported = (int) $file-&gt;rows_imported + $counter;
              $file-&gt;imported =1;
              $file-&gt;total_execution_time=$time;
              $file-&gt;save();
              fclose($in);
      // }
      //   catch(\Exception $e)
      //   {
      //     dd($e-&gt;getMessage());
      //   }
   }


I would like to make this upload time better, if possible.
I would say that when you are dealing with bulk load operations, you might want to forego usage of your framework's ORM (data model) or query builder functionalities, in favor of more straightforward raw query or even command line functionality in order to leverage the DB's bulk load capabilities.

In this case, if your file and DB are on same server you might consider Postgres COPY query to bulk load the CSV.

For example:

COPY {table_name} (column1, column2, ...) FROM '$file_name' DELIMITER ',' CSV


Here you specify the table columns that match your columns in the CSV (in order given in CSV).  If you have timestamps properly defined on your table, you eliminate the need for you to "enrich" your inserted data with timestamps like you are currently doing (i.e. they could behave much like an autoincrement field upon row insert). In most cases, you should probably let the database be the authoritative source for generating timestamps anyway (unless you are trying to capture some client-specific timestamp value).

If your application and DB are on separate servers, you might consider forming a command-line statement using \copy to satisfy your requirement. This is pretty much the same syntax-wise as using COPY. This is the case that would be applicable to you since you are using RDS.

I think this would allow you to greatly simplify your code here such that you really only need to do your update check, your file system validation, then the bulk load command.

You would move away from the concept of interactively loading and refreshing data between the client and database.

You probably should also read through AWS documentation on bulk-loading into RDS for other considerations.

The bottom line here is that when you start thinking about bulk loading, you should probably understand that there are specific tools for this sort of work that go beyond the capabilities of your framework/ORM.



Other code review thoughts:


Should you have a more meaningful name for this method?  handle() seems kind of generic.
You should consider refactoring this method, as there is a lot of code complexity within this single method.  Perhaps individual (protected/private) methods for:


checking to see table(s) needing upload
file path generation and file system validation as readable
changing PHP ini settings at runtime - i.e. set_time_limit() - and resetting after execution which you fail to do here
actually executing bulk load.

You have some problems with style consistency. You should consider settling on a style and sticking to it (prefereably PSR-1/PSR-2 if you want to stick to best existing PHP-wide standards). Examples:


inconsistent indents
inconsistent spacing around and placement of flow control structures
mixing of camelCase and snake_case in your user-defined variables
inconsistent spacing around assignment operators

You are using exact and loose comparisons inconsistently.  I didn;t mention this as style problem, as this is more significant to code quality than those other items.  I would suggest getting in the habit of using strict comparisons as default and using loose comparisons only where you have specific reason to do so.  Codebases that are littered with loose comparisons are much more likely to have bugs in them due to unexpected truthy/falsey behaviors.