Apex Speed Test – Filtering records using List of ID versus List of sObject in SOQL

Reading Time: 3 minutes

Apex Speed Test Experiment:
This blog is part of Apex Speed Test Experiments.
These experiments check that are we doing something wrong while writing code? What thing is good to write or what is not? Is your single line of code killing the CPU limit?
I am doing experiments whatever I found. You are most welcome here, If you have anything which can be added in these experiments
#CodeShouldNotEatCPU #CPUScientist

Our Experiment:
In this experiment, we will check different behaviors while filtering data using List<Id> and List<sObject>.

You Will Need:
A computer, a browser, a Salesforce Org and 10000 lead records.

Let’s Start:
We have taken 10000 lead records for this experiment. We are querying these records and re-querying record using different ways.

Experiment 1:
In this experiment, We are querying 10000 records in a list. This query has only one field. Now we are passing this list in another query. Basically, we are passing List<sObject> in the filter condition.

Open the developer console. Now Open Execute Anonymous Window. (This option present in Debug menu.)
Copy this code ApexSpeedExperiment_2.runExperiment1(); and run it from developer console. I called this method 5 times and here are the results.

 

Result: Average time taken is 162.2 Milliseconds.

Experiment 2:
In this experiment, We are querying 10000 records in a list. This query has only one field. We are collecting the id of records in a new list then we are passing this list in another query. Basically, we are passing List<Id> in the filter condition.

Open the developer console. Now Open Execute Anonymous Window. (This option present in Debug menu.)
Copy this code ApexSpeedExperiment_2.runExperiment2(); and run it from developer console. I called this method 5 times and here are the results.

Result: Average time taken is 146.6 Milliseconds.

Note: When tested with Set instead of the list. Average time taken is 151 Milliseconds.

Experiment 3:
In this experiment, We are querying 10000 records in a list. This query has more than one field. Now we are passing this list in another query. Basically, we are passing List<sObject> in the filter condition.

Open the developer console. Now Open Execute Anonymous Window. (This option present in Debug menu.)
Copy this code ApexSpeedExperiment_2.runExperiment3(); and run it from developer console. I called this method 5 times and here are the results.

Result: Average time taken is 171.2 Milliseconds.

Conclusion:
According to our experiments, filtering records with List<Id> is faster than List<sObject>. But still few notes that need to keep in mind:

1. If you have a query with the only Id then the passing list of sObject will be good as for loop also consume CPU limit.
2. If you have a query with the more than 5-6 fields then the passing list of Id will be good.
3. Both approaches are good in their own places.

Special Note: In one of the codes, which was written on After Insert and the code was re-querying the data from Trigger.New (Trigger.New is List of sObject). As logic needed more parent field’s data. In After Trigger, We get almost all fields data and it was consuming 5200 Milliseconds.

To fix the issue, I used a for loop to get Ids of records then used in the filter query. Now the query was taking 100-120 Milliseconds. Reduced the 5000 Milliseconds (5 Seconds). Which is very high optimization.

Selecting approach, Depend on your code structure and the flow. But my suggestion is that filtering records using List<Id> is good.

Thank You for taking part in the Apex Speed Test Experiment. If you have any other experiment in mind. Please let me know. I will conduct an experiment and will share the blog here.

Leave a Reply