"We need more RAM" is the diagnosis that hosting companies reach because it is the solution they can sell you. Nine times out of ten, when I am brought in to investigate a slow application, the bottleneck is in the queries — not the hardware. This is one of those cases.

The Starting Point

The client ran a B2B SaaS platform. Response times had been creeping upward for six months and had recently crossed the threshold where users were complaining. The hosting company had run a CPU utilization report and recommended upgrading from 16GB to 32GB of RAM. The client called me before signing the upgrade contract.

I started where I always start: the database's own diagnostic tools. In SQL Server, I queried sys.dm_exec_query_stats for the most resource-intensive queries by total worker time. In PostgreSQL, pg_stat_statements gives you the same picture. Within 20 minutes, I had a list of the five queries responsible for the majority of the load. None of them needed more RAM.

Fix 1: Replacing Nested Subqueries with CTEs

The worst offender was a reporting query that used three levels of nested subqueries. The query planner was materializing intermediate results multiple times, and the execution plan showed the inner subquery being executed once per row of the outer query.

I rewrote it using Common Table Expressions (CTEs). A CTE lets you name and define a dataset once, then reference it multiple times in the same query. The query planner can materialize the CTE result once and reuse it, rather than recomputing the subquery for every row.

This single rewrite dropped the query from 8.4 seconds to 1.1 seconds — a 87% reduction for that query alone. The logic became readable as a side effect: nested subqueries are hard to follow; named CTEs communicate intent.

Fix 2: The Hidden N+1 Problem in the ORM

The second major issue was an N+1 query pattern hiding inside Entity Framework. The code looked like this:

var orders = await context.Orders
    .Where(o => o.Status == OrderStatus.Pending)
    .ToListAsync();

foreach (var order in orders)
{
    var customer = await context.Customers
        .FindAsync(order.CustomerId); // N queries here
    // ... process
}

For 500 pending orders, this generated 501 database round-trips. The fix is to use EF Core's Include to load related data in a single join, or to restructure the query to project the necessary data upfront:

var orders = await context.Orders
    .Where(o => o.Status == OrderStatus.Pending)
    .Include(o => o.Customer)
    .ToListAsync();

One round-trip instead of 501. The response time for this endpoint dropped from 4.2 seconds to 280 milliseconds.

N+1 problems are invisible in development where datasets are small. They surface in production when the list has hundreds of rows. If you are using an ORM, auditing for this pattern should be part of every code review.

Fix 3: Missing Composite Indexes

The third issue was a pair of queries that each filtered on two columns — but the table only had single-column indexes on each. The query planner was scanning all rows matching the first condition, then filtering in memory for the second.

The fix: a composite index covering both columns in the correct order. Index column order matters — the leading column should be the one used in equality comparisons most often. For range predicates (date ranges, numeric ranges), those columns go at the end of the index.

After adding two composite indexes (one took 45 minutes to build on the production table — plan your index additions during low-traffic periods), those queries went from full table scans taking 3 seconds to index seeks taking under 50 milliseconds.

Fix 4: Covering Indexes for Read-Heavy Queries

A covering index includes all the columns needed by a query, so the database engine never has to go back to the main table to fetch additional data. For the most frequently-executed read queries — the ones running thousands of times per hour — this eliminates a significant portion of I/O.

I identified the three highest-frequency queries and added covering indexes that included their SELECT columns. The effect on those specific queries was modest (10-20% improvement), but at high execution frequency, modest improvements compound into meaningful load reduction.

The Result

After four targeted fixes — no schema changes, no application rewrites, no hardware changes — average query time across the application dropped by 71%. The hosting upgrade the client was about to purchase was unnecessary. The total cost of the engagement was less than the first month of the upgraded hosting tier.

If your application is slowing down and someone is recommending more hardware, it is worth spending a few hours looking at the query layer first. The answer is usually there.

For a broader view of how database design decisions affect performance from the start, my post on database bottlenecks in SMBs covers the diagnostic side. And if you want me to run a query audit on your system, reach out.