Mastering SOQL & SOSL: Performance, Security, and Real-World Use Cases
When working with Salesforce at an enterprise scale, SOQL and SOSL are not just about fetching data—they are about performance, security, and scalability. A strong command over advanced querying techniques helps developers build optimized, secure, and governor-limit-safe solutions.
In this blog, we’ll explore advanced SOQL & SOSL concepts, why they matter, and how they are used in real-world Salesforce implementations.
Performance Optimization in SOQL & SOSL
Performance plays a critical role in Salesforce applications, especially when dealing with large data volumes.
1. Handling Governor Limits Effectively
Salesforce enforces strict limits on queries, rows, and CPU time. Writing bulk-safe queries and avoiding queries inside loops is essential to stay within these limits.
2. Complex Parent-Child Relationship Queries
Efficient handling of parent-child and child-to-parent queries reduces the number of SOQL calls and improves overall performance.
3. Best Practices for Large Data Sets
Techniques such as selective filters, indexed fields, and query optimization help ensure fast query execution even in large orgs.
4. Security Considerations
Always design queries with object-level and field-level security in mind to avoid data exposure.
Advanced SOQL Scenarios You Must Know
1. SOQL with Formula & Polymorphic Fields
List<Event> events = [
SELECT Id, Subject, What.Type, What.Name
FROM Event WHERE What.Type IN (‘Account’,’Opportunity’)
];
Why it’s important:
This tests your understanding of polymorphic relationships like WhoId and WhatId, commonly used in Activities and Tasks. Handling these correctly is crucial in real-world business logic.
2. SOQL Using Date Literals
List<Opportunity> opps = [
SELECT Id, Name, CloseDate FROM Opportunity
WHERE CloseDate = NEXT_N_DAYS:30 AND StageName != ‘Closed Won’
];
Why it’s important:
Date literals such as YESTERDAY, LAST_N_DAYS, and NEXT_N_DAYS:30 allow efficient and readable filtering without complex date calculations.
3. Security-Enforced SOQL (Spring ’23+)
List<Account> secureAccounts = [ SELECT Id, Name FROM Account WITH SECURITY_ENFORCED ];
Why it’s important:
Using WITH SECURITY_ENFORCED ensures that queries respect field-level and object-level security automatically. This is a must-know feature for senior Salesforce developers.
4. Using FOR UPDATE to Lock Records
List<Account> accountsToLock = [
SELECT Id, Name FROM Account WHERE Industry = ‘Banking’ FOR UPDATE
];
Why it’s important:
Record locking prevents race conditions when multiple users or processes attempt to update the same record simultaneously.
5. Multi-Level Relationship Queries
List<Account> accounts = [ SELECT Name,
(SELECT FirstName, LastName, (SELECT Subject FROM Cases) FROM Contacts) FROM Account LIMIT 10
Why it’s important:
Nested relationship queries test your ability to work with multiple parent-child levels, which is common in complex data models.
6. Advanced Aggregation with GROUP BY ROLLUP
AggregateResult[] results = [
SELECT Industry, Type, COUNT(Id) cnt FROM Account
GROUP BY ROLLUP(Industry, Type)
];
Why it’s important:
ROLLUP enables hierarchical aggregations, making it useful for reporting-style queries and analytics use cases.
7. Handling Large Data Using Iterative Queries
for (List<Account> accList : [SELECT Id, Name FROM Account WHERE Industry != null LIMIT 50000]) {
// Process records in chunks
}
Why it’s important:
Iterative processing helps manage millions of records while staying within Salesforce governor limits.
8. Asynchronous SOSL for Scalability
@future
public static void searchAndProcess(String searchTerm) {
List<List<SObject>> searchResults = [
FIND :searchTerm IN ALL FIELDS RETURNING Account(Id, Name)
];
// Process results asynchronously
}
Why it’s important:
Executing SOSL in asynchronous contexts (Batch, Queueable, Future) improves scalability when dealing with large search operations.
9. Chained Dynamic SOQL (Runtime Query Building)
String baseQuery = ‘SELECT Id, Name FROM Account WHERE ‘; String condition = ‘Industry = \’Technology\’ AND Rating = \’Hot\”; List<Account> accounts = Database.query(baseQuery + condition);
Why it’s important:
Dynamic SOQL allows flexible query construction based on runtime conditions, while still maintaining security and preventing SOQL injection.
10. Selective Queries for High Performance
List<Contact> selectiveQuery = [ SELECT Id, LastName FROM Contact WHERE LastName = :lastName
AND AccountId IN :accountIds LIMIT 200
];
Why it’s important:
Understanding query selectivity, indexed fields, and avoiding non-selective filters is critical for maintaining performance in large Salesforce orgs.
Why These Concepts Matter
✔ Covers basic to advanced SOQL & SOSL scenarios
✔ Focuses on security, performance, and bulkification
✔ Demonstrates knowledge of modern Salesforce features like WITH SECURITY_ENFORCED
✔ Reflects real-world, enterprise-level Salesforce use cases
Final Thoughts
Mastering these SOQL and SOSL techniques not only improves application performance but also showcases your expertise as a Salesforce Developer who can handle large-scale, secure, and optimized solutions.
If you’re preparing for senior Salesforce developer interviews or working on complex orgs, these topics are absolutely essential.