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 uses an Oracle database under the hood with 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:

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

Reading the results:

| Cardinality | What it means | |-------------|--------------| | Low (< 10%) | Index will be used — fast | | Medium (10–30%) | May use index — acceptable | | High (> 30%) | Full table scan — optimize! |

Example of a bad plan:

Query: SELECT Id FROM Account WHERE Industry = 'Technology'
Plan: TableScan — Cost: 0.92 (high)

After adding a selective filter:

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

2. Always Filter on Indexed Fields

Standard indexed fields (always indexed):

• Id
• Name
• OwnerId
• RecordTypeId
• CreatedDate / LastModifiedDate
• 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:

• Max 1 level of sub-query nesting
• Sub-query returns max 200 records per parent
• Max 20 sub-queries per top-level 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

Date literals are more performant than dynamic expressions because they're optimizer-friendly:

// ✅ Use date literals
WHERE CreatedDate = LAST_N_DAYS:30
WHERE CloseDate = THIS_QUARTER
WHERE LastModifiedDate > LAST_MONTH
 
// ⚠️ Dynamic dates — still works but slightly heavier
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 you retrieve:

// ❌ 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.

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
• External ID fields used for data integration upserts

Performance Checklist

Before deploying a query to production:

• [ ] Ran Query Plan — cardinality is low (< 10%)
• [ ] 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:

• SOQL and SOSL Reference
• Query Plan Tool - Developer Console
• SOQL Performance Tips - Salesforce Help