What Can NULL Do to Me?

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.