Apex Speed Test – Filtering records using List of ID versus List of sObject in SOQL
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.
First thing first, you don’t test Set that is recommended by Salesforce to query by Id, and for do that you can convert direct without a for loop for it, some like this =>
List records = [SELECT Id FROM Account];
Set accSet = (new Map(records)).keySet();
That is nice thing to know. We can definitely use this.
I would like to see the difference between doing two separate queries and 1 query with a query in the where clause.
//start timer
Lead[] leadLst = [Select Id FROM Lead];
List reQueryingLeads = [Select Id FROM Lead where Id IN :leadLst];
//end timer
VS.
//start timer
List reQueryingLeads = [Select Id FROM Lead where Id IN (Select Id FROM Lead) ];
//end timer
VS.
//start timer
Lead[] leadLst = [Select Id FROM Lead];
List lIds = new List();
for( lead l : leadLst){
lIds.add(l.id);
}
List reQueryingLeads = [Select Id FROM Lead where Id IN :lIds];
//end timer