Salesforce Use a Two-Phase Query to Access High-Volume Privacy Center Retained Data

Plain Language Summary: How the Two-Phase Approach Works

The two-phase query approach solves the performance problem by splitting one slow query into two faster queries. In Phase 1, you run a lightweight query that uses your filter condition to retrieve only the record IDs that match. Because this query only returns IDs (not all fields), it runs faster even against large data volumes. In Phase 2, you take those record IDs and run a second query that uses the optimized Id_c field (which is indexed) in an IN clause to retrieve the full record data efficiently. This avoids the performance issue caused by querying large volumes of data using non-optimized relationship fields in a single query.

Phase 1 — Find IDs Using the Desired Filter

The first query uses the desired filter to find a limited set of record IDs. Copy the resulting Id_c values to use in Phase 2.
Query format: SELECT Id_c FROM [RetentionObject]_x WHERE [LookupField]_c = 'value' LIMIT 200
Replace [RetentionObject]_x with your retention object API name and [LookupField]_c with the lookup field name you are filtering on.

Phase 2 — Retrieve Full Records Using the Optimized Id_c Field

The second query takes the Id_c values from Phase 1 and uses them in an IN clause. Because Id_c is an optimized (indexed) field, this query executes efficiently and retrieves all fields for the matching records.
Query format: SELECT FIELDS(ALL) FROM [RetentionObject]_x WHERE Id_c IN ('id1', 'id2', 'id3', ...) LIMIT 200
Replace the id1, id2, id3 placeholders with the actual Id_c values returned from Phase 1.

Note

If you continue to experience timeouts after implementing the two-phase SOQL query, contact Salesforce Customer Support to request compaction of your retention store data. Compacting data can improve query performance and reduce the risk of timeouts.