Slow Pages, Large Requests & Eager Loading (N+1)

1. Measure first

Most slow pages are slow for one of a few reasons: too many queries (N+1), a query without an index, too much data loaded into memory, slow external calls in the request, or heavy work that should be queued. Guessing wastes time; measure.

Tools: Laravel Debugbar or Telescope (query count and time per request), DB::listen or the slow query log in MySQL, EXPLAIN on suspicious queries, Spring Boot Actuator metrics and Hibernate statistics, browser DevTools Network tab (time to first byte vs download vs rendering), and APM tools such as New Relic or Datadog in production.

Quick checks

// Fail loudly in development when a relation is lazy-loaded (Laravel 9+)
Model::preventLazyLoading(! app()->isProduction());

// Log slow queries
DB::listen(function ($query) {
    if ($query->time > 200) Log::warning('slow query', ['sql' => $query->sql, 'ms' => $query->time]);
});

MySQL

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;

EXPLAIN SELECT * FROM transactions WHERE account_id = 42 ORDER BY created_at DESC LIMIT 20;
-- type=ALL and rows=4000000 means a full table scan: add an index
CREATE INDEX idx_tx_account_created ON transactions (account_id, created_at);

2. The N+1 problem and eager loading

Lazy loading fetches a relation the first time you access it. In a loop this produces one query for the list plus one query per row: N+1. Fifty orders with their customer and items becomes 101 queries; each is fast, but the round trips add up to seconds.

Eager loading fetches the relation for all parent rows in one extra query using WHERE id IN (...). Fifty orders with customers and items becomes 3 queries regardless of the number of rows.

Order history page

// N+1: 1 + 50 (customer) + 50 (items) = 101 queries
$orders = Order::latest()->take(50)->get();
foreach ($orders as $order) {
    echo $order->customer->name . ' ' . $order->items->count();
}

// Eager loading: 3 queries
$orders = Order::with(['customer:id,name', 'items'])->latest()->take(50)->get();

// Only need a count? Do not load the rows at all
$orders = Order::with('customer:id,name')->withCount('items')->withSum('items as total_qty', 'quantity')->latest()->paginate(50);

// Nested and constrained eager loading
$accounts = Account::with([
    'transactions' => fn ($q) => $q->where('created_at', '>=', now()->subDays(30))->latest()->limit(5),
    'transactions.merchant:id,name',
])->get();

// Already have the models? Load afterwards
$orders->load('payments');
$order->loadMissing('items.product');

Generated SQL with eager loading

SELECT * FROM orders ORDER BY created_at DESC LIMIT 50;
SELECT id, name FROM customers WHERE id IN (3, 9, 14, ...);
SELECT * FROM order_items WHERE order_id IN (187, 186, 185, ...);

Spring Boot / JPA: the same problem and fixes

// Lazy @OneToMany + loop = N+1. Fix with a fetch join or an entity graph:
@Query("select o from Order o join fetch o.customer where o.createdAt > :since")
List<Order> recentWithCustomer(@Param("since") Instant since);

@EntityGraph(attributePaths = {"customer", "items"})
Page<Order> findByStatus(String status, Pageable pageable);

// Or batch lazy loads globally: spring.jpa.properties.hibernate.default_batch_fetch_size=100
// Never return entities directly as JSON: serialisation triggers lazy loads; map to DTOs.

Over-eager loading is the opposite mistake: with("everything") on a list page that shows two columns loads megabytes of unused data. Load what the screen uses, and select only needed columns.

3. Large result sets: pagination, chunking, streaming

Loading 2 million transactions with ->get() builds 2 million PHP objects and exhausts memory. Paginate for screens; chunk or stream for processing and exports.

Techniques for big tables

// Screens: paginate (cursor pagination stays fast on deep pages)
$tx = $account->transactions()->latest('id')->cursorPaginate(25);

// Processing: chunkById walks the primary key, safe even while updating rows
Transaction::where('status', 'pending')->chunkById(1000, function ($rows) {
    foreach ($rows as $tx) $tx->settle();
});

// Low memory iteration: lazy() / lazyById() / cursor()
foreach (Customer::where('segment', 'gold')->lazyById(2000) as $customer) {
    SendFestiveOffer::dispatch($customer->id);
}

// Bulk writes: one query instead of thousands
DB::table('price_updates')->upsert($rows, ['sku'], ['price', 'updated_at']);
Product::whereIn('id', $ids)->update(['is_featured' => true]);

// Exports: stream a CSV instead of building it in memory
return response()->streamDownload(function () use ($account) {
    $out = fopen('php://output', 'w');
    fputcsv($out, ['Date', 'Description', 'Debit', 'Credit', 'Balance']);
    $account->transactions()->orderBy('id')->lazyById(2000)->each(fn ($t) => fputcsv($out, [$t->created_at, $t->narration, $t->debit, $t->credit, $t->balance]));
    fclose($out);
}, "statement-{$account->number}.csv");

For very large exports (a year of transactions for a corporate account) do not stream inside the request at all: dispatch a job that writes the file to storage, then email a signed download link. The same applies to large imports: accept the upload, store it, and process it in a queued batch of chunks.

4. Large requests: uploads, payloads and timeouts

For files larger than a few MB, upload directly from the browser to object storage (S3 pre-signed URL) so the bytes never pass through PHP, then send only the file key to the API. For bulk API payloads (a merchant posting 10,000 refunds), accept the request, validate the shape, store it, return 202 Accepted with a job id, and process it asynchronously; the client polls or receives a webhook.

Protect the server from oversized JSON: limit array sizes in validation (items => max:50), limit request body size at the web server, and rate-limit endpoints.

5. Caching and other quick wins

Cache expensive, rarely changing data

// Category tree on every product page: cache for an hour, clear when an admin edits categories
$tree = Cache::remember('categories:tree:v1', now()->addHour(), fn () => Category::with('children')->whereNull('parent_id')->get());

// Per-user dashboard totals: short TTL
$summary = Cache::remember("dashboard:{$user->id}", 60, fn () => $reports->summaryFor($user));

// Invalidate on change
Category::saved(fn () => Cache::forget('categories:tree:v1'));

React: do not render 10,000 rows

// Paginate or virtualise long lists, debounce search, and lazy-load heavy screens
const Reports = React.lazy(() => import('./Reports'));          // separate JS chunk
const debounced = useMemo(() => debounce(q => setQuery(q), 300), []);
<FixedSizeList height={600} itemCount={rows.length} itemSize={48}>{Row}</FixedSizeList>   // react-window

6. Interview questions