Optimizes Salesforce SOQL queries with selective filters, index strategies, query plans, and loop avoidance patterns. Use for governor limits, slow loads, or 100K+ record datasets.
How this skill is triggered — by the user, by Claude, or both
Slash command
/salesforce-claude-code:sf-soql-optimizationThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Poorly written SOQL is the most common cause of governor limit exceptions and slow page loads in Salesforce. This skill covers optimization procedures, query plan analysis, and index strategy.
Poorly written SOQL is the most common cause of governor limit exceptions and slow page loads in Salesforce. This skill covers optimization procedures, query plan analysis, and index strategy.
@../_reference/SOQL_PATTERNS.md @../_reference/GOVERNOR_LIMITS.md
System.LimitException: Too many SOQL queries errorsThe query optimizer decides whether to use an index or perform a full table scan based on selectivity — the percentage of records a query expects to return. See @../_reference/SOQL_PATTERNS.md for threshold tables.
// Potentially NOT selective — if more than 10% of Contacts have Status = 'Active'
List<Contact> contacts = [SELECT Id FROM Contact WHERE Status__c = 'Active'];
// Selective — CreatedDate is indexed, LAST_N_DAYS:30 likely returns < 10%
List<Contact> recentContacts = [
SELECT Id FROM Contact
WHERE CreatedDate = LAST_N_DAYS:30
];
Use the Query Plan Tool in Developer Console to verify actual query plans. These thresholds are guidelines; the optimizer considers data distribution, available indexes, and org-specific factors.
Every iteration of a loop containing a SOQL query multiplies the query count toward the 100/200 limit.
// Collect all IDs first
List<Account> accounts = [SELECT Id, OwnerId FROM Account WHERE Type = 'Customer'];
Set<Id> ownerIds = new Set<Id>();
for (Account acc : accounts) {
ownerIds.add(acc.OwnerId);
}
// Single query for all related records
Map<Id, User> ownerMap = new Map<Id, User>(
[SELECT Id, Name, Email FROM User WHERE Id IN :ownerIds]
);
// Now iterate — zero SOQL queries in this loop
for (Account acc : accounts) {
User owner = ownerMap.get(acc.OwnerId);
if (owner != null) {
sendWelcomeEmail(acc, owner);
}
}
trigger OpportunityTrigger on Opportunity (after update) {
Set<Id> oppIds = Trigger.newMap.keySet();
Map<Id, List<OpportunityLineItem>> itemsByOppId = new Map<Id, List<OpportunityLineItem>>();
for (OpportunityLineItem item : [
SELECT Id, OpportunityId, Quantity, UnitPrice
FROM OpportunityLineItem
WHERE OpportunityId IN :oppIds
]) {
if (!itemsByOppId.containsKey(item.OpportunityId)) {
itemsByOppId.put(item.OpportunityId, new List<OpportunityLineItem>());
}
itemsByOppId.get(item.OpportunityId).add(item);
}
for (Opportunity opp : Trigger.new) {
List<OpportunityLineItem> items = itemsByOppId.get(opp.Id);
if (items == null) items = new List<OpportunityLineItem>();
// Process items...
}
}
// Filter on RecordTypeId (indexed) rather than custom non-indexed fields
[SELECT Id FROM Account WHERE RecordTypeId = :RETAIL_RECORD_TYPE_ID]
// Combine indexed + non-indexed for compound selectivity
[SELECT Id FROM Account
WHERE Type = 'Customer'
AND Custom_Category__c = 'Retail']
// Select only what the calling code actually uses
List<Account> accounts = [SELECT Id, Name FROM Account WHERE Id IN :accountIds];
Account acc = [SELECT Id, Name FROM Account WHERE Name = 'Acme' LIMIT 1];
List<Account> recentAccounts = [
SELECT Id, Name, CreatedDate
FROM Account
ORDER BY CreatedDate DESC
LIMIT 50
];
// Trailing wildcard CAN use the index
[SELECT Id FROM Account WHERE Name LIKE 'Acme%']
// For full-text search — use SOSL instead
List<List<SObject>> results = [FIND 'Corp' IN NAME FIELDS RETURNING Account(Id, Name)];
// Bind variables are safe and performant
List<Account> accounts = [SELECT Id FROM Account WHERE Name = :accountName];
// For dynamic SOQL — use Database.queryWithBinds
Map<String, Object> bindVars = new Map<String, Object>{
'accountName' => accountName,
'minRevenue' => minimumRevenue
};
String safeQuery = 'SELECT Id, Name FROM Account WHERE Name = :accountName AND AnnualRevenue >= :minRevenue';
List<Account> accounts2 = Database.queryWithBinds(safeQuery, bindVars, AccessLevel.USER_MODE);
List<Account> accounts = [
SELECT Id, Name,
(SELECT Id, FirstName, LastName, Email
FROM Contacts
WHERE Email != null
ORDER BY LastName)
FROM Account
WHERE Type = 'Customer'
WITH USER_MODE
];
Limits: Max 1 level of subquery depth. Child subqueries do NOT count as separate SOQL queries. Max 20 subqueries per query.
List<Contact> contacts = [
SELECT Id, FirstName, LastName,
Account.Name,
Account.Owner.Name,
Account.Owner.Email
FROM Contact
WHERE AccountId != null
WITH USER_MODE
];
Limits: Max 5 levels of parent traversal. Max 35 relationship traversals total per query.
// Use COUNT() instead of loading records to count
Integer customerCount = [SELECT COUNT() FROM Account WHERE Type = 'Customer'];
// AggregateResult for grouped/summed data
List<AggregateResult> results = [
SELECT Type, COUNT(Id) recordCount, SUM(AnnualRevenue) totalRevenue
FROM Account
WHERE Type != null
GROUP BY Type
HAVING COUNT(Id) > 5
ORDER BY COUNT(Id) DESC
];
public List<SObject> buildDynamicQuery(
String objectName, List<String> fields,
String whereClause, Integer maxRecords
) {
Schema.DescribeSObjectResult describe =
Schema.getGlobalDescribe().get(objectName)?.getDescribe();
if (describe == null) {
throw new InvalidQueryException('Unknown object: ' + objectName);
}
Map<String, Schema.SObjectField> fieldMap = describe.fields.getMap();
List<String> validatedFields = new List<String>();
for (String field : fields) {
if (fieldMap.containsKey(field.toLowerCase())) {
validatedFields.add(field);
}
}
if (validatedFields.isEmpty()) validatedFields.add('Id');
String soql = 'SELECT ' + String.join(validatedFields, ', ') +
' FROM ' + objectName;
if (String.isNotBlank(whereClause)) {
soql += ' WHERE ' + whereClause;
}
soql += ' LIMIT ' + Math.min(maxRecords, 2000);
return Database.queryWithBinds(soql, new Map<String, Object>(), AccessLevel.USER_MODE);
}
For heavily-queried fields not indexed by default, request a custom index from Salesforce Support. Provide object name, field API name, and typical query pattern.
For objects with >10 million records, Salesforce Support can create a skinny table — a narrow copy with only the most-queried fields.
public class LargeDataProcessingBatch implements Database.Batchable<SObject> {
public Database.QueryLocator start(Database.BatchableContext bc) {
return Database.getQueryLocator([
SELECT Id, Status__c FROM Account
WHERE CreatedDate < :Date.today().addYears(-5)
AND Status__c = 'Active'
]);
}
public void execute(Database.BatchableContext bc, List<Account> scope) { /* ... */ }
public void finish(Database.BatchableContext bc) {}
}
String searchTerm = 'Acme Holdings';
List<List<SObject>> searchResults = [
FIND :searchTerm
IN ALL FIELDS
RETURNING
Account(Id, Name, Type WHERE Type = 'Customer'),
Contact(Id, FirstName, LastName, AccountId)
LIMIT 50
];
List<Task> tasks = [
SELECT Id, Subject,
TYPEOF What
WHEN Account THEN Name, Phone
WHEN Opportunity THEN Amount, StageName
END
FROM Task
WHERE OwnerId = :userId
WITH USER_MODE
];
Use when querying objects with polymorphic lookups (Task.WhoId, Task.WhatId, Event.WhoId, Event.WhatId) to avoid multiple queries or instanceof checks.
-- Semi-join: accounts that have contacts with emails
SELECT Id, Name FROM Account
WHERE Id IN (SELECT AccountId FROM Contact WHERE Email != null)
-- Anti-join: accounts with no closed opportunities
SELECT Id, Name FROM Account
WHERE Id NOT IN (SELECT AccountId FROM Opportunity WHERE IsClosed = true)
Inner subquery can only return one field. Max one level of nesting. More efficient than querying both objects and filtering in Apex.
Use the Developer Console Query Plan to understand query execution before deploying.
| Result | Meaning |
|---|---|
TableScan | Full table scan — potentially slow on large objects |
Index | Index used — fast and selective |
Cost | Estimated relative cost — lower is better |
If you see TableScan on a large object:
sf-apex-agent — For interactive, in-depth guidancesf-soql-constraints — Hard rules for SOQL safety and compliancenpx claudepluginhub jiten-singh-shahi/salesforce-claude-code --plugin salesforce-claude-codeGenerates, optimizes, and analyzes Salesforce SOQL/SOSL queries from natural language. Automatically activates when working with .soql files, relationship queries, aggregates, or query performance.
Generates production-ready SOQL queries from natural-language requirements with selectivity analysis and governor-limit guidance. Does not execute queries.
Optimizes Salesforce API performance via SOQL tuning, Composite/Collections batching, relationship queries, and caching using jsforce in JS/TS.