SOQL Best Practices for Salesforce Admins

Why the same query is instant in one org and times out in another

SOQL performance is not about the query's length. It is about selectivity: whether the filter can use an index to skip most of the table, or whether Salesforce has to scan every row to find out.

On a 5,000-record object nothing you write matters. On a 5,000,000-record object, one non-indexed WHERE clause is the difference between 200 milliseconds and a timeout. These are the rules worth internalising before you hit that boundary.

1. Filter on something indexed

Indexed by default: Id, Name, audit fields (CreatedDate, LastModifiedDate, SystemModstamp), RecordTypeId, lookup and master-detail fields, and any field marked External ID or Unique.

A filter on an unindexed custom text field on a large object is a full scan. If you need it regularly, ask Salesforce Support to add a custom index — that is a normal, supported request.

2. Selectivity beats cleverness

A filter is selective when it matches a small fraction of the table. Two clauses joined by AND are as fast as the most selective one; two joined by OR are as slow as the least selective one.

-- Selective: an indexed date filter first
SELECT Id, Name, StageName, Amount
FROM Opportunity
WHERE CreatedDate = LAST_N_DAYS:30 AND StageName = 'Closed Won'

3. Never SELECT what you will not read

There is no SELECT * in SOQL, and that is a feature. Every field you add is data serialised, transferred, and held in memory. Select the four fields you are going to look at.

4. Use date literals, not hard-coded dates

SELECT Id, Name, CreatedDate
FROM Lead
WHERE CreatedDate = LAST_N_DAYS:30

LAST_N_DAYS:30, THIS_MONTH, THIS_QUARTER and friends resolve at run time in the running user's time zone. A saved query keeps working next month; a hard-coded range silently goes stale. Date literals are never quoted — quoting them is the most common malformed-query error there is.

5. Count before you touch

Before any export or mass update, run the count. It costs nothing and it tells you the blast radius.

SELECT COUNT()
FROM Account
WHERE Industry = NULL AND CreatedDate = LAST_N_DAYS:90

COUNT() returns a scalar and no rows. COUNT(Id) is the aggregate form you need alongside GROUP BY. Mixing them up produces an error that reads like a syntax problem but is a semantics problem.

6. Aggregate on the server, not in a spreadsheet

Exporting 50,000 rows to pivot them in Excel is a habit worth breaking. GROUP BY does it in one call.

SELECT StageName, COUNT(Id) total, SUM(Amount) pipeline
FROM Opportunity
WHERE CloseDate = THIS_QUARTER
GROUP BY StageName
ORDER BY SUM(Amount) DESC

Alias your aggregates. Without total and pipeline, the columns come back as expr0 and expr1.

7. Traverse relationships instead of joining

SOQL has no JOIN. Upward you dot-walk, up to five levels; downward you use a subquery with the child relationship name, not the child object's API name.

SELECT Id, Name,
  (SELECT Id, Name FROM Contacts)
FROM Account
LIMIT 50

For "parents that have a matching child", use a semi-join:

SELECT Id, Name
FROM Account
WHERE Id IN (
  SELECT AccountId FROM Contact WHERE CreatedDate = LAST_N_DAYS:30
)

8. = NULL, never IS NULL

SOQL uses = NULL and != NULL. IS NULL is SQL and fails. For text fields imported from a spreadsheet, check both null and empty string — they are not the same thing.

9. ORDER BY before LIMIT, always together

LIMIT 10 without ORDER BY gives you an arbitrary ten rows, not the newest ten. And sorting on a high-volume unindexed field is the classic cause of a query timeout: the sort forces the scan you were trying to avoid.

OFFSET caps at 2,000, so it is a paging tool for browsing, not an export strategy. Past that, page on the sort field itself or move to the Bulk API.

10. Test destructive queries in a sandbox

The WHERE clause that selects the wrong 40,000 records looks exactly like the one that selects the right 400. Run it in a sandbox, check the count, export the current values, then run it in production.

The shape of a good admin query

SELECT Id, Name, Industry, Owner.Name   -- only what you will read
FROM Account                            -- API name, not the label
WHERE CreatedDate = LAST_N_DAYS:30      -- indexed, selective, relative
  AND Industry = NULL                   -- the actual question
ORDER BY CreatedDate DESC               -- deterministic
LIMIT 200                               -- bounded

Selective, bounded, deterministic, and readable by the person who inherits it.

The SOQL query library has the copy-paste version of these patterns per object, and what SOQL is covers the fundamentals if you are handing this to someone newer.