Cost of SOQL Queries

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.

  1. Simple query:

2. Adding an extra field to the simple query:

3. Adding a WHERE clause to the simple query with an extra field:

4. Adding one more field to the query:

5. Retrieving set of five records in a list without a WHERE clause:

6. Retrieving child record Ids

7. Retrieving child records Ids + Name

8. Running a simple aggregate SOQL query

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.

  1. Adding extra fields, WHERE clause reflects in extra DB operation time and heap size.
  2. 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
  3. 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. 
  4. Heap size remains the same for subsequent executions of the same query with the same data volume
  5. 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!


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *