0

I would like to ask a question regarding the best management of large data. I have a custom object Product Configuration under Opportunity where it has a lookup field (parent Configuration) to itself. I want in my Apex code to process only all parent product configurations for an Opportunity at a time. So what i do is to select all records that have the parent Configuration field null. However, i recently read that it is not a best practice to use null in a query. So, what is better to follow as solution? The way i do it (query for the parent records by using the null in the SOQL query) or to select all product configurations of this opportunity and then process only the parents? I am trying to understand which is a better practice for a large batch process we are going to build.

2
  • 3
    How many records are you expecting for parent configurations over next couple of years + existing?
    – manjit5190
    Jun 4, 2021 at 17:11
  • 1
    Can you restrict the query by looking only for those referenced by Opportunity records?
    – Phil W
    Jun 4, 2021 at 17:56

1 Answer 1

1

The best way to deal with this is to make sure you have an index somehow. There's a lot of ways to potentially deal with this.

Restrict By Opportunity

As long as the query on the Opportunity is selective (e.g. you query for Opportunity__c where it is a specific Id value, and not null), then the null values in ParentConfiguration__c won't matter. The only time you have a potential problem is when none of the fields you're querying are indexed. You may already have the best possible solution for your situation. If not, read on...

Null Lookup

Every lookup field that's null actually has a hidden value in it: 000000000000000AAA. I checked the Query Planner, and this value actually uses an index, so you can still be selective, even without an Opportunity Id (potentially).

Formula Index

IF(ISBLANK(ParentConfiguration__c),'Main Product','Sub Product')

And then:

SELECT Id FROM ProductConfiguration__c WHERE ProductType__c = 'Main Product'

The main downside to this is requires intervention from Technical Support, and they can deny you the index if it doesn't fit the criteria.

Text Field

Create a text field that is populated by a workflow/process/flow/trigger with the same as above; text fields are always indexed.

Lookup Field

Create a lookup field to some other object; lookup fields are always index. Populate parent records with one lookup value, sub products with a different value. Perhaps use a trigger to manage those records as configurations change.

Metadata Records/Custom Settings

You could use a Custom Metadata or Custom Settings type if you only plan on having a few top-level configurations. This isn't scalable because of the 10MB limit, and might not be portable if you use record Ids, but if you only have a small amount of data, it's an attractive option.

Naming Convention

You could set a top level configuration to have a specific name format. The Name field of an object is always indexed, so you can reliably use this to find just the parents.

Others

The above is certainly not an exhaustive list, but is more meant to give you an idea of the possibilities. The main point is to leverage an index to avoid nulls, even if the main field you're interested in uses null to indicate a special condition.

3
  • Hi sfdcfox and thanks for your response for one more time! So, if i have a query like this: [select id, name from Product_Configuration__c where opportunityId =: oppId AND parent_Configuration__c = null]. This is considered to be optimized? Thanks in advance. Jun 7, 2021 at 8:01
  • 1
    @GiorgosGalaios Yes, that's definitely selective.
    – sfdcfox
    Jun 7, 2021 at 8:47
  • Good, thanks again!! Jun 7, 2021 at 9:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.