Efficient Reporting: Optimizing Query Caching and Big Data Report Generation with Queues

Efficient Reporting in Laravel: My Go-To Way Using Query Caching + Queues for Big Data

Okay, let’s talk about something no one enjoys but everyone needs: report generation.

You build this neat Laravel app, everything works fine — then one day your client goes:

“Hey, can you add a report with ALL the transactions from this year?”

Boom.
Suddenly you’re staring at a table with half a million rows and thinking:
“Yeah… maybe I shouldn’t have just ->get()-ed this.”

I’ve been there. More than once. So here’s how I now handle efficient reporting in Laravel when things get big — and I mean, really big.

Why Real-Time Reporting Sucks (Most of the Time)

Let’s say you have a button on the dashboard that fetches 6 months of transactions and turns them into a nice table or CSV. Sounds innocent, right?

Well… not when:

Your transactions table has 3 million rows
Users are exporting during business hours
You’re using shared hosting (👀)

Suddenly your CPU spikes, users are getting 502s, and you’re refreshing Forge every 10 seconds.
Lesson learned: don’t do heavy stuff on the main thread.

Step 1: Cache the Query Results (But Do It Smartly)

Here’s what I used to do:

return DB::table('transactions')->get();

Worked… until it didn’t.

Now I cache big queries using Cache::remember() — but with a twist:
I set the cache to expire at the end of the day, not just “60 minutes later”.

$expiresAt = now()->endOfDay();
$data = Cache::remember('sales_report', $expiresAt, function () {
    return DB::table('transactions')
        ->where('created_at', '>=', now()->subDays(30))
        ->get();
});

Why This Works

Everyone sees the same data during the day
No sudden query spam if 10 users hit “Download Report”
Still updates daily — which is usually “good enough” for most business use-cases

Would this work for stock trading apps? Nope.
But for admin dashboards, internal reports, or client exports? 100%.

Step 2: Offload the Actual Report Generation to Queues

Alright, so caching helped us read smarter.
But what if the report still needs to crunch 1GB of data and spit out a CSV?

Here’s the play: job queues.

Instead of making users wait 45 seconds and praying nothing times out, just say:

“Your report is being generated — we’ll notify you when it’s ready.”

Boom. Done.

My typical flow:

public function requestReport()
{
    GenerateBigReport::dispatch(auth()->id());

    return response()->json([
        'message' => 'We’re working on your report. Check back soon!'
    ]);
}

Then in the job itself:

public function handle()
{
    $user = User::find($this->userId);
    $rows = DB::table('transactions')->get();

    $path = storage_path("app/reports/report_{$user->id}.csv");
    $csv = fopen($path, 'w');

    fputcsv($csv, ['ID', 'Amount', 'Date']);

    foreach ($rows as $row) {
        fputcsv($csv, [$row->id, $row->amount, $row->created_at]);
    }

    fclose($csv);

    // notify user (email, in-app, etc)
}

This runs in the background. No more timeout errors, no server crashes.
It just… works.

Step 3: Let Them Download When It’s Ready

You don’t even need a fancy UI. Just give them a “Download” button that checks if the file exists:

public function downloadReport()
{
    $path = "reports/report_" . auth()->id() . ".csv";

    if (!Storage::exists($path)) {
        return response()->json(['ready' => false]);
    }

    return Storage::download($path);
}

Now you’ve got:

✅ Async generation
✅ Cached queries
✅ No wasted server cycles
✅ Happy client who thinks your app is “super fast”

But Wait, There’s More (aka Stuff I Learned the Hard Way)

Queue workers must be running, obviously. I forgot this once. The job sat in the queue for 3 hours and I thought the system was broken 😅
Set up retry attempts and timeouts — big reports might take time
Clean up old reports! I use a nightly artisan command that deletes anything older than 3 days from /storage/reports

Final Thoughts

If I had to summarize this in one sentence:

Never trust a big report to run on a click — always queue it, and cache your reads.

That little mantra has saved me from soooo many panicked messages, CPU spikes, and support emails.

The combo of query caching + queued report jobs is honestly underrated in Laravel circles. Everyone talks about live dashboards and Livewire, but for good ol’ boring business reports?
This is the way.

Pro Tip: If your users need real-time exports, consider generating lightweight summaries daily and letting them filter from that. You’ll get 80% of the value at 10% of the cost.