Query takes to much time to execute laravel

Question

I m uploading an excel file, which contains User data with their products status (0,1).

I want to do save the product data in Productsale table first, with user_id, product_id,target_month, and status.

then I get all users and then get product and its status from productsale table and count it and save its result in Saleproduct table.

I have 33000 rows in the excel file and saving 300k in productsale, boz everyuser have 8 products,

here is the SS of excel

here is my code

try {
          $path = $request->file('file')->store('upload', ['disk' => 'upload']);
          $value = (new FastExcel())->import($path, function ($line) {
            $user = User::where('code', $line['RVS Code'])->first();
            $store = Store::where('code', $line['Customer Code'])->first();
            $a = array_keys($line);
            $total_number = count($a);
            $n = 4;
            $productsale= 0;
            for ($i=3; $i<$total_number; $i++) {
              $str_arr = preg_split('/(ml )/', $a[$i]);
                $product = Product::where('name', $str_arr[1] ?? null)->where('type', $str_arr[0] . 'ml')->first();
                if (!empty($product)) {
                    $product = ProductSale::updateOrCreate([
                    'user_id' => $user->id,
                    'store_id' => $store->id,
                    'month' => $line['Target Month'],
                    'product_id' => $product->id,
                    'status' => $line[$str_arr[0] . 'ml ' . $str_arr[1]],
                  ]);
            }
         }
          });
          //sales
          $datas = User::all();
          foreach($datas as $user){
            $targets = Target::where('user_id',$user->id)->get();
              foreach($targets as $target){
              $sales =   Sales::where('user_id', $user->id)->where('month',$target->month)->first();
                $products = Product::all();
                foreach ($products as $product) {
                  $totalSale = ProductSale::where('user_id',$user->id)->where('month',$target->month)->where('product_id',$product->id)->sum('status');
                  $sale_product = SalesProduct::updateOrCreate([
                    'product_id' => $product->id,
                    'sales_id' => $sales->id,
                    'sale' => $totalSale,
                ]);
                }
  
              }
          }

          return response()->json(true, 200);
      }

Answer 1

you are requesting a separate query from your "poor database :(" per each product of each target of each user.

• Tries to implement the relationships between product, sales, users, target, and eager-load them while fetching users
• be careful about every single query you do in each individual loops
• gets your final query and uses EXPLAIN command in your DB to analyze what will happen in the backend for executing your code.
• active DB slow query logs to get and then optimize them.
• changes your database table engine to MyISAM.

you can do many other things for optimizing your query.

Answer 2

If it takes too long to run (and presumably results in an error being returned) then don't do it synchronously - ie. whilst the user is waiting - but to it asynchronously.

Your controller should just be concerned with validating the file, saving it into storage, and returning the success code to the user to tell them that the file has been uploaded, and will be processed shortly.

Then move all the code about the processing of the file into a job, which the controller dispatches, which runs in the background. Ideally this would be on a queue (in which case look at your queue settings, as the default is 30 seconds for the job to finish there, which lines up with the PHP settings you are probably falling foul of already, so be prepared to allow a longer time for the job to execute).