DEV Community

Arun Tyagi
Arun Tyagi

Posted on

The Laravel Query That Was Destroying My Client's SaaS Performance — and the 3-Line Fix That Solved It

How the Problem Showed Up

A fintech client's operations team started filing support
tickets every month-end: 'the reconciliation dashboard is
frozen.' Not slow — frozen. Requests timing out at 40 seconds,
taking the entire admin panel down with them.

The timing was the first clue. Month-end = maximum data
volume. The dashboard that crashed had a date-range filter,
and no one was limiting it.

Step 1 — Reproducing It With Real Data

Before touching any code, I reproduced the issue on a staging
environment seeded with production-volume data (anonymised).
Staging with 1,000 rows showed nothing. With 2 million rows:
exact same freeze.

# Seed staging with production-scale data
php artisan db:seed --class=TransactionSeeder --count=2000000
Enter fullscreen mode Exit fullscreen mode

Then I ran Laravel Debugbar and watched the query count:
847 queries for a 50-row paginated list.

Step 2 — Finding the Root Cause

Here is the controller code I inherited:

// BEFORE — loads everything, then filters in PHP
public function index(Request $request): View
{
    $transactions = Transaction::with([
        'customer', 'account', 'processedBy'
    ])->get(); // <-- no limit, no pagination, no scope

    $filtered = $transactions->filter(function ($t) use ($request) {
        return $t->created_at->between(
            $request->start_date,
            $request->end_date
        );
    });

    return view('transactions.index', compact('filtered'));
}
Enter fullscreen mode Exit fullscreen mode

Three problems in one method:

  1. Transaction::get() with no WHERE clause — loads the full 2M-row table into PHP memory on every page load
  2. The date filtering happens in PHP, not SQL — so the database scans every row before PHP can throw most of them away
  3. The with() eager loading runs 3 additional queries per batch — at 2M rows, that is 6M additional queries

Step 3 — The Fix 

// AFTER — filter in SQL, paginate with cursor, index the column
public function index(Request $request): View
{
    $transactions = Transaction::with([
        'customer:id,name',
        'account:id,reference',
        'processedBy:id,name'
    ])
    ->whereBetween('created_at', [
        $request->date('start_date'),
        $request->date('end_date')
    ])
    ->select(['id','customer_id','account_id',
              'processed_by','amount','status','created_at'])
    ->orderBy('created_at', 'desc')
    ->cursorPaginate(50);

    return view('transactions.index', compact('transactions'));
}
Enter fullscreen mode Exit fullscreen mode

And the migration that made the WHERE clause fast:

// Migration: add composite index on filtered columns
Schema::table('transactions', function (Blueprint $table) {
    $table->index(['created_at', 'status'], 'idx_tx_date_status');
});
Enter fullscreen mode Exit fullscreen mode

What changed:

  • whereBetween pushes the filter to SQL — database scans the index, not the full table
  • select() reduces memory footprint from ~400 bytes to ~80 bytes per row
  • cursorPaginate(50) uses a keyset cursor instead of OFFSET, which degrades on large datasets
  • Constrained eager loading (customer:id,name) eliminates SELECT * from relationship queries

The Numbers

Metric Before After
Response time 40.2 seconds 380ms
SQL queries 847 4
Peak memory 412 MB 38 MB
Server costs Unchanged Unchanged
Index migration — 12 seconds

No infrastructure change. No cache layer. No queue offloading.
Just writing SQL correctly.

The Pattern This Appears In

This is not an exotic bug. It's the single most common
performance mistake I audit in Laravel codebases:

  • get() without a WHERE clause on a growing table
  • PHP-level filtering on SQL-retrievable data
  • OFFSET-based pagination past page 50 on large tables
  • Missing indexes on columns that appear in WHERE or ORDER BY

Clockwork (the browser extension) and Laravel Debugbar surface
all four in under 15 minutes. If your staging environment
has less than 100K rows, you will not see these problems
until production volume hits.

If You Are Working With a Laravel Application at Scale

This kind of architectural audit — profiling, query analysis,
index strategy, and refactoring — is a standard part of how I
approach new engagements. If you are dealing with a slow
dashboard or planning a SaaS application that will grow
past 500K records, the decisions made in the early sprints
determine how painful month 18 will be.

You can explore the types of projects I work on at
Insert Link: Arun Tyagi — Laravel Developer Portfolio, or reach out
directly for a technical consultation.

— Arun Tyagi | aruntyagi.com | Noida, India

Top comments (0)