Deep Dive: Designing Efficient SOQL for Large Salesforce Data Volumes

A SOQL query that is fast with 50,000 records can become an architectural problem with 50 million.

Large Data Volume (LDV) design is not about memorizing one selectivity percentage. It is about understanding how Salesforce's query optimizer chooses access paths, measuring the plan in the real org, and choosing a retrieval pattern appropriate to the workload.

1. SOQL Runs Through Salesforce's Query Optimizer

Salesforce does not expose its underlying multitenant database as direct SQL. SOQL is evaluated through the platform's query layer and query optimizer.

Salesforce's engineering guidance explains that the optimizer uses platform-specific statistics and chooses an access path, including whether an index is useful.

That means "this field is indexed" does not automatically mean "this query will use the index efficiently."

Selectivity, operator choice, data distribution, total record count, sharing, and the combination of predicates all matter.

2. Selectivity Is About Reducing the Candidate Set

Consider:

SELECT Id, Name
FROM Account
WHERE Status__c = 'Active'

If almost every Account is Active, the predicate may not narrow the data enough to make an index useful even if Status__c is indexed.

Now consider a more discriminating business key or partition:

SELECT Id, Name
FROM Account
WHERE Region__c = 'APAC'
  AND Status__c = 'Active'
  AND SystemModstamp >= 2026-08-23T00:00:00Z

The exact plan depends on the org's data. The lesson is to design filters that narrow the candidate population in ways aligned with the workload.

3. Use Query Plan, Don't Guess

Salesforce provides Query Plan tooling. Current Salesforce developer tooling can retrieve a plan and show operations and relative cost.

Use it when a query matters operationally, especially for LDV.

Look for:

which operation drives the plan;
whether an index is considered;
relative cost;
cardinality/selectivity notes;
how the plan changes when predicates change.

Performance tuning should be evidence-driven.

4. Indexed Fields and Operator Choice

Salesforce's query-optimizer guidance repeatedly emphasizes that an index is useful only when the predicate/operator can use it and the result is sufficiently selective.

Common performance traps include negative predicates and leading-wildcard searches. Salesforce engineering guidance notes that NOT-style conditions and LIKE '%value' patterns do not use indexes in the same way selective positive predicates can.

Prefer a positively selective business condition when the data model permits it.

5. Leading Wildcards Are Expensive by Nature

WHERE Name LIKE '%Systems%'

asks for a contains-style search. The database cannot simply seek from the beginning of a normal index as it can with a useful prefix.

If contains search is a core high-volume requirement, reconsider whether SOQL on that field is the correct search mechanism rather than trying to micro-optimize an inherently broad predicate.

6. Select Only What You Need

FIELDS(ALL) is convenient for exploration, but production LDV integrations should generally be deliberate about payload shape.

SELECT Id, External_Id__c, SystemModstamp
FROM Account
WHERE SystemModstamp > :checkpoint

is easier on network, serialization, and downstream memory than retrieving every field when only three are required.

Query performance is not only database execution time. End-to-end performance includes payload size, API transfer, deserialization, memory, and processing.

7. Pagination: Avoid Deep OFFSET as a General Extraction Strategy

OFFSET is convenient for small interactive pages, but it is not the pattern I would choose for extracting millions of records.

For deterministic traversal, keyset/seek-style pagination is usually a better mental model:

SELECT Id, Name
FROM Account
WHERE Id > :lastId
ORDER BY Id
LIMIT 2000

For incremental integrations, a compound checkpoint can be safer when many records share the same timestamp:

SELECT Id, SystemModstamp
FROM Account
WHERE SystemModstamp > :lastTimestamp
   OR (SystemModstamp = :lastTimestamp AND Id > :lastId)
ORDER BY SystemModstamp, Id
LIMIT 2000

The exact query must respect SOQL syntax and your workload, but the architecture principle is stable ordering plus a durable checkpoint.

8. ORDER BY + LIMIT Can Be an Optimization Pattern

Salesforce engineering guidance describes sort optimization: in some LDV scenarios, ORDER BY on an indexed field combined with LIMIT can allow the optimizer to walk a presorted index and stop after enough records are found.

This is not a magic fix and should be validated with Query Plan and representative data.

The useful lesson is that query shape - not just the WHERE clause - can affect the optimizer's available strategies.

9. Deleted Records Can Affect Selectivity

Salesforce engineering guidance has historically called out that recently deleted records can influence the total-record calculations used by the optimizer until physical deletion occurs.

Large loads/deletes can therefore change performance characteristics even when application code did not change.

This is another reason to monitor query plans and latency over time rather than treating performance testing as a one-time activity.

10. Separate Transactional Queries from Bulk Extraction

Do not force one SOQL/API pattern to serve every workload.

Interactive/transactional

Use selective queries, tight limits, predictable latency, and only required fields.

Incremental integration

Use a durable change/checkpoint strategy such as SystemModstamp where appropriate, stable ordering, retries, and idempotent processing. For event-driven use cases, evaluate Change Data Capture rather than polling broad windows forever.

Very large extraction

Use Bulk API capabilities appropriate to the data size. Salesforce documents PK Chunking as a technique for splitting very large extracts into manageable chunks based on record IDs for supported objects.

A query returning tens or hundreds of millions of rows is not just "a bigger REST query."

11. PK Chunking for Huge Data Sets

Salesforce's engineering guidance describes PK Chunking for extracts involving tens or hundreds of millions of records. The technique divides the dataset by primary key so multiple smaller queries can be processed more reliably.

Use it when the workload is truly bulk extraction and the object/API supports the pattern.

Do not use it as a substitute for selectivity in ordinary application queries.

12. Formula Fields and Calculated Predicates

Filtering on calculated values can be convenient but may complicate selectivity. Do not assume a formula-based condition has the same index behavior as a straightforward indexed field predicate.

Salesforce also introduced FORMULA() in SOQL WHERE as a Summer '26 pilot. Pilot availability should be treated explicitly; it does not turn arbitrary calculated filtering into a guaranteed LDV optimization.

If a high-volume integration repeatedly filters by an important business dimension, consider whether the data model should expose that dimension in a query-friendly, indexable way.

13. Relationship Queries Can Multiply Work

Relationship queries are one of SOQL's strengths, but broad parent/child retrieval can produce large logical result sets.

Ask whether the consumer truly needs the relationship in one query. Sometimes two selective queries with controlled joining in the integration are easier to scale and retry than one very broad relationship query.

Salesforce engineering guidance has similarly noted that complex joins can sometimes perform better as separate queries.

14. Design for Data Skew

Even a seemingly selective field can become problematic when values are heavily skewed.

If 90% of records share the same tenant, owner, status, or category, filtering by that value does little to narrow the candidate set.

LDV architecture should examine actual distributions, not only field definitions.

15. A Practical Tuning Workflow

When a production SOQL query slows down:

capture the exact query shape and bind-value characteristics;
measure current latency and returned cardinality;
inspect Query Plan;
identify the leading candidate predicate/index;
check data distribution and recent volume changes;
remove unnecessary fields;
replace negative/broad predicates where business semantics permit;
test additional selective predicates;
evaluate ORDER BY/LIMIT patterns where applicable;
reconsider the API/pattern if the workload is actually bulk extraction or change propagation.

16. Architecture Decision Matrix

Workload	Prefer
User-facing lookup	Selective SOQL + tight LIMIT
Admin/search experience	Appropriate search/query capability based on search semantics
Incremental polling	Stable checkpoint + selective SOQL
Near-real-time change propagation	Change Data Capture / event-driven design
Huge historical extraction	Bulk API; evaluate PK Chunking
Aggregate question	SOQL aggregate query instead of retrieving every row

17. What Not to Do

Avoid these habits:

assuming every indexed field guarantees a fast query;
using FIELDS(ALL) in high-volume integrations by default;
deep OFFSET pagination for huge exports;
leading % searches on massive objects without questioning the search design;
polling millions of rows when CDC better matches the requirement;
tuning from sandbox-sized data and assuming production will behave identically;
persisting checkpoints before a batch is safely processed.

Final Principle

Efficient SOQL at large scale is an architecture problem disguised as a query problem.

The best optimization may be a better predicate. It may be an index. It may be a stable pagination key, a Bulk API extraction, PK Chunking, or replacing polling with Change Data Capture.

Start with the business access pattern, inspect the real Query Plan, and choose the Salesforce capability that matches the workload.