SOQL BEST PRACTICES: BOOSTING PERFORMANCE AND EFFICIENCY

Introduction

Effective utilization of Salesforce’s SOQL (Salesforce Object Query Language) is essential for developers and administrators handling substantial data volumes. Poorly optimized queries can lead to performance issues, exceeding governor limits, and even application failures.

Achieving SOQL Best Practices

Building Efficient & Selective Queries

• For optimal performance, SOQL queries must be selective. This means applying WHERE clauses in your queries and combining multiple fields using AND and OR clauses. Having more fields in your WHERE clause improves selectivity, but not all fields are equal. Indexed fields significantly enhance query performance by avoiding full table scans.
  
  
• Indexed fields include:
  • Id
  • Name
  • OwnerId
  • CreatedDate
  • SystemModStamp
  • RecordType
  • Master-Detail Fields
  • Lookup Fields
  • Unique Fields
  • External ID Fields
    
    

Using these indexed fields in your query’s WHERE clause increases the chance that your query is selective and uses an index instead of a full table scan.

Common Causes of Non-Selective SOQL Queries

Using an indexed field doesn’t always guarantee optimal performance. Avoid the following practices to ensure your queries remain selective:

• Querying for null rows: Avoid queries that look for records with empty or null fields.
  
  SELECT Id, Name FROM Account WHERE Custom_Field__c = null
  
  
• Negative filter operators: Avoid using operators like !=, NOT LIKE, or EXCLUDES.
  
  SELECT CaseNumber FROM Case WHERE Status != ‘New’
  
  
• Leading wildcards: Avoid queries with leading wildcards.
  
  SELECT Id, LastName, FirstName FROM Contact WHERE LastName LIKE ‘%smi%’
  
  
• Text fields with comparison operators: Avoid using comparison operators with text-based fields.
  
  SELECT AccountId, Amount FROM Opportunity WHERE Order_Number__c > 10
  
  

Query Optimizer

Using the Developer Console can help speed up your queries by providing insights into how the Query Optimizer works. Enable the Query Plan tool to access query operations and their costs.

1. 1. Open Developer Console from Setup.
   2. Select Help > Preferences.
   3. Set ‘Enable Query Plan’ to TRUE.
   4. Save the changes.
      
      

Avoid Querying on Formula Fields

Filtering queries using formula fields can be inefficient because formula fields lack underlying indexes and require full scans to find target records. To work around this limitation, store formula field values in a separate custom field that can be indexed.

Custom Indexes Containing Null Rows

When querying an object to find records with empty fields, consider performance implications. By default, Force.com field indexes don’t include nulls, requiring full scans. Contact Salesforce Customer Support to create custom indexes that include null rows for better performance.

Delete Records from Recycle Bin

Deleted records can affect query performance. Resolve this issue by:

• Adding IsDeleted = false to your queries.
• Emptying the recycle bin regularly.
  
  

LastModifiedDate vs SystemModStamp

The SystemModStamp is indexed, but LastModifiedDate is not. The query optimizer attempts to use the SystemModStamp index even when filtering on LastModifiedDate but cannot do so if using LastModifiedDate to determine the upper boundary of a date range.

SOQL Injection Prevention

Prevent SOQL injection by:

• Avoiding dynamic SOQL where possible, using static queries and binding variables.
• Using String.escapeSingleQuotes() for dynamic SOQL.
• Type casting.
• Replacing characters.
• Whitelisting.
  
  

SOQL vs. SOSL

• SOQL: Use when you know the objects or fields where the data resides and need to retrieve data from single or related objects, count records, sort results, or retrieve data from specific field types.
  
  
• SOSL: Use when you don’t know the objects or fields where the data resides and need to retrieve data efficiently from multiple objects and fields.
  
  

Avoid SOQL Inside FOR Loops

Avoid placing SOQL queries or DML statements inside FOR loops to prevent exceeding governor limits. Instead, retrieve data first and then process it in the loop.

Summary

By implementing these best practices, Salesforce developers and administrators can significantly enhance the performance and scalability of their applications. Selective querying, optimization tools like the Query Optimizer, and prudent use of query languages contribute to maintaining efficient data retrieval processes while adhering to Salesforce’s platform limits. Continuous adherence to these guidelines ensures a robust and sustainable Salesforce environment, supporting business operations effectively.

In conclusion, optimizing SOQL queries not only improves application performance but also ensures adherence to Salesforce best practices, fostering a stable and efficient platform for organizational success.