Laravel version:
8.x

PHP version:
8.0

MySQL version:
8.0

Server:
Larave Forge

I'm running a command on one of my websites

php artisan command:here

after a few minutes I get this status "Timed Out", is there anything that can be optimized here to prevent the time out?

subscriptions table has 40,000 records and incomes table has 8,000,000. Every subscription has a maximum of 200 records in the incomes table.

To explain what this command does, this is fixing (by inserting) the lacking of income of every ACTIVE subscription, which will be identified based on the hour difference from the last income that has been inserted. Every subscription has a max of 200 records in the incomes table, so if the script detected that a subscription has already reached the 200 income records, it will update the status to COMPLETED.

Subscription.php (model)

public function latestIncome()
{
    return $this->hasOne(Income::class)->latestOfMany();
}

Income.php (model)

public function subscription()
{
    return $this->belongsTo(Subscription::class);
}

namespace App\Console\Commands;

class SomeCommand extends Command
{
    protected $signature = 'command:here';

    public function handle()
    {
        ini_set('max_execution_time', 0);

        foreach (Subscription::with('latestIncome')->withCount('income')->where('status', 'ACTIVE')->lazy() as $subscription) {
            $count_earnings = $subscription->income_count;
            $recent_bonus = $subscription->latestIncome;

            if ($recent_bonus) {
                if ($count_earnings < 200) {
                    $hour_difference = now()->diffInHours($recent_bonus->created_at);

                    if ($hour_difference > 1) {
                        $to_insert = 200 - $count_earnings;
                        $max = $hour_difference;

                        if ($hour_difference > $to_insert) {
                            $max = $to_insert;
                        }

                        for ($i = 0; $i < $max; $i  ) {
                            $income = new Income;
                            $income->user_id = $subscription->user_id;
                            $income->subscription_id = $subscription->id;
                            $income->amount = (100 * 0.002) * 100;
                            $income->save();
                        }

                        if (($count_earnings   $max) >= 200) {
                            $subscription->update(['status' => 'COMPLETED']);
                        }

                        Log::info('Fix for:'.$subscription->id.' | User:'.$subscription->user_id.' | Total:'.$max);
                    }
                } else {
                    $subscription->update(['status' => 'COMPLETED']);
                }
            }
        }
    }
}

CodePudding user response：

You can define a time limit in the __construct function or in your index controller if you want a large time restriction.

    public function __construct()
    {
        set_time_limit(8000000);
    }

CodePudding user response：

You might gain some performance if you do your updates in bulk:

namespace App\Console\Commands;

class SomeCommand extends Command
{
    protected $signature = 'command:here';

    public function handle()
    {
        ini_set('max_execution_time', 0);

        // Update everything you can before the loop
        Subscription::has('income', '>=', 200)
                ->where('status', '!=' 'COMPLETED')
                ->update([ 'status' => 'COMPLETED' ]);

        foreach (Subscription::with('latestIncome')->withCount('income')->where('status', 'ACTIVE')->lazy() as $subscription) {
            $count_earnings = $subscription->income_count;
            $recent_bonus = $subscription->latestIncome;

            if ($recent_bonus) {
              $hour_difference = now()->diffInHours($recent_bonus->created_at);

              if ($hour_difference > 1) {
                   $to_insert = 200 - $count_earnings;
                   $max = $hour_difference;

                   if ($hour_difference > $to_insert) {
                       $max = $to_insert;
                   }
                        
                   // Some performance gain here possibly
                   Income::insert(collect()->pad($max, [ 
                         'user_id' => $subscription->user_id,
                         'subscription_id' => $subscription->id,
                         'amount' => (100 * 0.002) * 100,
                   ])->all());

                   Log::info('Fix for:'.$subscription->id.' | User:'.$subscription->user_id.' | Total:'.$max);
                }
            }
        }
       // Update anything that got pushed over that threshold
       Subscription::has('income', '>=', 200)
            ->where('status', '!=' 'COMPLETED')
            ->update([ 'status' => 'COMPLETED' ]);
    }
}