Selective SOQL and large data volume queries
How the query optimizer decides whether a filter can use an index, how to rewrite negative, null, OR and formula filters so it can, and which structure (SOQL for loop, Batch QueryLocator, SOSL, OFFSET, hard delete) fits a large data volume scenario.
Given a scenario, choose the appropriate logic and query structure to maximize application performance and handle large data volumes.
~8 min read · 13 practice questions ·
What the exam expects
- Decide whether a filter is selective from the field's index type, the operator and the number of matching rows.
- Rewrite negative, null, OR and cross-object formula filters so the optimizer can use an index, and confirm it with the Query Plan tool.
- Choose a SOQL for loop, a Batch Apex QueryLocator or SOSL for large result sets and multi-object text search.
- Know the OFFSET ceiling and why large deletes should bypass the Recycle Bin.
Key terms: selective filter (a filter on an indexed field that matches fewer rows than the index threshold), standard index (maintained by the platform on fields such as Id, Name, lookups and CreatedDate), custom index (on a custom field, from an External ID or Unique flag or a Support request), Query Plan tool (Developer Console view of the optimizer's plans and costs), soft delete (a deleted row kept in the Recycle Bin that every query must still exclude).
Key facts
- Detail
- <10% of rows, max 333,333 (LDV guide; the Query Plan FAQ tiers it: 10% of first million + 5% beyond)
- Detail
- <30% of first million + <15% of the rest, max 1,000,000
- Detail
- Every OR field must be indexed for any index to be used
- Detail
- Id, Name, OwnerId, lookups, CreatedDate, SystemModstamp, RecordType, External ID, Unique
- Detail
- 50,000; a batch QueryLocator returns up to 50 million
- Detail
- 6 MB synchronous, 12 MB asynchronous
- Detail
- 2,000 rows, else
NUMBER_OUTSIDE_VALID_RANGE
- Detail
- 15 days; soft-deleted rows still slow queries
Sources for this section (7)
- Indexesdeveloper.salesforce.com (opens in a new tab)
- Query Plan Tool FAQhelp.salesforce.com (opens in a new tab)
- Working with Very Large SOQL Queriesdeveloper.salesforce.com (opens in a new tab)
- Using Batch Apexdeveloper.salesforce.com (opens in a new tab)
- Execution Governors and Limitsdeveloper.salesforce.com (opens in a new tab)
- OFFSETdeveloper.salesforce.com (opens in a new tab)
- Deleting Data (Techniques)developer.salesforce.com (opens in a new tab)
- Why does `Promo_Code__c` without an index force a scan even though the other OR field is a lookup?
- When should a search page use SOSL instead of several `LIKE` queries?
- Why does the 42nd page of a 50-row OFFSET pager fail while the 41st works?
- Which tool shows whether a query uses an index before you request one?
The questions are free to read. Answers with their sources and self-grading are included with the pack.
Keep reading with the Platform Developer II pack
- Core concepts
- Exam traps
- Worked scenario
The pack includes:
- All 28 lessons of the Platform Developer II guide, with reading progress
- Interactive decision walks and step-by-step sequence steppers
- Inline practice questions and one-click topic practice (13 questions for this lesson)
- An official source cited for every claim
- An AI tutor that answers from the guide
- Unlimited timed practice exams