ApexSensei

Blog

SOQL Habits That Bite in Production (and How to Catch Them Early)

By Firus Hanov · · 8 min read

Query-in-loop, missing fields, non-selective LIKE, and string-built SOQL — habits that pass in a tiny Developer org and fail when the data is real.

SOQL Habits That Bite in Production (and How to Catch Them Early)

SOQL that works on 40 Accounts in a Developer org is allowed to lie. Production has 400,000 Accounts, custom indexes you did not create, and a user who will load a related list at 9:01 on Monday.

The query language is small. The habits around it are what fail. This is not a syntax tour. It is the short list of patterns that pass a demo and then page-timeout, heap-overflow, or return the wrong row in an org that matters.

ApexSensei's Query the org drills are free to read, and running them is part of Apex Path. Run these in a Developer org first. Production is a terrible classroom.

Habit 1: Query inside the loop

This still ships:

for (Account acc : Trigger.new) {
    List<Contact> people = [
        SELECT Id FROM Contact WHERE AccountId = :acc.Id
    ];
    acc.Number_of_Contacts__c = people.size();
}

One Account in the trigger: one query. Two hundred Accounts: 200 queries. The limit is 100 SOQL per transaction. The 101st throws System.LimitException: Too many SOQL queries: 101.

The fix is one query, keyed by Id:

Set<Id> accountIds = new Map<Id, Account>(Trigger.new).keySet();
Map<Id, Integer> counts = new Map<Id, Integer>();
for (AggregateResult row : [
    SELECT AccountId aid, COUNT(Id) c
    FROM Contact
    WHERE AccountId IN :accountIds
    GROUP BY AccountId
]) {
    counts.put((Id) row.get('aid'), (Integer) row.get('c'));
}

If you learned Apex from anonymous snippets that looped five demo rows, this habit never hurt. Prove bulk behavior is where it hurts on purpose. Bulk update once is the DML twin. Same shape: collect, then one statement.

Habit 2: SELECT * as a lifestyle

Apex has no SELECT *. People fake it by listing every field they might need "later." That later arrives as heap and CPU.

A field you did not select cannot be read:

Account acc = [SELECT Id, Name FROM Account WHERE Id = :accountId];
String phone = acc.Phone; // SObjectException: SObject row was retrieved via SOQL without querying the requested field: Account.Phone

That exception is a gift. The silent cost is selecting 80 fields including long text areas into a 200-row loop. Heap is 6 MB in a synchronous request. It is smaller than it sounds once descriptions and rich text are in the row.

Name the fields the next ten lines will use. If a later method needs Phone, select Phone there, or pass it in. Query one Account forces Id and Name only. That constraint is the lesson.

Draw the queries on paper. If the diagram has a loop around a SELECT, stop.

Habit 3: Filter on something the optimizer cannot use

A query is selective when the filter can use an index and still return a small enough slice. Leading-wildcard LIKE is the classic self-own:

List<Account> rows = [
    SELECT Id, Name FROM Account WHERE Name LIKE '%Acme%'
];

That is a table scan dressed as a search box. Fine at 200 rows. At 2 million Accounts it competes with every other tenant on the instance for CPU and will time out or return a "non-selective query" error in a trigger context.

Prefer equality, Id, lookup, RecordType, and indexed custom fields. WHERE Id = :seeded.Id is the most selective filter you will ever write. Filter Account names and Bind an Account name keep you on named, bound filters instead of scanning the org "to see what's there."

Null checks on unindexed fields, negative filters (!=, NOT IN), and OR across unrelated columns are the other usual way to disable an index. If a query only works because the sandbox is empty, it is not finished.

Habit 4: String-built SOQL

Dynamic SOQL is valid. String concatenation with user input is how you get injection and broken quotes:

String q = 'SELECT Id FROM Account WHERE Name = \'' + name + '\'';
List<Account> rows = Database.query(q);

If name is Acme' LIMIT 1) //, you are no longer writing the query you think. Use a bind even in dynamic SOQL:

String q = 'SELECT Id FROM Account WHERE Name = :name';
List<Account> rows = Database.query(q);

Binds are typed, escaped, and readable in debug logs as bind values. They are also how you avoid building 200-branch if-trees of raw SQL. Match an Account list is the IN :ids version of this habit. Keep the colon.

Habit 5: N+1 by relationship, the polite version

Parent-to-child and child-to-parent exist so you do not loop. This is one round trip:

List<Account> accounts = [
    SELECT Id, Name,
        (SELECT Id, Email FROM Contacts WHERE Email = null)
    FROM Account
    WHERE Id IN :accountIds
];

This is two hundred:

for (Account acc : accounts) {
    acc.Contacts__r; // not queried
    List<Contact> kids = [SELECT Email FROM Contact WHERE AccountId = :acc.Id];
}

Load Account Contacts and Read a Contact's Account are the two directions. Learn both. Nested subqueries have their own row caps — 200 in some parent-child shapes, more with QueryLocator — so "one query" is not a blank check to pull the whole org graph.

If you need every Contact on every Account in the company, you do not need a trigger. You need Batch Apex and a QueryLocator.

Habit 6: OFFSET as pagination

OFFSET 2000 looks like a page number. Salesforce caps OFFSET at 2,000. Page 11 of a 2,100-row list is not a legal query. Deep pages also get more expensive; the database still walks the skipped rows.

Paginate on a stable key instead:

List<Account> page = [
    SELECT Id, Name FROM Account
    WHERE Name > :lastName
    ORDER BY Name, Id
    LIMIT 200
];

Batch jobs should not paginate at all. Database.getQueryLocator streams in chunks of up to 2,000 (or 50 million rows across the job). Stream tagged Accounts is the drill. Measure governor headroom is where you print the leftover query budget instead of guessing.

Habit 7: Trusting the first row

Assignment from SOQL to a single sObject throws QueryException when there are zero rows or more than one:

Account acc = [SELECT Id FROM Account WHERE Name = 'Acme'];

In a Developer org there is one Acme. In production there are forty, or none. Use a list and check size(), or LIMIT 1 only when you have an Id and therefore uniqueness.

List<Account> matches = [
    SELECT Id, Name FROM Account WHERE Name = :name LIMIT 2
];
if (matches.size() != 1) {
    throw new QueryException('Expected one Account named ' + name);
}

The extra LIMIT 2 is cheap insurance: you can tell "none" from "many" without pulling 5,000 duplicates.

Habit 8: Counting in Apex what SOQL can count

Pulling 50,000 Ids into a list so you can call size() is how you hit heap. Use COUNT(), COUNT_DISTINCT(), SUM(), GROUP BY.

Integer n = [
    SELECT COUNT() FROM Opportunity WHERE AccountId = :accountId
];

Summarize Opportunity amounts and Keep busy Account groups are aggregate drills. They exist because a map of sums in Apex is usually a GROUP BY you did not write.

Aggregates do not return sObjects. They return AggregateResult. Alias the fields. Cast them. That friction is cheaper than a heap error at month-end.

Catch it in the Developer org

You will not accidentally create 400,000 Accounts tonight. You can still catch the habit:

  • Put 200 rows through the same code path. If queries grow linearly, stop.
  • Log Limits.getQueries() and Limits.getQueryRows() after the method. Numbers that surprise you are bugs.
  • Query only by Id you just inserted. If the test needs SeeAllData, the filter is wrong.
  • Read the debug log's SOQL_EXECUTE_BEGIN lines. Count them by hand once.

Anonymous Apex is a good place to print those limits. It is a bad place to leave the finished code. See Your First Salesforce Developer Org for the scratch-pad loop, then put the query in a class and assert it under 200 rows.

Habit 9: FOR UPDATE and then going to lunch

FOR UPDATE locks the selected rows for the rest of the transaction. Use it when two updates might collide on the same Account. Do not use it on a 50,000-row query, and do not call out to HTTP in the same transaction while you hold the lock.

Account locked = [
    SELECT Id, Status__c FROM Account
    WHERE Id = :accountId
    FOR UPDATE
];
locked.Status__c = 'Working';
update locked;

A long lock is how you get UNABLE_TO_LOCK_ROW for everyone else. Keep the transaction short. Query the row you will write, write it, commit. If you need a callout, release first — Salesforce already forbids callouts after uncommitted DML in many cases, and mixing locks with waiting makes it worse.

Habit 10: Debugging with a query that changes the answer

A common rescue in a broken org is to add LIMIT 1 or a hardcoded Id so the page loads, then forget to remove it. The bug is still there. The next record is the one that fails.

If you need a slice for diagnosis, bind a set you control and leave a test that uses 200 rows. Do not ship the slice. Debug logs and Limits.getQueryRows() tell you what happened without changing the filter.

The same applies to WITH SECURITY_ENFORCED and stripInaccessible. Those are real tools. They are not a reason to query extra fields "just in case" and strip them later. Select what the running user is allowed to see, or use a service that declares the field list.

Find missing Contact emails is a WHERE that names the defect (null email), not a dump of every Contact. Start queries from the question, not from the object.

A production-shaped drill order

Start here: /learn/query-the-org/query-one-account. Then bind, then relationships, then the locator. You can read each drill. Check and Run on Query the org is part of Apex Path. Production will not warn you that yesterday's snippet does not scale. The 101st query is the warning, and by then a user is waiting on the page.

Practice Apex free on ApexSensei