Note: This study is based on limited data available in Developer Org & discuss the points from an high level. In complex instance & large volume of data, one might see a difference in the observations.
Before starting, let’s setup the environment.
Head up to the Developer Console in Salesforce and toggle the Debug Levels to FINEST mode.
We will be running the SOQL queries in Anonymous Window and inspect the logs on a 0.1 second time scale. While investigating these debug logs, make sure you are switching the perspective of each log to All (Predefined).
It should look like below:
Lets observe and note some common SOQL query costs on HEAP and Execution time. The test developer instance has 17 account records while running this analysis.
- Simple query:
Apex code: Account a = [SELECT Id From Account Limit 1];
Total Time consumed by Apex (milliseconds): 4.8, 4.7 [First🏃♂️➡️, Second🏃♂️➡️]
Total Time consumed by DB operation (milliseconds): 16.76, 8.68 [First🏃♂️➡️, Second🏃♂️➡️]
Total Heap Size Consumed: 1163 Bytes [Same in both runs]2. Adding an extra field to the simple query:
Apex code: Account a = [SELECT Id, Name From Account Limit 1];
Total Time consumed by Apex (milliseconds): 4.40, 4.47 [First🏃♂️➡️, Second🏃♂️➡️]
Total Time consumed by DB operation (milliseconds): 18.14, 11.32 [First🏃♂️➡️, Second🏃♂️➡️]
Total Heap Size Consumed: 1190 Bytes [Same in both runs]3. Adding a WHERE clause to the simple query with an extra field:
Apex code: Account a = [SELECT Id, Name From Account Where Name='GenePoint' Limit 1];
Total Time consumed by Apex (milliseconds): 3.11, 3.62 [First🏃♂️➡️, Second🏃♂️➡️]
Total Time consumed by DB operation (milliseconds): 22.38, 17.73 [First🏃♂️➡️, Second🏃♂️➡️]
Total Heap Size Consumed: 1215 Bytes [Same in both runs]4. Adding one more field to the query:
Apex code: Account a = [SELECT Id, Name, Industry From Account Where Name='GenePoint' Limit 1];
Total Time consumed by Apex (milliseconds): 4.70, 3.70 [First🏃♂️➡️, Second🏃♂️➡️]
Total Time consumed by DB operation (milliseconds): 31.53, 13.99 [First🏃♂️➡️, Second🏃♂️➡️]
Total Heap Size Consumed: 1254 Bytes [Same in both runs]5. Retrieving set of five records in a list without a WHERE clause:
Apex code: List<Account> accs = [SELECT Id, Name, Industry From Account Limit 5];
Total Time consumed by Apex (milliseconds): 3.63, 4.05 [First🏃♂️➡️, Second🏃♂️➡️]
Total Time consumed by DB operation (milliseconds): 16.53, 13.03 [First🏃♂️➡️, Second🏃♂️➡️]
Total Heap Size Consumed: 1539 Bytes [Same in both runs]6. Retrieving child record Ids
Apex code: List<Account> accs = [SELECT Id, Name, Industry, (SELECT Id From Contacts) From Account Limit 5];
Total Time consumed by Apex (milliseconds): 3.54, 3.39 [First🏃♂️➡️, Second🏃♂️➡️]
Total Time consumed by DB operation (milliseconds): 18.26, 16.99 [First🏃♂️➡️, Second🏃♂️➡️]
Total Heap Size Consumed: 2046 Bytes [Same in both runs]7. Retrieving child records Ids + Name
Apex code: List<Account> accs = [SELECT Id, Name, Industry, (SELECT Id, Name From Contacts) From Account Limit 5];
Total Time consumed by Apex (milliseconds): 3.04, 3.68 [First🏃♂️➡️, Second🏃♂️➡️]
Total Time consumed by DB operation (milliseconds): 19.38, 19.17 [First🏃♂️➡️, Second🏃♂️➡️]
Total Heap Size Consumed: 2185 Bytes [Same in both runs]8. Running a simple aggregate SOQL query
Apex code: List<AggregateResult> aggr = [SELECT Count(Id), Industry From Account Group By Industry];
Total Time consumed by Apex (milliseconds): 6.22, 3.28 [First🏃♂️➡️, Second🏃♂️➡️]
Total Time consumed by DB operation (milliseconds): 35.88, 12.97 [First🏃♂️➡️, Second🏃♂️➡️]
Total Heap Size Consumed: 1679 Bytes [Same in both runs]We can note here following observations which are helpful in investigation, architecting or implementing SOQL in your Org instance. You can also inspect particular part your Apex code to drill down these details. Data was not updated during any of these tests. Ideally in case of a data change between first and second run, the second run should behave same as first run as it fetches fresh data.
- Adding extra fields, WHERE clause reflects in extra DB operation time and heap size.
- When the same SOQL query is executed multiple times in different Apex transactions, the database operation time may be reduced after the first execution due DATABASE read optimizations done by Salesforce. However, this behavior can vary based on factors like data volume and query complexity
- Heap size consumption depends on the structure of all SOQL queries in an Apex transaction, the total number of records returned, and the logic and data structures used in the Apex code itself.
- Heap size remains the same for subsequent executions of the same query with the same data volume
- The DB operation time difference between a single record and five record fetch is not 5X times, also the time consumption is almost near which is interesting. If you have controlled sets of data flowing through your APEX implementation, you should not fear fetching more than 1 record per SOQL query. In fact its more suitable to query more record at once than issuing multiple queries for same object if reducing total execution time is one of your concerns. It should also balance well with your Apex logic.
From an architecture point of view, an instance which holds too complex automations, customizations, processor and data heavy operations, getting a hold on your APEX from this POV becomes very significant and unavoidable.
Hope you enjoyed this writeup, have a great day!
Leave a Reply