Do you have inconsistent query performance or Apex limit issues with your SOQL queries? Read on to learn more about how NULLs can impact your query performance and runtime.
In this blog post, we aim to increase awareness of NULL handling in code and how much impact this can have on transaction performance. We’ll also provide ways to mitigate these negative impacts.
What is NULL?
“Null is a special marker used in Structured Query Language to indicate that a data value does not exist in the database.”
A few facts about NULL
- In SQL and relational databases, NULL is nothing but the absence of data
- NULL is often a reserved keyword
- NULL cannot be equated to zero or any value
- Result of NULL equated/compared to any value is unknown
- NULL = NULL is false
NULL related pitfalls
One might ask “What difference can a filter being NULL make on my query? Isn’t it just like a ‘nothing’ filter?”
Well unfortunately, no. In RDBMS three valued logic (3VL), NULL searches means that results in a table cannot be read as indexes. Indexes are structures storing data in a particular order. As NULL is the lack of existence of data, it doesn’t fit in an index structure, with exceptions.
Salesforce and NULLs
The above NULL filter issue is also prevalent in Salesforce environments, mostly from code (SOQLs) passing NULL values or empty strings in lists declared as part of Apex. As such, it’s a part of good coding practice to include a NULL check in your SOQL queries.
Let’s take a look at some related examples.
1) Below we query on an account object having 4 million records, with a NULL search it takes 4 seconds to read the whole account record set, due to the lack of a NULL-enabled custom index.
As seen in the Salesforce optimizer notes below, due to the lack of NULL-enabled custom indexes, we ended up reading all 4 million records and fetching the result set of 2.9 million records.
2) This next example brings an even less desirable result from a NULL filter — when the main filter is on the referenced object. In this case, the user will get all the contacts which don’t have an account mapped to them. We’ll end up reading all contact records and all account records, and then filtering records satisfying the condition.
The below optimizer notes show we have to read both the account and contact objects fully to find the required result set.
Effectively, with just a change in the filter value, the whole meaning of the SOQL query changes.
3) Lastly, this next example shows how a custom index with NULL can help in some of these scenarios.
Without a custom index on Fax, we’ll read the full object users (142k records) and get 718 records back.
With a custom index on the Fax column with NULL enabled, we can get better performance, as seen below:
The most common reason for NULL filters in Apex code is not having NULL checks for passed lists.
Below is a real-life code snippet where a for loop runs on results produced from an Apex SOQL query. Here contractId at runtime can be NULL or an empty string — if not handled properly this can cause high CPU and I/O usage.
A simple fix
Adding an if condition to check whether contractId is not null before using it in the for loop/SOQL query.
Negative impacts due to NULL searches
- Longer Apex code execution times
- Apex slowness causing Concurrent Apex Request Limit Errors
- SOQLs in Apex triggers error out as they breach the 200K object size limit for nonselective queries
- High CPU resource consumption resulting in throttling or blocking of requests
Best practice recommendations
- Add NULL/empty string checks to code which should not run for NULL values
- Create a custom index with NULL values if NULL is a good filter and indexing is supported
- Handle NULL propagation after arithmetic or logical operations (because its not handled automatically)
- Increase data quality from upstream integration systems
- Enforce mandatory fields in UI selection
About the Author
Pratheesh EC is Lead Member of Technical Staff , Evangelist on Core Database Performance team at Salesforce.
His areas of interest involve working on resolving complex customer problems with database challenges.
His team’s mission is to advocate best database practices while using Salesforce products.