Question 339746 in Laravel Question posted in
The official Laravel Documentation can be found here.

PHP/Laravel groupBy is extremely slow

Our customers pay for licenses that run for some time, e.g. 6 months.
Example:

Product: product1
Payment date: 2024-01-01
Start date: 2024-01-01
End date: 2024-03-31
Total excluding tax: $29.99

We want to know how much of this amount goes towards which month.
So we calculate the number of days (in this case: 31 + 29 + 31 = 91) and divide the amount by this number to get the amount per day ($29.99 / 91 days = $0.32956 / day), and then we get:

January 2024: 31 * $0.32956 = $10.216
February 2024: 29 * $0.32956 = $9.557
March 2024: 31 * $0.32956 = $10.216

Here, the numbers are very similar for the sake of the example, but usually, we’d of course have licenses that start in the middle of the month, so the numbers can be very different.

We have thousands of such licenses for different products, so it’s a grouping both by product and by month, which would be very slow to do in the database (MariaDB).
For this reason, we want to do a single, simple SQL SELECT that fetches all licenses, and then do the grouping in PHP/Laravel code.
Yet for some reason, this is excruciatingly slow.
The code looks like this:


    public function revenueWaterfall(Request $request)
    {
        $products = $request->get('product_id') == 'all' ? Products::all() : Product::whereId($request->get('product_id'))->get();

        $bookedPeriodTo = $request->get('bookedPeriodTo');
        $bookedPeriodFrom = $request->get('bookedPeriodFrom');

        $productId = $products->count() == 1 ? $products->first()->id : null;

        $allData = Invoice::getAllForPeriod(Carbon::parse($bookedPeriodFrom), Carbon::parse($bookedPeriodTo), $productId)->groupBy('product_id')->sort();

        $splitValues = $allData->map(function ($productPayments) {
                    $total = $productPayments->sum('total_excluding_tax');
                    $months = $this->splitRevenueByMonth($productPayments);
                    return array_merge($months, ['total' => $total]);
                });
        return response()->json($splitValues);
    }

    function splitRevenueByMonth($payments)
    {
        $byMonth = [];

        foreach ($payments as $payment) {
            $startDay = Carbon::parse($payment->period_start)->startOfDay();
            $endDay = Carbon::parse($payment->period_end)->startOfDay();
            $daysInPeriod = $endDay->diffInDays($startDay);

            if ($daysInPeriod < 1) {
                // Handle invalid period
                throw new Exception("Invalid period detected");
            }

            $amountPerDay = $payment->total_excluding_tax / $daysInPeriod;

            for ($i = clone $startDay; $i->lessThan($endDay); $i->addDay()) {
                $month = $i->format('M Y');
                $byMonth[$month] = ($byMonth[$month] ?? 0) + $amountPerDay;
            }
        }

        return $byMonth;
    }

Actually, when I look at the code now (I slightly simplified it for the sake of this question), it seems like the grouping by product is missing.
What we would like to have is an output like this:

{
"product1":
    {
        "January 2024": 2255209.2525,
        "February 2024: 5252525.5336,
        "March 2024": 35363.3636
    },
"product2":
    {
        "December 2023": 309906.3532,
        "January 2024": 3059035.9092
    }
}

etc.

Tags:

2

Answers


  1. 0

    Have you been able to confirm the code bottleneck? I see a couple spots that I would investigate. I know you’re focused on the grouping, but maybe check out these areas as well.

    1. $allData = Invoice::getAllForPeriod(...): The grouping and sorting directly from the DB call should be timed to ensure it’s not slow here. A DB index could be helpful.

    2. Inside your .map statement you are using $total = $productPayments->sum('total_excluding_tax'); That is totaling all of the payments for the given product, but then the splitRevenueByMonth looks at every payment for that product again. Try calculating $total inside splitRevenueByMonth and returning that as part of your response. You’re already looping through the entire range of the $payment, so you can calculate the total as you go. That would also remove the need for array_merge which copies the array yet again.

    That should save some time. Another suggestion would be: Instead of looping through all the days in the entire payment period which could be 30, 60, 90+ etc, You could loop through only the months between $period_start and $period_end and multiply by the days. Your loop reduces from days to months. Pseudo Code:

    //Find the beginning and end months
$endMonth = getMonth($payment->period_start);
$startMonth = getMonth($payment->period_end);

/*Now for each month do some calculations. Your inner loop drops in
iterations significantly. You'll need some help methods to increase a
month and compare months. I'm writing $month < $endMonth, but that just 
signifies a pseudo calculation. You can't rely on that alone because 
December (12) will not be less than January (01), but your method should 
handle.*/

for($month = $startMonth, $month <= $endMonth, addMonth($month)) {
  $toalDaysInMonth = getDaysInMonth($month); // You can even move this outside of loop as static for the application b/c it won't change (until a leap year)
  
  // If in current month calculate difference between begin and full month to get partial days
  // Partial 1st month
  if($month == $startMonth) {
    $dayDiff = ($daysInMonth - $startDay);
    $byMonth[$month] = ($byMonth[$month] ?? 0) + ($amountPerDay * $dayDiff);
  }
  else if ($month == $endMonth) {  // Partial end month
    // For the end month, you just need how many days into the month
    $byMonth[$month] = ($byMonth[$month] ?? 0) + ($endDay * $amountPerDay);
  }
  else {  // Full month
    $byMonth[$month] = ($byMonth[$month] ?? 0) + ($toalDaysInMonth * $amountPerDay);
  }
}
    Login or Signup to reply.
  2. 0

    in your provided snippets the slow permormance could be due to the way you are processing the data and it become more slow when you have large amount of data.

    In your case you are iterating over each day of every payment to calculate monthy revenue this can also get slow processing in large data.

    Also each foreach loop, data maniplulation using Carbon this can also slow down the processing.

    you can try to fetch data once and grouped by product ID.

    To calculate revenue reduce the daily iteration.

    so you can do something like this

    function revenueWaterfall(Request $request)
{
    // Fetch products based on request
    $products = $request->get('product_id') == 'all' ? Products::all() : Product::whereId($request->get('product_id'))->get();

    $bookedPeriodTo = $request->get('bookedPeriodTo');
    $bookedPeriodFrom = $request->get('bookedPeriodFrom');

    // Fetch all data for the period, product
    $allData = Invoice::getAllForPeriod(Carbon::parse($bookedPeriodFrom), Carbon::parse($bookedPeriodTo), $products->pluck('id')->toArray());

    // Initialize the result structure
    $results = [];

    foreach ($allData as $data) {
        $productPayments = $data->groupBy('product_id');
        foreach ($productPayments as $productId => $payments) {
            $productName = $products->firstWhere('id', $productId)->name; // Change the 'name' field according to you Product Model
            $monthlyRevenue = $this->splitRevenueByMonth($payments);

            // Aggregate results by product and month
            foreach ($monthlyRevenue as $month => $revenue) {
                $results[$productName][$month] = ($results[$productName][$month] ?? 0) + $revenue;
            }
        }
    }

    return response()->json($results);
}

function splitRevenueByMonth($payments)
{
    $byMonth = [];

    foreach ($payments as $payment) {
        $startDay = Carbon::parse($payment->period_start)->startOfDay();
        $endDay = Carbon::parse($payment->period_end)->startOfDay();
        $amount = $payment->total_excluding_tax;

        // Calculate the contribution to each month
        while ($startDay->lessThan($endDay)) {
            $endOfMonth = (clone $startDay)->endOfMonth();
            $daysInMonth = $startDay->diffInDays($endOfMonth->lessThan($endDay) ? $endOfMonth : $endDay) + 1;

            $month = $startDay->format('M Y');
            $amountPerDay = $amount / $startDay->daysInMonth;
            $byMonth[$month] = ($byMonth[$month] ?? 0) + $daysInMonth * $amountPerDay;

            $startDay = $endOfMonth->addDay();
        }
    }

    return $byMonth;
}

    Edit the code according to the fields you have in your Model, Also remember that the performance is also depends on your database queries and your db structure.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search