SOQL Optimization – Avoiding Limits & Selective Queries

SOQL is limited to 100 queries per transaction. Writing optimized queries prevents hitting this limit and improves performance. This guide covers query optimization techniques.

1. The N+1 Problem: SOQL in Loops

❌ Bad - Query in Loop (N+1 Problem)


// This is a common antipattern
List<Account> accounts = [SELECT Id FROM Account LIMIT 100];

for (Account acc : accounts) {
    // Each iteration = 1 query! Total: 101 queries!
    List<Opportunity> opps = [SELECT Id FROM Opportunity WHERE AccountId = :acc.Id];
    System.debug('Account ' + acc.Id + ' has ' + opps.size() + ' opportunities');
}

// Result: Hit 100 query limit!

✅ Good - Single Query with Aggregation


// Query once, process all data
Map<Id, List<Opportunity>> oppsByAccount = new Map<Id, List<Opportunity>>();

for (Opportunity opp : [SELECT Id, AccountId FROM Opportunity LIMIT 10000]) {
    if (!oppsByAccount.containsKey(opp.AccountId)) {
        oppsByAccount.put(opp.AccountId, new List<Opportunity>());
    }
    oppsByAccount.get(opp.AccountId).add(opp);
}

List<Account> accounts = [SELECT Id FROM Account LIMIT 100];

for (Account acc : accounts) {
    List<Opportunity> opps = oppsByAccount.get(acc.Id);
    // No queries in loop!
}

2. Using Relationships (Child to Parent Queries)

✅ Query Related Objects in Single Query


// Get opportunities WITH account data - one query!
List<Opportunity> opps = [
    SELECT Id, Name, Amount, Account.Name, Account.Industry
    FROM Opportunity
    LIMIT 1000
];

for (Opportunity opp : opps) {
    System.debug('Opportunity: ' + opp.Name);
    System.debug('Account: ' + opp.Account.Name);
    // No additional queries needed
}

Parent-to-Child Relationships


// Get accounts WITH all opportunities
List<Account> accounts = [
    SELECT Id, Name, (SELECT Id, Name, Amount FROM Opportunities)
    FROM Account
    LIMIT 100
];

for (Account acc : accounts) {
    System.debug('Account: ' + acc.Name);
    for (Opportunity opp : acc.Opportunities) {
        System.debug('  - Opportunity: ' + opp.Name);
    }
}

3. Selective Filtering (WHERE Clauses)

❌ Bad - Query All, Filter in Code


List<Account> allAccounts = [SELECT Id, Name, Revenue FROM Account];

List<Account> hotAccounts = new List<Account>();
for (Account acc : allAccounts) {
    if (acc.Revenue > 1000000) {
        hotAccounts.add(acc);
    }
}

// Inefficient - loads unnecessary data

✅ Good - Filter in WHERE Clause


List<Account> hotAccounts = [
    SELECT Id, Name, Revenue
    FROM Account
    WHERE Revenue > 1000000
];

// Much better - database filters for you

4. Indexing and Query Performance

Salesforce is fast when you query on indexed fields. Standard indexes include:

✅ Good - Query on indexed fields


// Fast - searching by  Id
Account acc = [SELECT Id, Name FROM Account WHERE Id = :accountId];

// Fast - searching by Name
List<Account> accounts = [SELECT Id FROM Account WHERE Name LIKE 'Acme%'];

// Fast - searching by CreatedDate
List<Account> newAccounts = [
    SELECT Id FROM Account
    WHERE CreatedDate = LAST_N_DAYS:30
];

// Fast - searching by external ID
Contact con = [SELECT Id FROM Contact WHERE External_ID__c = :externalId];

5. Selective Field Selection

❌ Bad - Select All Fields


List<Account> accounts = [SELECT * FROM Account];

// * means all fields - slow for large objects!

✅ Good - Select Only Needed Fields


List<Account> accounts = [
    SELECT Id, Name, Phone, BillingCity
    FROM Account
    LIMIT 1000
];

// Only request what you need

6. Batch Processing Large Queries

If you need to process millions of records, use Batch Apex: 


global class ProcessAllAccountsBatch implements Database.Batchable<sObject> {
    
    global Database.QueryLocator start(Database.BatchableContext bc) {
        // Query all records
        return Database.getQueryLocator([
            SELECT Id, Name, Rating FROM Account
        ]);
    }
    
    global void execute(Database.BatchableContext bc, List<SObject> scope) {
        // Process in batches of 200 (default)
        List<Account> accounts = (List<Account>) scope;
        
        for (Account acc : accounts) {
            // Process each account
            acc.Rating = 'Hot';
        }
        update accounts;
    }
    
    global void finish(Database.BatchableContext bc) { }
}

// Execute
Database.executeBatch(new ProcessAllAccountsBatch(), 200);

7. Avoiding Common SOQL Mistakes

❌ Bad - Using OFFSET


// Slow - scans all records before offset
List<Account> accounts = [
    SELECT Id FROM Account
    LIMIT 1000
    OFFSET 50000  // Scans 50k records just to skip them!
];

✅ Good - Use pagination with Id


// Efficient - uses index
List<Account> accounts = [
    SELECT Id FROM Account
    WHERE Id > :lastId  // Remember last Id from previous page
    ORDER BY Id
    LIMIT 1000
];

8. Query Optimization Checklist

Optimization Impact
❌ SOQL in loops Hits 100 query limit
✅ Single query + map Efficient, stays under limit
✅ Selective WHERE Faster, less data
✅ Specific fields Smaller payload
✅ Relationship queries Combines data in one query

Key Takeaways