SOQL Performance Tips: Write Faster Salesforce Queries That Avoid Governor Limits

Writing SOQL that works is easy. Writing SOQL that works efficiently at scale — with large datasets, bulk operations, and governor limits — requires understanding how Salesforce executes queries under the hood. This guide covers the optimization techniques that actually matter in production environments.

Write Selective WHERE Clauses

Salesforce categorizes SOQL queries as either selective or non-selective. A selective query uses indexed fields and filters down to a small percentage of total records. A non-selective query scans too many records and fails with a "System.QueryException: Non-selective query against large object type" error once the object has enough data.

A query is generally considered selective if it filters on indexed fields and the result set is less than roughly 10% of the total records (or 333,000 records, whichever is smaller for large objects). The most reliable way to write selective queries:

• Filter on Id or Name — always indexed
• Filter on lookup/relationship fields — foreign keys are indexed
• Filter on custom fields marked as External ID or Unique
• Filter on RecordTypeId, OwnerId, CreatedDate, LastModifiedDate

Project Only the Fields You Need

SELECT * does not exist in SOQL — you must specify each field. This is actually a performance feature: querying fewer fields reduces data transfer and heap usage. However, many developers query far more fields than they need out of habit.

// Poor — fetches 50+ fields when only Name is needed
List<Account> accounts = [SELECT Id, Name, Phone, Website, Industry,
    AnnualRevenue, BillingCity, ShippingCity, ... FROM Account WHERE Id IN :ids];

// Better — query only what the code actually uses
List<Account> accounts = [SELECT Id, Name FROM Account WHERE Id IN :ids];

Every field you add to a SELECT clause costs memory. Large objects with rich schemas can easily push heap usage toward the 6MB (sync) or 12MB (async) governor limit when over-fetched.

Understand Indexed Fields in Salesforce

Salesforce automatically indexes a specific set of fields on every object:

• Id — primary key, always indexed
• Name — indexed on most standard objects
• OwnerId — indexed
• CreatedDate, LastModifiedDate — indexed
• RecordTypeId — indexed
• All foreign key (lookup) fields — indexed
• Custom fields marked as External ID or Unique — indexed

Standard picklist fields like StageName or Status are not automatically indexed. Filtering a large Opportunity set by StageName alone can cause non-selective query errors. Combine with an indexed field to keep the query selective:

// Risky on large orgs — StageName alone is not indexed
List<Opportunity> opps = [SELECT Id FROM Opportunity WHERE StageName = 'Closed Won'];

// Better — add a date range filter on an indexed field
List<Opportunity> opps = [SELECT Id FROM Opportunity
    WHERE StageName = 'Closed Won'
    AND CloseDate = LAST_N_YEARS:2];

Avoid SOQL in Loops — Always

This is the single most-violated SOQL rule in Apex development, and it is the root cause of the majority of LimitException: Too many SOQL queries: 101 errors in production. The governor limit is 100 SOQL queries per synchronous transaction, 200 per asynchronous.

The correct pattern is: collect all IDs you need first, run one SOQL query to get all matching records, then process using a Map for O(1) lookup.

// Pattern: bulk-safe SOQL with Map lookup
Set<Id> accountIds = new Set<Id>();
for (Opportunity opp : Trigger.new) {
    if (opp.AccountId != null) accountIds.add(opp.AccountId);
}

Map<Id, Account> accountMap = new Map<Id, Account>(
    [SELECT Id, Name, AnnualRevenue FROM Account WHERE Id IN :accountIds]
);

for (Opportunity opp : Trigger.new) {
    Account related = accountMap.get(opp.AccountId);
    if (related != null) {
        // use related account data safely
    }
}

Use Aggregate Functions Wisely

Aggregate functions (COUNT, SUM, AVG, MIN, MAX, COUNT_DISTINCT) let you compute summaries in SOQL rather than loading records into Apex and computing them there. This is almost always more efficient — the database does the heavy lifting.

// Instead of loading 10,000 records and summing in Apex:
List<Opportunity> opps = [SELECT Amount FROM Opportunity WHERE IsWon = true];
Decimal total = 0;
for (Opportunity o : opps) { total += o.Amount; }

// Do this — one aggregate row, no heap cost:
AggregateResult[] result = [SELECT SUM(Amount) totalAmt FROM Opportunity WHERE IsWon = true];
Decimal total = (Decimal) result[0].get('totalAmt');

Aggregate queries count as one SOQL query regardless of how many records they summarize. They are essential for rollup use cases and reporting logic.

Use the Query Plan Tool in Developer Console

Salesforce provides a Query Plan tool in the Developer Console that shows exactly how a query will be executed — including which index (if any) will be used, the estimated query cost, and whether it will be selective. Use it before deploying any query against a large object.

To access: Developer Console → Query Editor → check "Query Plan" checkbox → Execute. Look for a "Leading Operation Type" of "Index" rather than "Table Scan". A table scan on a large object is a non-selective query waiting to fail.

When to Use SOSL Instead of SOQL

SOSL (Salesforce Object Search Language) is a full-text search query language. Use it when:

• You need to search across multiple objects simultaneously
• You're searching text fields without knowing the exact value (partial match, full-text)
• You need to search custom fields that aren't indexed in SOQL

// SOSL searches across Contact, Lead, and Account in one query
List<List<SObject>> results = [FIND 'John Smith' IN NAME FIELDS
    RETURNING Contact(Id, Name, Email), Lead(Id, Name), Account(Id, Name)];

SOSL has a separate governor limit (20 per transaction) and has different indexing rules than SOQL. Do not use SOSL when you already know the record ID or exact field value — SOQL with an indexed filter will always be faster.

SOQL Governor Limits Quick Reference

• SOQL queries per transaction: 100 (sync), 200 (async)
• Records returned per query: 50,000
• Total records in queryMore: 10 million
• Characters in SOQL query string: 100,000
• SOSL searches per transaction: 20
• Records returned per SOSL query: 2,000