ApexSensei

Drills › Ship it safely

Measure query cost

Ship it safely · Beginner · 10 min · Runs in your Salesforce Developer org · Apex Path

Scenario. A release review wants proof of what a lookup costs in SOQL queries, and how much a bulk query saves.

Measure how many SOQL queries a loop costs, then compare it with one bulk query.

What you will learn

Governor limits cap what one transaction can use, such as 100 SOQL queries. Limits.getQueries() tells you how many are used so far. Read it before and after some work; the difference is the cost.

Integer before = Limits.getQueries();
List<User> rows = [SELECT Id FROM User LIMIT 1];
Integer cost = Limits.getQueries() - before;

Gotcha. A query inside a loop costs one query per pass. 3 passes cost 3, and the 101st query in the transaction throws an uncatchable LimitException.

The task

Measure how many SOQL queries the loop costs, then load the same Profiles with one query and measure that too.

Examples:

  • the loop runs one query per Profile → Loop cost: 3
  • one query with WHERE Id IN :profileIds → Bulk cost: 1
  • both ways find the same names, five queries in all → Same names: true and Total queries: 5

Requirements:

  1. Save Limits.getQueries() in loopStart before the loop and subtract it after the loop. Print the result as Loop cost:.
  2. Load the three Profiles again with one query that uses IN :profileIds, and add each Name to bulkNames.
  3. Measure that query the same way and print it as Bulk cost:.
  4. Print Same names: with whether both Lists hold the same names. Compare them as Sets, because the two queries may return rows in a different order.
  5. Print Total queries: with Limits.getQueries() as the last line.

Starter code

List<Profile> picked = [SELECT Id FROM Profile ORDER BY Name LIMIT 3];
Set<Id> profileIds = new Map<Id, Profile>(picked).keySet();

Integer loopStart;
List<String> loopNames = new List<String>();
for (Profile item : picked) {
    Profile row = [SELECT Name FROM Profile WHERE Id = :item.Id];
    loopNames.add(row.Name);
}

List<String> bulkNames = new List<String>();
Integer loopCost = -1;
Integer bulkCost = -1;
System.debug('Loop cost: ' + loopCost);
System.debug('Bulk cost: ' + bulkCost);

When it passes

The loop costs 3 queries, the bulk query costs 1, and both find the same three Profile names. Every cost comes from live limit counters.

Try this drill free

You write the Apex yourself. ApexSensei runs it and tells you what passed and what did not.

Build Contacts with one query and one insert ›