Salesforce SOQL and SOSL Limits for Search Queries

In Salesforce, we use SOQL (Salesforce Object Query Language) and SOSL (Salesforce Object Search Language) to query, fetch, and search the data of the Salesforce org. To ensure better performance and avoid errors like hitting governor limits, Salesforce has enforced certain limits on SOQL and SOSL queries.

In this Salesforce tutorial, I will explain the limits of SOQL and SOSL for search queries in Salesforce and how we can follow the best practices to avoid hitting the limits.

Table of Contents

SOQL Query Limits in Salesforce

First, we will discuss the limits of the SOQL queries in Salesforce that are used to retrieve data from a single object or related object record.

1. SOQL statements:

The limits for the SOQL statements in Salesforce are as follows.

Limit for the maximum length of a SOQL statement: The maximum length of a Salesforce Object Query Language (SOQL) statement is 100,000 characters. If a SOQL statement exceeds this limit, the API will return a MALFORMED_QUERY error.
Maximum number of junction IDs: In SOQL, the maximum number of junction IDs is 500 per query. If a query includes more than 500 junction IDs, it fails and returns the MALFORED_QUERY exception.

Malformed Query Limit in Salesforce SOQL

2. SOQL WHERE Clause:

In Salesforce, when we execute the SOQL with a WHERE clause, there is a limit of 4000 characters for each string within a WHERE clause.

The default limit for the string characters in SOQL is 100000 characters.

3. SOQL query results:

Maximum Rows returned: The maximum result size is 2000, but this can be smaller if you have selected several fields or included blob fields (e.g., long text area fields, rich text area fields) in your SOQL query. In each REST API call, the maximum length for the combined URI and headers is 16,384 bytes.
The availability for SOQL query results is 2 days, including results in nested queries.

4. SOQL query Timeout:

For the maximum runtime of an SOQL query, there is a limit of 32 minutes for executing the operation and processing the results. Still, a query can time out at either the execution or processing stage. Out of the 32 minutes, a query operation has 2 minutes to execute and 30 minutes to process results before a timeout occurs.

5. Maximum Number of SOQL Queries:

In Salesforce, the limit for the maximum SOQL queries in synchronous transactions is 100, while in asynchronous transactions, the limit is 200 queries.

6. Aggregate Query Limit:

In Salesforce SOQL, the aggregate query limit is 50,000 records that can be processed in a single query, meaning that an aggregate query can return a maximum of 50,000 aggregated rows before reaching the query limit; each row returned counts towards this limit, even if it represents aggregated data from multiple records.

SOSL Query Limits in Salesforce

In Salesforce, SOSL is used to perform text searches across multiple objects. Its limits are slightly different from SOQL, which are as follows:

1. The maximum length of SOSL statements:

In Salesforce, the limit for the length of the SOSL statements is 100000, which is the same as the maximum length of SOQL queries. For SOSL statements that exceed this maximum length, the API returns a MALFORMED_SEARCH exception code, and no result rows are returned.

The maximum number of SOSL queries in a transaction is 20.

2. SOSL Search Query Strings Limit:

In Salesforce, the default maximum length of the search query string in SOSL statements is 10000 characters. If any SOSL search query exceeds the limit of 10000, then no result rows are returned.

If any SOSL search query is longer than 4000 characters, then any condition operators should be removed. For example, we executed a SOSL query with the AND operator whose characters are 4001, then replacing AND with the OR operator will make it 4000, which could return more results than expected.

3. SOSL Maximum Rows Returned:

In Salesforce, the maximum number of rows a single SOSL query can return is 2,000 records. This limit applies to API version 28.0 and later; previous versions may have a lower limit.

The LIMIT clause can be added to a SOSL query to specify the maximum number of rows returned in the text query, up to 2,000 results. If unspecified, the default is the maximum 2,000 results.

Relationship Query Limitations for SOQL and SOSL in Salesforce

While executing the relationship queries, there are several limitations that we should consider.

1. In Salesforce, the relationship queries don’t work like SQL relationship queries, where we use a Join statement to establish a relationship. In Salesforce, the objects in the relationship query should already have a relationship.

2. For the relationship query of the standard object, child-to-parent relationships are limited to 55 for a single query. For custom objects, the limit is 40 relationships, so we can reference all the child-to-parent relationships for a custom object in one query.

3. A single query of polymorphic fields can count against the child-to-parent relationship limit multiple times. For example, the below query has three relationships that exceed the limit.

SELECT 
  TYPEOF Owner
    WHEN User THEN Name, Email
    WHEN Group THEN DeveloperName, RelatedId
    ELSE CreatedDate
  END
FROM Task 
WHERE ActivityDate = THIS_WEEK

The same relationship used multiple times in a query also counts as one relationship.

4. In each specified relationship, no more than five levels can be specified in a child-to-parent relationship. For example, Contact.Account.Owner.FirstName will be considered as three levels.

We can query up to five-level child-to-parent relationships via REST, SOAP, and Apex query calls for standard and custom objects.

5. No more than 20 parent-to-child relationships can be specified in a query.

6. In API version 57.0 and earlier, only two levels of parent-to-child relationship can be specified in a query.

Combined Governor Limits for SOQL and SOSL

When we work combined with SOQL and SOSL queries in a transaction, the following governor limits apply.

1. The data returned by SOQL and SOSL queries contributes to the transaction’s heap size. The heap size limit is 6 MB for synchronous transactions and 12 MB for asynchronous transactions.

2. FOR VIEW and FOR REFERENCE: In Salesforce, the RecentlyViewed object is updated every time the logged-in user views or references a record. It is also updated when records are retrieved using the FOR VIEW and FOR REFERENCE clauses in a SOQL query.

To ensure the most recent data is available, RecentlyViewed data is periodically truncated to 200 records per object, and it is retained for 90 days, after which it is removed periodically.

3. OFFSET clause: In Salesforce, the maximum number of rows skipped by OFFSET is 2000. If we request an offset greater than 2,000 results, it will return the error NUMBER_OUTSIDE_VALID_RANGE.

Best Practices for executing SOQL and SOSL in Salesforce

1. Allow indexed searches when SOQL queries with multiple WHERE filters can’t use indexes. The search results can exceed the index limit if we use two indexed fields joined by an OR in the WHERE clause.

2. If you have to query on formula fields, use formulas. Avoid filtering with formula fields that contain dynamic, non-deterministic references.

3. Use SOQL and SOSL where appropriate, keep queries focused, and minimize the amount of data being queried or searched.

4. In SOQL, selective filters reduce the number of rows the Query Optimizer has to scan. For example, use filters that reference indexed fields and fields with a wider range of possible values.

5. For SOSL, selective filters reduce the number of irrelevant results. If the filters aren’t selective and there are search term matches for more than 2,000 records, results can be impacted by search crowding.

6. Avoid timeouts on large SOQL queries and consider using the Bulk API if you still get timeouts; consider adding a LIMIT clause.

Conclusion

In this Salesforce tutorial, we have learned about SOQL and SOSL limits, which help improve performance and avoid errors like governor limits. The best practices we discussed, such as using selective filters, indexed fields, and keeping queries focused, make the queries efficient and avoid errors.

For large data operations, breaking queries into smaller parts or using tools like Bulk API can be helpful. By applying the above tips, you will be able to execute the SOQL and SOSL queries in a more efficient way in your Salesforce org.