lifesound's avatar

Suggestions to accelerate Laravel - excel upload

This is how I do that

    public function store(Request $request)
    {
        $request->validate([
            'file' => ['required','mimes:csv']
        ]);

        $file = $request->file('file');
        $file_name =$file->getClientOriginalName();
        $filename_exist= Shipment::where('filename', $file_name)->first();

        if ($filename_exist) {
            return back()->withErrors([sprintf('file %s exists ', $file_name)]);
        }

        $sampleHeadings = [
            "quantity","type","brand","model","product","generation","status","part_number","list_id","batch",
            "serial", "mfg_serial","note","warehouse","location","cost","availability","sold_to","order_id","sale_price",
            "order_date","shipment","visibility"
        ];

        $headings = (new HeadingRowImport())->toArray($file);
        $headings =$headings[0][0];

        $missedCols =[];
        foreach ($sampleHeadings as $sampleHeading) {
            if (!in_array($sampleHeading, $headings)) {
                array_push($missedCols, $sampleHeading);
            }
        }

        if (count($missedCols) > 0) {
            return back()->withErrors(['error', sprintf('columns %s required', implode(' - ', $missedCols))]);
        }

        \DB::transaction(function () use ($file, $file_name, &$ship) {
            $ship = Shipment::create([
                'filename' => $file_name
            ]);

            $all_products = Product::all();
            $imported  = \Excel::toCollection(new ProductsImport($ship->id), $file);

            $sku_start_number = 100001;

            $imported->first()->map(function ($import) use ($ship, &$sku_start_number) {
                $import = $import->toArray();
                unset($import['id']);
                trim($import['serial']);

                $import['shipment_id'] = $ship->id;
                $import['created_at'] = now();
                $import['updated_at'] = now();

                // Generate SKU
                // if the name already exists - get the same sku
                $same_name = Product::where('product', $import['product'])->first();
                if ($same_name) {
                    $import['sku'] = $same_name->sku;
                } else {
                    $type_2letters = $import['type'] ? substr(trim($import['type']), 0, 2) : 'PR';
                    $brand_2letters = $import['brand'] ? substr(trim($import['brand']), 0, 2) : 'BR';
                    $sku_start_number += 1;
                    $sku = $type_2letters . $brand_2letters . $sku_start_number;
                    $import['sku'] = strtoupper($sku);
                }

                $duplicate = Product::where('serial', $import['serial'])->orWhere('mfg_serial', $import['mfg_serial'])->first();
                if ($duplicate) {
                    Duplicate::insert($import);
                } else {
                    Product::insert($import);
                }
            });
        });

        return redirect('/shipments/'.$ship->id)->with('success', 'success ');
    }

this is the import class

class ProductsImport implements ToModel, WithHeadingRow
{
    protected $shipment_id;

    public function __construct($shipment_id)
    {
        $this->shipment_id = $shipment_id;
    }


    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row)
    {
        // dd($row);
        return new Product([
            'quantity'=>$row['quantity'],
            'type'=>$row['type'],
            'brand'=>$row['brand'],
            'model'=>$row['model'],
            'product'=>$row['product'],
            'generation'=>$row['generation'],
            'status'=>$row['status'],
            'sku'=>$row['sku'],
            'part_number'=>$row['part_number'],
            'list_id'=>$row['list_id'],
            'batch'=>$row['batch'],
            'serial'=>$row['serial'],
            'mfg_serial'=>$row['mfg_serial'],
            'note'=>$row['note'],
            'warehouse'=>$row['warehouse'],
            'location'=>$row['location'],
            'cost'=>$row['cost'],
            'availability' =>$row['availability'],
            'sold_to' =>$row['sold_to'],
            'order_id' =>$row['order_id'],
            'sale_price'=>$row['sale_price'],
            'order_date'=>$row['order_date'],
            'shipment'=>$row['shipment'],
            'visibility'=>$row['visibility'],
            'shipment_id'=>$this->shipment_id
        ]);
    }

    public function collection(Collection $rows)
    {
        foreach ($rows as $row) {
			//
        }
    }
}

The problem is large files give ( NGINX gateway timeout ) error .. for smaller files it is slow but OK ..

ANY suggestions are highly appreciated .. every req second matters

0 likes
8 replies
Tray2's avatar

The best way is to just upload the file, register a job to process it in the background.

lifesound's avatar

@Tray2 but I want to say it is not a heavy server few requests how jobs benefits this ?

could I get any benefit of jobs or redis for my db intensive code ? while doing upload there too few requests !!

Also the regular user of the system is not used to use cli

Tray2's avatar
Tray2
Best Answer
Level 74

@lifesound There are time limits for a regular request, if I remember correctly it's 60 seconds. If a request takes longer than that it is timed out, it is also bad to make the user wait, they will likely press the back button or some other link in your app, causing the request to be canceled in an uncontrolled way.

The guideline is to give the system back to the users as fast as possible. We have two scenarios here, the first we process the file directly upon upload.

  1. The user uploads the file 5s.
  2. Your application starts processing the file 10-55s
  3. If step 2 takes longer the request will be canceled.
  4. The use will have to wait up to 60 seconds before the system is available to him.

in the second we queue the processing, and give the system back to the user much faster.

  1. The user uploads the file 5s
  2. Your application queues a job to process the file 0s
  3. The user gets the system back, and can continue to work.
  4. Your application starts to process the file, and it doesn't really matter if it takes 10s or 500s since we don't have the limit of the request.
  5. When the job is done, broadcast a notification to the user, that their data is available.

In scenario 1, you can't upload files that would cause the request to time out, this puts a limit on how big a file can be, while in scenario 2, you can basically upload an excel file that has a million records, and takes ten minutes to process.

Bottom line, never let the user wait, time consuming tasks should be done in the back end to not affect the users.

ManoMahe's avatar

@Tray2 Do I have any working examples for the second approach? Because I'm confused with the flow of some other random YouTube videos.

Please or to participate in this conversation.