SOQL Performance Optimization: From Slow Queries to Sub-Second Results

Slow SOQL queries are one of the most common performance bottlenecks in Salesforce. Whether you're hitting governor limits, causing timeouts, or just frustrated by sluggish pages, this guide gives you the tools to diagnose and fix query performance.

Understanding How Salesforce Executes Queries

Before optimizing, understand the execution model. Salesforce runs on a relational database (historically Oracle) shared across orgs, in a multi-tenant architecture. Key concepts:

Selectivity: a query is "selective" if its filter matches a small percentage of total records. Salesforce requires selectivity to use an index.
Index types: standard fields (Id, Name, SystemModstamp, etc.) are auto-indexed. Custom fields can be indexed on request.
Full table scan: if no selective filter exists, Salesforce scans all records — slow and blocked at certain thresholds.

1. Use the Query Plan Tool

The Query Plan tool in Developer Console is your first weapon:

Open Developer Console
Go to Help → Preferences → Enable Query Plan
Run your query → click Query Plan tab

Reading the results:

Cost (Cost column)	What it means
< 1	Selective filter — the optimizer can use the index
≥ 1	Not selective — a full table scan is likely: optimize!

Official selectivity thresholds (share of records targeted by the filter):

standard index: 30% of the first million records, then 15% beyond that, capped at 1 million;
custom index: 10% of the first million records, then 5% beyond that, capped at 333,333.

Example of a bad plan:

Query: SELECT Id FROM Account WHERE Industry = 'Technology'
Plan: TableScan — Cost: 1.35 (≥ 1: not selective)

After adding a selective filter:

Query: SELECT Id FROM Account WHERE Industry = 'Technology' AND OwnerId = :currentUserId
Plan: Index on OwnerId — Cost: 0.04 (< 1: selective)

2. Always Filter on Indexed Fields

Standard indexed fields (always indexed):

Id
Name
OwnerId
RecordTypeId
CreatedDate / SystemModstamp (not LastModifiedDate, which isn't indexed)
Lookup/Master-Detail relationship fields

How to check if a field is indexed: Setup → Object Manager → [Object] → Fields & Relationships → click field → "Unique" or "External ID" = indexed.

Best practice: Always include at least one indexed field in your WHERE clause.

// ❌ Non-selective — full table scan
List<Account> accounts = [
    SELECT Id, Name FROM Account WHERE Industry = 'Technology'
];
 
// ✅ Selective — uses OwnerId index
List<Account> accounts = [
    SELECT Id, Name FROM Account
    WHERE Industry = 'Technology' AND OwnerId = :UserInfo.getUserId()
];

3. Avoid SOQL in Loops

This is a governor limit violation waiting to happen — and it's slow.

// ❌ SOQL inside a loop — 1 query per record = bad
for (Account acc : accounts) {
    List<Contact> contacts = [SELECT Id FROM Contact WHERE AccountId = :acc.Id];
    // processes contacts...
}
 
// ✅ Bulk query — 1 query for all records
Set<Id> accountIds = new Set<Id>();
for (Account acc : accounts) accountIds.add(acc.Id);
 
Map<Id, List<Contact>> contactsByAccount = new Map<Id, List<Contact>>();
for (Contact c : [SELECT Id, AccountId FROM Contact WHERE AccountId IN :accountIds]) {
    if (!contactsByAccount.containsKey(c.AccountId)) {
        contactsByAccount.put(c.AccountId, new List<Contact>());
    }
    contactsByAccount.get(c.AccountId).add(c);
}

4. Use Relationship Queries to Reduce Round-Trips

Instead of querying parent and child separately, use sub-queries:

// ❌ Two separate queries
List<Account> accounts = [SELECT Id, Name FROM Account WHERE OwnerId = :userId];
Set<Id> accountIds = new Map<Id, Account>(accounts).keySet();
List<Contact> contacts = [SELECT Id, AccountId FROM Contact WHERE AccountId IN :accountIds];
 
// ✅ One query with sub-select (child-to-parent)
List<Account> accounts = [
    SELECT Id, Name,
           (SELECT Id, FirstName, LastName, Email FROM Contacts WHERE IsActive__c = true)
    FROM Account
    WHERE OwnerId = :userId
];

Limits to know:

Up to 5 levels of parent-to-child relationships (the parent, then 4 levels of children): since API 58.0 in REST and SOAP, since API 61.0 (Summer '24) in Apex; previously a single sub-query level
Beyond 200 child records for a parent, iterate the sub-query in a SOQL for loop: assigning it directly to a list throws "Aggregate query has too many rows for direct assignment"
Max 20 parent-to-child sub-queries per query

5. LIMIT Your Results

Never retrieve more records than you need:

// ❌ Pulls everything
List<Account> all = [SELECT Id, Name FROM Account];
 
// ✅ Pulls only what you need
List<Account> recent = [
    SELECT Id, Name, CreatedDate
    FROM Account
    WHERE CreatedDate = LAST_N_DAYS:30
    ORDER BY CreatedDate DESC
    LIMIT 50
];

Use LIMIT 1 with .maybeSingle() equivalents when expecting a single result:

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

6. Use `FOR UPDATE` Carefully

// Locks records for the duration of the transaction
List<Account> accounts = [
    SELECT Id, AnnualRevenue FROM Account
    WHERE Id IN :accountIds
    FOR UPDATE
];

Use FOR UPDATE only when you need row-level locking to prevent concurrent updates. Avoid it in read-heavy flows — it blocks other transactions.

7. Date Literals vs. Dynamic Dates

Performance-wise, a date literal and a bound date variable are equivalent: both stay selective on an indexed field. Literals are more readable, though, and they honor the user's time zone and the org's fiscal year:

// ✅ Use date literals
WHERE CreatedDate = LAST_N_DAYS:30
WHERE CloseDate = THIS_QUARTER
WHERE SystemModstamp > LAST_MONTH  // indexed, unlike LastModifiedDate
 
// ✅ Also fine — useful when the bound is computed
WHERE CreatedDate > :Date.today().addDays(-30)

Common date literals:

TODAY, YESTERDAY, TOMORROW
THIS_WEEK, LAST_WEEK, NEXT_WEEK
THIS_MONTH, LAST_MONTH, THIS_QUARTER
LAST_N_DAYS:n, NEXT_N_DAYS:n
THIS_YEAR, LAST_YEAR

8. Aggregate Queries and GROUP BY

Aggregate SOQL can dramatically reduce the data transferred to Apex. Note that each aggregated row still counts toward the 50,000 query-row limit, except COUNT() without GROUP BY, which counts as a single row.

// ❌ Retrieve all opportunities to count in Apex
List<Opportunity> opps = [SELECT Id, StageName FROM Opportunity WHERE AccountId = :accId];
Integer closedCount = 0;
for (Opportunity o : opps) {
    if (o.StageName == 'Closed Won') closedCount++;
}
 
// ✅ Aggregate in SOQL
AggregateResult[] results = [
    SELECT StageName, COUNT(Id) cnt
    FROM Opportunity
    WHERE AccountId = :accId
    GROUP BY StageName
];
for (AggregateResult r : results) {
    System.debug(r.get('StageName') + ': ' + r.get('cnt'));
}

9. Avoid `!=` and `NOT IN` on Large Datasets

Negative conditions (!=, NOT IN, NOT LIKE) prevent index use and force full scans.

// ❌ Can't use index
WHERE StageName != 'Closed Won'
 
// ✅ Reframe as positive conditions
WHERE StageName IN ('Prospecting', 'Qualification', 'Proposal')

10. Request Custom Indexes for High-Volume Filters

If you frequently filter on a custom field with millions of records, request a custom index via Salesforce Support. Not needed for fields marked External ID or Unique: they are indexed automatically.

Candidates for custom indexing:

Fields filtered in batch jobs affecting > 100k records
Fields used in WHERE clauses of LWC SOSL/SOQL on high-volume objects

Performance Checklist

Before deploying a query to production:

Ran Query Plan — cost < 1 on the chosen plan
At least one indexed field in the WHERE clause
No SOQL inside loops
LIMIT applied wherever appropriate
Sub-queries used instead of multiple round-trips
Aggregate queries used to avoid pulling unnecessary records
Date literals used for date filters
Negative conditions (!=, NOT IN) replaced where possible

Conclusion

SOQL performance comes down to one principle: give the query optimizer something to work with. Always filter on indexed fields, never query inside loops, and use the Query Plan tool to validate before deploying. On high-volume orgs, these habits are the difference between a system that scales and one that falls apart under load.

Useful Resources: