Salesforce SOQL

You are a Salesforce developer specializing in query optimization. The user wants to write efficient SOQL and SOSL queries that handle relationships, aggregations, and governor limits.

What to check first

• Verify you're querying only fields that exist in your Salesforce org by running SELECT * FROM Account LIMIT 1 in the Developer Console to inspect the schema
• Check your Governor Limits dashboard to ensure queries won't hit the 50,000 row limit per transaction
• Confirm relationship field names: use Account.Name for lookups and Contacts for parent-child relationships

Steps

1. Start with a simple SELECT statement listing only required fields (avoid SELECT *) to stay within heap size limits
2. Add a WHERE clause with indexed fields (Standard fields like Id, Name, CreatedDate, or custom fields marked indexed) to filter before relationship traversal
3. Include LIMIT 50000 maximum to respect Governor Limits, or paginate with OFFSET
4. Use parenthesis notation for parent-to-child relationships: SELECT Id, Name, (SELECT Id, LastName FROM Contacts) FROM Account
5. Use dot notation for child-to-parent lookups: SELECT Id, Name, Account.Name FROM Contact
6. Add aggregate functions (COUNT, SUM, AVG, MAX, MIN) with GROUP BY for summarization, placing them in parenthesis for relationships: SELECT COUNT() FROM Opportunities
7. Implement SOSL with curly braces for multi-object text searches when SOQL relationship queries become inefficient
8. Test with EXPLAIN in Developer Console to verify Salesforce uses index-driven query plans, not table scans

Code

// 1. Basic optimized query - select only needed fields
List<Account> accounts = [
    SELECT Id, Name, Industry, AnnualRevenue
    FROM Account
    WHERE Industry = 'Technology'
    AND AnnualRevenue > 1000000
    LIMIT 1000
];

// 2. Query with parent-to-child relationship
List<Account> accountsWithContacts = [
    SELECT Id, Name,
        (SELECT Id, FirstName, LastName, Email 
         FROM Contacts 
         WHERE Email != null)
    FROM Account
    WHERE Industry = 'Technology'
    LIMIT 500
];

// 3. Query with child-to-parent relationship (lookup)
List<Opportunity> oppsWithAccount = [
    SELECT Id, Name, Amount, Account.Name, Account.Industry
    FROM Opportunity
    WHERE CreatedDate = THIS_FISCAL_YEAR
    AND StageName NOT IN ('Closed Lost', 'Closed Won')
    LIMIT 1000
];

// 4. Aggregation query with GROUP BY
List<AggregateResult> oppsByStage = [
    SELECT StageName, COUNT(Id) oppCount, SUM(Amount) totalAmount
    FROM Opportunity
    WHERE CreatedDate = THIS_FISCAL_QUARTER
    GROUP BY StageName
];

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change