SOQL Patterns That Won't Kill Your Governor Limits

I review a lot of Apex. Across enterprise, ISV, and government projects, the single most common thing I flag isn't sloppy naming or missing tests. It's SOQL written by someone who didn't believe governor limits would ever bite them — until they did, in production, on a Tuesday.

Governor limits aren't a punishment. They exist because Salesforce runs your code in a multi-tenant environment, and one team's runaway query would slow down everyone else. The trick is that the limits are generous enough that bad SOQL works fine on a 10,000-record sandbox and fails the moment you hit a real org. So the antipatterns survive code review, ship to production, and break six months later when the data finally catches up.

Queries Inside Loops Are Still the Number One Mistake

Yes, every Salesforce developer learns this on day one. It's still the most common thing I find. Usually disguised.

The obvious version is rare:

for (Account a : accounts) {
    List<Contact> contacts = [SELECT Id FROM Contact WHERE AccountId = :a.Id];
    // do something
}

You'll catch this in a for loop. What you'll miss is the same thing wrapped one layer deep:

public void enrichOrders(List<Order> orders) {
    for (Order o : orders) {
        OrderHelper.calculateDiscount(o);
    }
}

public class OrderHelper {
    public static void calculateDiscount(Order o) {
        Account a = [SELECT Type, Industry FROM Account WHERE Id = :o.AccountId];
        // ...
    }
}

The for loop has no SOQL in it. The query is in a helper. Code review sees a clean loop and a clean helper, and approves both. In a 200-record bulk update, that helper fires 200 SOQL queries — over the limit, transaction killed.

The fix is the same as it's always been. Pre-fetch, then iterate. But the discipline has to extend to helpers, triggers, and any method called from inside a loop. I've started writing trigger handlers with a hard rule: handler methods receive lists, never single records. That structurally prevents this class of bug. The same logic that drives the trigger framework I've described before — make the wrong thing harder to write.

Selective Queries Are More Subtle Than the Docs Suggest

Salesforce's docs tell you a query is "selective" when the filter uses an indexed field and matches less than 10% of the records (or under a certain absolute threshold). That's the rule, and it's correct, but it's not the whole story.

What the docs don't emphasise: a selective query can become non-selective as the data grows. A query filtering by Status__c = 'Open' is selective when 5% of cases are open. When the org grows and 30% are open because of a workflow change, the same query starts throwing System.QueryException: non-selective query against large object type.

The query didn't change. The data did. And these failures are nasty because they only happen in production — sandbox refreshes don't replicate the volume.

Two practical patterns:

1. Filter by record-creation time when you can. WHERE CreatedDate = LAST_N_DAYS:30 is almost always selective because the standard CreatedDate index is healthy and recent records are a small fraction of total records.

2. Don't trust custom field indexes blindly. Salesforce won't index a custom field unless you ask, and even then, the request goes through Support. If you've designed an architecture that depends on a custom field being indexed, file the request early and confirm before go-live.

I've inherited orgs where someone assumed External_Id__c was indexed because they marked it as External ID. It wasn't. The query worked fine in the sandbox because the data was tiny. Production had 8 million records. The integration that ran every 15 minutes failed every time after launch.

Aggregate Queries Beat Loops for Counting

If you need to count or sum across records, run an aggregate query. Don't query the records and loop.

This is bad:

List<Opportunity> opps = [SELECT Amount FROM Opportunity WHERE AccountId = :accountId];
Decimal total = 0;
for (Opportunity o : opps) {
    total += o.Amount;
}

This is the same logic, one query, one row, no loop:

AggregateResult result = [
    SELECT SUM(Amount) total
    FROM Opportunity
    WHERE AccountId = :accountId
];
Decimal total = (Decimal) result.get('total');

The first approach hits the SOQL row limit if there are more than 50,000 opportunities for the account. The second one runs in the database engine and returns a single row regardless of volume.

The same pattern applies to COUNT(), MAX(), MIN(), and AVG(). If you're tempted to compute a statistic in Apex by looping, check whether SOQL can do it in the database first. It usually can.

Use SOQL FOR Loops for Large Result Sets

When you genuinely need to process millions of records — say, in a Batch Apex job — don't load them all into a list:

List<Account> accounts = [SELECT Id, Name FROM Account];   // heap blows up
for (Account a : accounts) { ... }

Use the SOQL for loop:

for (Account a : [SELECT Id, Name FROM Account]) {
    // process one record at a time
}

The platform processes records in chunks of 200 internally. Heap stays bounded. SOQL row limit is checked at the query level, not per-row, so you still need Batch Apex for queries that exceed 50,000 rows total — but for everything between 200 and 50,000, the SOQL for loop is the cleanest approach.

The catch: you can't backtrack or revisit records inside a SOQL for loop. If your processing logic needs random access, you have to load to a list. Most processing doesn't.

Selective Sub-Queries Are Faster Than Joins

Salesforce SOQL doesn't have full SQL JOINs. It has parent-child relationship queries, which are functionally similar but different in performance characteristics.

When fetching a parent and its children, the relationship query is almost always faster than two separate queries:

// Slower — two queries, hits SOQL count limit twice
Account a = [SELECT Id, Name FROM Account WHERE Id = :accId];
List<Contact> contacts = [SELECT Id, Email FROM Contact WHERE AccountId = :accId];

// Faster — one query, one count
Account a = [
    SELECT Id, Name, (SELECT Id, Email FROM Contacts)
    FROM Account
    WHERE Id = :accId
];

The platform optimises the sub-query into a single round-trip. You also get the children pre-grouped by parent, which is what you wanted anyway.

The trade-off: the maximum number of child records returned per parent is capped (200 by default). If you need more, you have to fall back to two queries — but at that point, you should question whether you really need to load that many children synchronously.

Don't Use Field-Level SOQL As a Query Builder

A pattern I see in older codebases: building SOQL strings dynamically because the developer wants to filter on a different field depending on context.

String soql = 'SELECT Id FROM Account WHERE ';
if (filterByName) {
    soql += 'Name = :nameFilter';
} else {
    soql += 'AccountNumber = :numberFilter';
}
List<Account> results = Database.query(soql);

This works. It's also fragile, hard to test, hard to read, and prone to SOQL injection if any user-supplied value sneaks in. In most cases, the cleaner approach is to write two static queries:

List<Account> results = filterByName
    ? [SELECT Id FROM Account WHERE Name = :nameFilter]
    : [SELECT Id FROM Account WHERE AccountNumber = :numberFilter];

Save dynamic SOQL for cases where the field set is genuinely unknown until runtime — generic search components, admin tools, that kind of thing. And when you do use it, bind variables go through :variable syntax, never string concatenation. That's a hard rule, and it's the same hill I'll die on as for the trigger framework: no exceptions.

When to Reach for Custom Indexes, Skinny Tables, and Big Objects

These are escape hatches, not defaults. I've used all three on a high-volume government org, and each has a narrow use case.

Custom indexes help when you're filtering on a field that isn't indexed by default and the query is non-selective without it. File a Support case requesting the index, explain the query and volume, and they'll evaluate. Don't assume — confirm in writing before relying on it.

Skinny tables help when you have a wide object with hundreds of fields, and queries only need a small subset. The platform builds a denormalised copy of the chosen fields and queries it instead. Useful for reports against high-volume objects. Also requires Support.

Big Objects are for archival data — billions of rows, queried rarely, with a different query syntax (async SOQL). Don't use them as a "fast" alternative to standard objects. The use case is data you keep for compliance but rarely touch.

The cost of all three: complexity, deployment overhead, and dependencies on Support. If a regular SOQL pattern works, use it.

What I Watch For in Code Review

A short list, in priority order:

1. Any SOQL inside a for loop, even one method deep
2. Queries without a WHERE clause on objects that grow unbounded
3. Aggregate operations done by looping instead of SUM/COUNT
4. Wide queries (SELECT *-style) returning hundreds of records when only a few fields are used
5. Dynamic SOQL with string concatenation instead of bind variables
6. Assumed indexes on custom fields that aren't actually indexed

Most production governor-limit failures I've debugged trace back to one of these six. Catching them in review is much cheaper than catching them at midnight when the integration breaks.

If you're untangling SOQL antipatterns in an inherited org and want a second pair of eyes, that's the kind of thing I do — an audit catches the patterns that haven't fired yet but will.

The shortest summary I can offer: write SOQL like the data is going to grow, because it usually does. The patterns that survive are the ones that don't depend on the data staying small.