SOQL Lib Performance Measures

[pgajek2]

Is your feature request related to a problem? Please describe.
Measure SOQL Performance and store the details in the documentation.

Describe the solution you'd like

• Compare SOQL Lib with standard query - include data transformation
• Compare SOQL Lib with FFLib

[nwcm]

Some quick and dirty comparison, I am yet to validate. Most of this was agent junk

ANON APEX

// Choose your object and (optionally) tweak plan parameters
SoqlBenchmarkHarness.BenchmarkPlan plan = new SoqlBenchmarkHarness.BenchmarkPlan();
plan.sobjectType = Account.SObjectType;
plan.whereClause = 'Id != null';
plan.widths      = new List<Integer>{3, 10, 20};
plan.depths      = new List<Integer>{50, 500, 1000};
plan.repeatsPerScenario = 5;       // auto-scaled if needed
plan.warmupsPerEngine   = 2;
plan.doColdAndWarm      = true;    // adds the OFFSET 0 cold pass
plan.maxTotalRowsBudget = 40000;   // guard vs 50k row limit
plan.maxQueryBudget     = 95;      // guard vs 100 SOQL limit

// Run each engine separately (one at a time) to avoid 101
SoqlBenchmarkHarness.BenchmarkReport nativeRep = SoqlBenchmarkHarness.runNative(plan);
// SoqlBenchmarkHarness.BenchmarkReport fflibRep  = SoqlBenchmarkHarness.runFflib(plan);
// SoqlBenchmarkHarness.BenchmarkReport soqlRep   = SoqlBenchmarkHarness.runSoqlLib(plan);

// Each report logs JSON to the debug log under "SOQL BENCHMARK REPORT (engine)"

APEX

// SoqlBenchmarkHarness.cls
// One-engine-per-run SOQL performance harness for:
//   1) Native dynamic SOQL
//   2) fflib_QueryFactory
//   3) SOQL Lib (Apex Fluently)
//
// Key properties:
// - Runs a single engine per transaction to avoid SOQL 101.
// - Warm-ups neutralize first-time JIT/describe/library overhead.
// - Warm vs "cold-ish" (OFFSET 0) passes to reveal caching effects
//   without changing the result set.
// - Width (number of fields) and depth (LIMIT) matrices.
// - Auto-scales repeats to stay under SOQL and row budgets.
// - Captures wall ms, CPU ms, heap deltas, queries used, rows.
//
// Requirements:
//   - fflib-apex-common installed for QueryFactory.
//   - SOQL Lib installed for SOQL.of(...).
//
// This class executes WITHOUT SHARING to keep a consistent access context across engines.
// SOQL Lib is further set to systemMode().withoutSharing() for parity.

public without sharing class SoqlBenchmarkHarness {
  // --------------------- Data Contracts ---------------------

  public class BenchmarkPlan {
    public Schema.SObjectType sobjectType;
    public String whereClause = 'Id != null';
    public String orderByField = 'Id';
    public String orderByDirection = 'DESC';

    public List<Integer> widths = new List<Integer>{ 3, 10 };
    public List<Integer> depths = new List<Integer>{ 50, 500 };

    public Integer repeatsPerScenario = 5; // measured repeats
    public Integer warmupsPerEngine = 2; // pre-heats (discarded)
    public Boolean doColdAndWarm = true;

    public Integer maxTotalRowsBudget = 40000; // guard vs 50k rows/tx
    public Integer maxQueryBudget = 95; // keep well under 100 SOQL

    public Set<Schema.DisplayType> allowedTypes; // optional allow-only list
  }

  public class EngineResult {
    public String engine; // 'native' | 'fflib' | 'soql-lib'
    public String passType; // 'warm' | 'cold'
    public Integer width;
    public Integer depth;
    public Integer repeats;

    public Long avgWallMs;
    public Long minWallMs;
    public Long maxWallMs;
    public Long avgCpuMs;
    public Integer avgHeapDelta;

    public Integer lastReturnedRows;
    public Integer totalRowsRead;
    public Integer totalQueriesUsed;

    public List<String> sampleSoql;
    public List<String> errors;
  }

  public class BenchmarkReport {
    public String engine;
    public String sobjectApiName;
    public String whereClause;
    public String orderBy;
    public List<Integer> widths;
    public List<Integer> depths;
    public Integer repeatsPerScenario;
    public Integer warmups;
    public List<EngineResult> results = new List<EngineResult>();

    public String toPrettyJson() {
      return JSON.serializePretty(this);
    }
  }

  // --------------------- Public Entry Points ---------------------

  public static BenchmarkReport runNative(BenchmarkPlan plan) {
    return runForSingleEngine(plan, 'native');
  }
  public static BenchmarkReport runFflib(BenchmarkPlan plan) {
    return runForSingleEngine(plan, 'fflib');
  }
  public static BenchmarkReport runSoqlLib(BenchmarkPlan plan) {
    return runForSingleEngine(plan, 'soql-lib');
  }

  // --------------------- Orchestration (single engine) ---------------------

  private static BenchmarkPlan normalizeAndScale(BenchmarkPlan plan) {
    if (plan == null)
      throw new IllegalArgumentException('BenchmarkPlan is required.');
    if (plan.sobjectType == null)
      throw new IllegalArgumentException('BenchmarkPlan.sobjectType must be set.');

    // 1) Row budget scaling (rows ~ sum(depths) * widths.size * repeats)
    Integer sumDepths = 0;
    for (Integer d : plan.depths)
      sumDepths += d;
    Integer estRows = sumDepths * plan.widths.size() * plan.repeatsPerScenario;
    if (estRows > plan.maxTotalRowsBudget) {
      Decimal scale = (Decimal) plan.maxTotalRowsBudget / Math.max(1, estRows);
      Integer newRepeats = Math.max(1, (Integer) Math.floor(plan.repeatsPerScenario * scale));
      System.debug(
        LoggingLevel.WARN,
        'Repeats reduced from ' +
          plan.repeatsPerScenario +
          ' to ' +
          newRepeats +
          ' to respect row budget ' +
          plan.maxTotalRowsBudget
      );
      plan.repeatsPerScenario = newRepeats;
    }

    // 2) SOQL query budget scaling for single engine:
    // measured = widths * depths * passCount * repeats; warmups = warmupsPerEngine
    Integer passCount = plan.doColdAndWarm ? 2 : 1;
    Integer measured =
      plan.widths.size() *
      plan.depths.size() *
      passCount *
      plan.repeatsPerScenario;
    Integer warmups = plan.warmupsPerEngine;
    Integer totalPlanned = measured + warmups;
    if (totalPlanned > plan.maxQueryBudget) {
      Integer numerator = Math.max(1, plan.maxQueryBudget - warmups);
      Integer denominator = Math.max(1, plan.widths.size() * plan.depths.size() * passCount);
      Integer newRepeats = Math.max(1, numerator / denominator);
      if (newRepeats < plan.repeatsPerScenario) {
        System.debug(
          LoggingLevel.WARN,
          'Repeats reduced from ' +
            plan.repeatsPerScenario +
            ' to ' +
            newRepeats +
            ' to respect SOQL query budget ' +
            plan.maxQueryBudget
        );
        plan.repeatsPerScenario = newRepeats;
      }
      measured = plan.widths.size() * plan.depths.size() * passCount * plan.repeatsPerScenario;
      totalPlanned = measured + warmups;
      if (totalPlanned > plan.maxQueryBudget && plan.doColdAndWarm) {
        plan.doColdAndWarm = false;
        System.debug(
          LoggingLevel.WARN,
          'Disabled cold pass to respect SOQL query budget ' + plan.maxQueryBudget
        );
      }
    }
    return plan;
  }

  private static BenchmarkReport runForSingleEngine(BenchmarkPlan plan, String engine) {
    plan = normalizeAndScale(plan);

    // Discover/select fields once for fairness
    List<String> selectableFields = pickSelectableFields(plan);

    // Warm up the chosen engine (LIMIT 1)
    List<String> warmFields = takeFirst(selectableFields, 3);
    for (Integer i = 0; i < plan.warmupsPerEngine; i++) {
      try {
        execOnce(
          engine,
          plan,
          warmFields,
          1,
          plan.whereClause,
          plan.orderByField,
          plan.orderByDirection,
          false,
          null
        );
      } catch (Exception e) {
        /* ignore */
      }
    }

    BenchmarkReport report = new BenchmarkReport();
    report.engine = engine;
    report.sobjectApiName = plan.sobjectType.getDescribe().getName();
    report.whereClause = plan.whereClause;
    report.orderBy = plan.orderByField + ' ' + plan.orderByDirection;
    report.widths = new List<Integer>(plan.widths);
    report.depths = new List<Integer>(plan.depths);
    report.repeatsPerScenario = plan.repeatsPerScenario;
    report.warmups = plan.warmupsPerEngine;

    // Iterate width × depth × pass
    for (Integer width : plan.widths) {
      List<String> fields = takeFirst(selectableFields, width);
      for (Integer depth : plan.depths) {
        // Warm pass (no offset)
        report.results.add(runScenario(engine, plan, fields, depth, 'warm', false));
        // Cold-ish pass (OFFSET 0)
        if (plan.doColdAndWarm) {
          report.results.add(runScenario(engine, plan, fields, depth, 'cold', true));
        }
      }
    }

    System.debug('SOQL BENCHMARK REPORT (' + engine + ')\n' + report.toPrettyJson());
    return report;
  }

  private static EngineResult runScenario(
    String engine,
    BenchmarkPlan plan,
    List<String> fields,
    Integer depth,
    String passType,
    Boolean addOffsetZero
  ) {
    EngineResult r = new EngineResult();
    r.engine = engine;
    r.passType = passType;
    r.width = fields.size();
    r.depth = depth;
    r.repeats = plan.repeatsPerScenario;
    r.sampleSoql = new List<String>();
    r.errors = new List<String>();

    Long wallSum = 0L, cpuSum = 0L;
    Integer heapSum = 0;
    Long wallMin = null, wallMax = null;
    Integer totalRows = 0, totalQueries = 0;

    for (Integer rep = 0; rep < plan.repeatsPerScenario; rep++) {
      Integer heapBefore = Limits.getHeapSize();
      Integer cpuBefore = Limits.getCpuTime();
      Integer qBefore = Limits.getQueries();
      Long t0 = nowMs();

      Integer returned = 0;
      try {
        returned = execOnce(
          engine,
          plan,
          fields,
          depth,
          plan.whereClause,
          plan.orderByField,
          plan.orderByDirection,
          addOffsetZero,
          r.sampleSoql
        );
      } catch (Exception e) {
        r.errors.add('repeat ' + rep + ': ' + e.getMessage());
      }

      Long wall = nowMs() - t0;
      Integer cpu = Limits.getCpuTime() - cpuBefore;
      Integer heapDelta = Limits.getHeapSize() - heapBefore;
      Integer qUsed = Limits.getQueries() - qBefore;

      wallSum += wall;
      cpuSum += cpu;
      heapSum += heapDelta;
      totalRows += (returned == null ? 0 : returned);
      totalQueries += (qUsed == null ? 0 : qUsed);

      wallMin = (wallMin == null ? wall : Math.min(wallMin, wall));
      wallMax = (wallMax == null ? wall : Math.max(wallMax, wall));
    }

    r.avgWallMs = (plan.repeatsPerScenario > 0
      ? Math.round((Decimal) wallSum / plan.repeatsPerScenario)
      : 0);
    r.minWallMs = (wallMin == null ? 0 : wallMin);
    r.maxWallMs = (wallMax == null ? 0 : wallMax);
    r.avgCpuMs = (plan.repeatsPerScenario > 0
      ? Math.round((Decimal) cpuSum / plan.repeatsPerScenario)
      : 0);
    r.avgHeapDelta = (plan.repeatsPerScenario > 0
      ? Math.round((Decimal) heapSum / plan.repeatsPerScenario)
      : 0);
    r.totalRowsRead = totalRows;
    r.totalQueriesUsed = totalQueries;
    r.lastReturnedRows = (plan.repeatsPerScenario > 0 ? totalRows / plan.repeatsPerScenario : 0);

    return r;
  }

  // --------------------- Single execution for an engine ---------------------

  private static Integer execOnce(
    String engine,
    BenchmarkPlan plan,
    List<String> fields,
    Integer depth,
    String whereExpr,
    String orderField,
    String orderDir,
    Boolean addOffsetZero,
    List<String> sampleSoqlOut
  ) {
    if (engine == 'native') {
      return executeNative(
        plan,
        fields,
        depth,
        whereExpr,
        orderField,
        orderDir,
        addOffsetZero,
        sampleSoqlOut
      );
    } else if (engine == 'fflib') {
      return executeFflib(
        plan,
        fields,
        depth,
        whereExpr,
        orderField,
        orderDir,
        addOffsetZero,
        sampleSoqlOut
      );
    } else if (engine == 'soql-lib') {
      return executeSoqlLib(
        plan,
        fields,
        depth,
        whereExpr,
        orderField,
        orderDir,
        addOffsetZero,
        sampleSoqlOut
      );
    }
    throw new IllegalArgumentException('Unknown engine: ' + engine);
  }

  // --------------------- Engine Implementations ---------------------

  // (1) Native dynamic SOQL
  private static Integer executeNative(
    BenchmarkPlan plan,
    List<String> fields,
    Integer depth,
    String whereExpr,
    String orderField,
    String orderDir,
    Boolean addOffsetZero,
    List<String> sampleSoqlOut
  ) {
    String fieldCsv = String.join(fields, ', ');
    String soql =
      'SELECT ' +
      fieldCsv +
      ' FROM ' +
      plan.sobjectType.getDescribe().getName() +
      ' WHERE ' +
      whereExpr +
      ' ORDER BY ' +
      orderField +
      ' ' +
      orderDir +
      ' LIMIT ' +
      depth +
      (addOffsetZero ? ' OFFSET 0' : '');

    if (sampleSoqlOut != null && sampleSoqlOut.size() < 2)
      sampleSoqlOut.add(soql);
    List<SObject> rows = Database.query(soql);
    return (rows == null ? 0 : rows.size());
  }

  // (2) fflib_QueryFactory
  private static Integer executeFflib(
    BenchmarkPlan plan,
    List<String> fields,
    Integer depth,
    String whereExpr,
    String orderField,
    String orderDir,
    Boolean addOffsetZero,
    List<String> sampleSoqlOut
  ) {
    fflib_QueryFactory qf = new fflib_QueryFactory(plan.sobjectType);
    qf.selectFields(new Set<String>(fields));
    qf.setCondition(whereExpr);
    fflib_QueryFactory.SortOrder sortOrder = (orderDir == 'DESC'
      ? fflib_QueryFactory.SortOrder.DESCENDING
      : fflib_QueryFactory.SortOrder.ASCENDING);
    qf.addOrdering(orderField, sortOrder);
    qf.setLimit(depth);
    if (addOffsetZero)
      qf.setOffset(0); // OFFSETS supported by QueryFactory

    String soql = qf.toSOQL();
    if (sampleSoqlOut != null && sampleSoqlOut.size() < 2)
      sampleSoqlOut.add(soql);
    List<SObject> rows = Database.query(soql);
    return (rows == null ? 0 : rows.size());
  }

  // (3) SOQL Lib (Apex Fluently)
  private static Integer executeSoqlLib(
    BenchmarkPlan plan,
    List<String> fields,
    Integer depth,
    String whereExpr,
    String orderField,
    String orderDir,
    Boolean addOffsetZero,
    List<String> sampleSoqlOut
  ) {
    SOQL.Queryable q = SOQL.of(plan.sobjectType)
      .with(fields)
      .whereAre(whereExpr)
      .orderBy(orderField, orderDir)
      .setLimit(depth);
    if (addOffsetZero)
      q = q.offset(0); // vary text, not results
    q = q.systemMode().withoutSharing(); // parity of access model

    if (sampleSoqlOut != null && sampleSoqlOut.size() < 2) {
      try {
        sampleSoqlOut.add(q.toString());
      } catch (Exception e) {
      }
    }
    List<SObject> rows = q.toList();
    return (rows == null ? 0 : rows.size());
  }

  // --------------------- Helpers ---------------------

  private static Long nowMs() {
    return System.now().getTime();
  }

  private static List<String> takeFirst(List<String> src, Integer n) {
    n = Math.max(0, Math.min(n, src.size()));
    List<String> out = new List<String>();
    for (Integer i = 0; i < n; i++)
      out.add(src[i]);
    return out;
  }

  private static List<String> pickSelectableFields(BenchmarkPlan plan) {
    Map<String, Schema.SObjectField> fmap = plan.sobjectType.getDescribe().fields.getMap();
    List<String> out = new List<String>();
    for (String api : fmap.keySet()) {
      Schema.DescribeFieldResult fd = fmap.get(api).getDescribe();
      if (fd.isDeprecatedAndHidden())
        continue;
      Schema.DisplayType t = fd.getType();

      if (plan.allowedTypes == null) {
        // avoid heavy/special types for a fair apples-to-apples projection width
        if (t == Schema.DisplayType.Base64)
          continue;
        if (t == Schema.DisplayType.Address)
          continue;
        if (t == Schema.DisplayType.Location)
          continue;
        if (t == Schema.DisplayType.AnyType)
          continue;
      } else if (!plan.allowedTypes.contains(t)) {
        continue;
      }
      out.add(api);
    }
    if (!out.contains('Id'))
      out.add('Id');
    out.sort(); // deterministic order
    return out;
  }
}

[pgajek2]

Thanks @nwcm , great staff!