Using SOQL and SOSL Efficiently in Salesforce

For Salesforce developers, mastering data retrieval is paramount to creating scalable and high-performance applications. The platform offers two primary tools for querying data from its robust database: SOQL (Salesforce Object Query Language) and SOSL (Salesforce Object Search Language). Although both facilitate data access, a crucial difference lies in when to utilize each. Understanding the specific strengths of SOQL versus SOSL, and implementing best practices for their optimization, is key to achieving superior application speed and code efficiency.

What is SOQL (Salesforce Object Query Language)?

SOQL (Salesforce Object Query Language) serves as the primary mechanism for developers to interact with the Salesforce database. Conceptually similar to standard SQL, SOQL is tailored for the Salesforce environment, allowing users to precisely retrieve data from one or several specified objects based on meticulous filtering conditions. A key capability of SOQL is its ability to traverse object relationships (like parent-child links) to access related data, facilitating powerful, deep queries.

Example: List accList = [SELECT Id, Name, Industry FROM Account WHERE Industry = ‘Finance’ LIMIT 50];

This query is designed to retrieve the initial 50 Account records whose Industry field is explicitly set to ‘Finance’.

When to Use SOQL

• You have a precise target for data retrieval, knowing the exact object(s) required.
• The goal is to retrieve a defined set of fields or navigate related records (e.g., retrieving Contact details alongside their parent Account).
• You are applying specific filters or criteria (using WHERE clauses) to narrow the returned record set.
• There is a need to calculate aggregate summaries using clauses like GROUP BY or HAVING.
  

Advanced SOQL Examples

1. Relationship Query (Parent-to-Child):

List<Account> accWithContacts = [SELECT Id, Name, (SELECT Id, LastName FROM Contacts) FROM Account WHERE Industry = ‘Finance’];

2. Aggregate Query:

AggregateResult[] result = [SELECT Industry, COUNT(Id) total FROM Account GROUP BY Industry];

3. Dynamic SOQL:

String industry = ‘Technology’;

String query = ‘SELECT Id, Name FROM Account WHERE Industry = :industry’;

List<Account> techAccounts = Database.query(query);

 

Best Practices for SOQL

• • **Avoid SELECT *** — Always fetch only the fields you need.

• • Use selective filters (e.g., Id, Name, CreatedDate) to improve performance.

• • Avoid queries inside loops to prevent hitting governor limits.

• • Use relationship queries to reduce the number of individual queries.

• • Set a LIMIT clause to prevent returning unnecessary records.

• • Use indexed fields and check the Query Plan Tool for optimization.

• • Cache frequently used queries to minimize repeated database hits.

What is SOSL (Salesforce Object Search Language)?

SOSL (Salesforce Object Search Language) is specifically designed for conducting text-based searches simultaneously across a diverse range of objects and their fields. It is the perfect tool for scenarios where the data’s exact location is unknown, such as performing a keyword search that spans across Lead, Contact, and Account records at the same time.

Example:

List<List<SObject>> searchResults = [FIND ‘John*’ IN ALL FIELDS RETURNING Account(Id, Name), Contact(Id, FirstName, LastName)];

This query searches for any record containing “John” across Accounts and Contacts.

When to Use SOSL

• • The requirement is to search across several distinct objects simultaneously.
  • Data needs to be retrieved using keywords or partial string matches.
  • You are developing a system-wide or global search functionality.
  • You require rapid text-based data retrieval without prior knowledge of the object housing the data.

Best Practices for SOSL

• • Limit the number of objects and fields in the RETURNING clause.

• • Use FIND with wildcards (* and ?) cautiously to avoid slow searches.

• • Run SOSL queries outside loops to stay within governor limits.

• • Use SOSL only for text searches — not for structured data queries.

• • Return specific fields instead of using SELECT *.

• • Combine SOSL and SOQL — use SOSL for search, then SOQL for filtering results.

SOQL vs SOSL – Key Differences

Feature	SOQL	SOSL
Purpose	Retrieve data by focusing on a specific object and traversing its established relationships.	Execute a free-text search that spans various object types simultaneously.
Search Type	Structured, condition-based	Keyword or text-based
Return Type	Records that meet conditions	Records containing search terms
Use Case	When the data source is known	When the data source is unknown
Example	Fetch Accounts by Industry	Search “John” across Contacts and Leads

Performance Optimization Tips

• • Avoid Queries in Loops
    Always gather record IDs first, then run your queries outside loops.

• • Use Indexed Fields
    Use indexed fields like Id, Name, or CreatedDate for faster searches.

• • Use the Query Plan Tool
    Analyze query selectivity and optimize conditions using Salesforce’s Query Plan Tool.

• • Utilizing Batch Apex and Queueable Apex
    For large datasets, process records asynchronously to avoid hitting limits.

• • Use Data Caching and Platform Cache
    Store frequently accessed query results temporarily to reduce repeated queries.

• • Optimize Filters
    Avoid using negative filters like NOT LIKE or !=, as they can reduce performance.

• • Use Combined Queries
    For complex datasets, consider subqueries and relationship queries instead of multiple separate queries.

Common Mistakes to Avoid

• • Querying inside loops (causes governor limit errors).
  • Using unselective WHERE clauses.
  • Fetching unnecessary fields or records.
  • Ignoring query optimization tools.
  • Not testing queries with large data volumes (LDV).