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.

select Id from Account where Success_Partner__c =NULL

 

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.

select Id from contact where account.Id=NULL

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.

Select id from User where Fax = NULL

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.

@auraEnabled public static List<External_Contract_Role__c> retrieveExternalRoles(String contractId) { List<External_Contract_Role__c> cs = new List<External_Contract_Role__c>(); Set<Id> cIds = new Set<Id>(); for(External_Contract_Role__c contact : [Select Id, Name, Contact__c, Contact__r.Name, Contact__r.AccountId, Contact__r.Account.Name, Contact__r.Email, Contact__r.Phone From External_Contract_Role__c Where ActiveContract__c = :contractId Order by Contact__r.Name]){ if(!cIds.contains(contact.Contact__c)){ cs.add(contact); cIds.add(contact.Contact__c); } }

A simple fix

Adding an if condition to check whether contractId is not null before using it in the for loop/SOQL query.

@auraEnabled public static List<External_Contract_Role__c> retrieveExternalRoles(String contractId) { if(contractId != null){ List<External_Contract_Role__c> cs = new List<External_Contract_Role__c>(); Set<Id> cIds = new Set<Id>(); for(External_Contract_Role__c contact : [Select Id, Name, Contact__c, Contact__r.Name, Contact__r.AccountId, Contact__r.Account.Name, Contact__r.Email, Contact__r.Phone From External_Contract_Role__c Where ActiveContract__c = :contractId Order by Contact__r.Name]){ if(!cIds.contains(contact.Contact__c)){ cs.add(contact); cIds.add(contact.Contact__c); } } }

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.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!