# Reviewing SOQL for readability and risk before it ships A query that runs fine on ten test records can lock up at scale. A checklist for having AI review SOQL for risk, and where to double-check it yourself. **Time:** 9 minutes **What you'll have when you're done:** A repeatable review pass for a SOQL query that catches the common ways one works in a sandbox and fails in production, plus a rewritten, commented version to hand off. **Before you start:** - Any AI chat tool you're allowed to use - A SOQL query, from a trigger, a Flow, a report, or a class ---A query returns the right rows against twenty test records and gets approved. Three months later it runs against forty thousand records inside a trigger that fires on every save, and something that looked fine in review is now the reason a batch job times out. The failure mode isn't wrong logic. It's logic that was only ever tested at a scale where the risk doesn't show up. --- ## What a review actually needs to check Four separate risks, and a query can pass three of them and still fail: 1. **Does it return the right rows?** Correctness, the thing everyone checks. 2. **Does it scale?** What happens at ten thousand records instead of ten. 3. **Is it selective?** Whether it can use an index, or has to scan every record on the object. 4. **Is the intent obvious to the next reader?** A query nobody can explain a year from now is a query nobody will safely change. --- ## Step 1: give it the query and where it runs Context changes what's risky. A query in an anonymous script tolerates things a query inside a trigger cannot. ``` Object(s): [what this queries] Where this runs: [trigger, Flow, batch class, scheduled job, report, LWC] Approximate record volume on the object(s): [rough order of magnitude] The query: [paste it exactly] ``` --- ## Step 2: ask for the specific risks, not a general opinion ``` Review this SOQL query for the four things below. For each one, say pass, risk, or fail, with the specific line or clause and why: 1. Selectivity: does the WHERE clause use an indexed or otherwise selective field, or would this require a full table scan at scale? 2. Governor limit risk: is this inside a loop, and could it run more than once per transaction? 3. Bulk safety: does it handle zero results, and multiple records, correctly, or does it assume exactly one? 4. Clarity: could someone unfamiliar with this object read the WHERE clause and understand what it's selecting for, without you explaining it? Don't suggest a rewrite yet. List findings only. ``` Asking for findings before a rewrite matters here more than in most prompts. A rewrite offered immediately tends to fix the first problem it notices and quietly ignore the rest. --- ## Step 3: verify the loop claim specifically Point 2, whether the query sits inside a loop, is the single most expensive mistake to miss and the easiest for AI to get right or wrong depending on how much surrounding code it can see. If the query is inside a class or trigger, paste the method or trigger body, not just the query line, so it can actually see whether the query executes once per transaction or once per record. ``` Here's the full method this query lives in: [paste it] Confirm: does this query execute exactly once per transaction, or could it run once per loop iteration? Show me the specific lines that determine this. ``` A query correctly declared safe when reviewed in isolation and declared unsafe once the surrounding loop is visible is not a contradiction, it's the review working as intended the second time. --- ## Step 4: ask for the rewrite, with the findings restated ``` Given these confirmed findings: [paste what came back from steps 2 and 3] Rewrite the query to address them. Add a one-line comment above it explaining what it selects for and why, in plain language, for someone who doesn't already know this object. ``` --- ## When it breaks **It calls a query selective when the field isn't actually indexed in your org.** Standard indexed fields (ID, Name, most lookup and master-detail fields, external IDs) are a safe default assumption; a custom field's index status is an org-specific fact AI can't see and shouldn't guess at. Confirm it in Setup before trusting the claim. **It misses a loop because the query and the loop are in different methods.** If a query sits in a helper method called from inside a loop, give it both methods together. A confident "this runs once" that only looked at the query's own method is a false negative, not a real finding. **The rewrite is correct but changes behavior at an edge case you didn't ask about.** Bulkifying a query sometimes changes what happens with zero related records or duplicate keys. Test the rewrite against an edge case explicitly, not just the happy path that passed before. **The query is inside a managed package or something you can't safely change.** Then this review is diagnostic, not actionable. Document what you found and route it to whoever owns that package rather than patching around it. --- ## What to try next Run this pass before a query goes into anything that fires on save or runs in bulk, not only when something's already broken. The four-question structure is fast enough to become a five-minute habit rather than an incident postmortem.

Next