What is Selective Query in Salesforce

 In Salesforce SOQL, a Selective Query is a query that efficiently uses indexes to return a small subset of records, avoiding full table scans and staying within governor limits (especially the 100,000-row limit).

✅ What Is a Selective Query?

A selective query is one that:

• Uses indexed fields in the WHERE clause

• Returns a small result set

• Avoids performance issues or timeouts during execution

🚫 Non-Selective Query (Bad Example)

sql
SELECT Id, Name FROM Contact WHERE FirstName LIKE '%John%'

• %John% disables index usage

• Can trigger Too many query rows: 100001

---

✅ Selective Query (Good Example)

sql
SELECT Id, Name FROM Contact WHERE AccountId = '001ABC123456789' LIMIT 100

• AccountId is an indexed field

• Returns limited records

🔍 Selectivity Rule of Thumb

For standard or custom objects:

• For a query to be selective, it should filter on an indexed field and return:

  • < 10% of records (if > 1 million records)

  • < 100,000 total rows returned

📌 Indexed Fields (Default)

Type	Examples
Standard	Id, Name, OwnerId, CreatedDate
Custom	Fields marked as External ID or Unique
System-created	RecordTypeId, MasterDetailId, LookupId

You can also request a custom index from Salesforce Support.

🛠️ Tools to Analyze Query Selectivity

1. Query Plan Tool in Developer Console:

   • Shows if the query is selective

   • Use: Query Plan tab after pasting a SOQL query

2. Explain Plan in Workbench:

   • Go to Utilities → Query Plan

   • Paste SOQL to check cost & cardinality

🧠 Tips to Make Queries Selective

• Use indexed fields in filters

• Use = or IN operators (not !=, NOT IN, or LIKE '%abc')

• Avoid filtering on formula fields (not indexable)

• Use skinny tables or custom indexes for reporting-heavy orgs

• Always bulkify Apex logic around SOQL

🧪 Example: Bulk-Safe + Selective

apex
// Query using indexed field and limiting fields fetched
List<Case> cases = [
  SELECT Id, Status, Subject 
  FROM Case 
  WHERE AccountId IN :accountIds
  AND CreatedDate = THIS_MONTH
  LIMIT 500
];

How to download the developer console SOQL query results

 

Hi Friends today we are going to discuss, How to download the developer console  SOQL query results

Step 1: activate user select

Step 2: select results

Step 3: copy & paste it to excel

Query Optimization Salesforce

 

Query optimization in Salesforce refers to the process of improving the performance and efficiency of your Salesforce queries. Salesforce uses a language called SOQL (Salesforce Object Query Language) for querying data. Here are some tips for optimizing your queries in Salesforce:

1.     Indexing:

·        Ensure that the fields used in WHERE clauses are indexed. Indexing allows Salesforce to quickly locate the records that match the specified conditions.

·        Standard fields are generally indexed, but custom fields may need to be manually indexed.

2.     Selective Filtering:

·        Design your queries to be selective. Selective queries are those that filter out a large portion of the records, allowing Salesforce to quickly retrieve the relevant data.

·        Avoid using "LIKE" queries without a leading wildcard ('%') as they are not selective and can result in full table scans.

3.       Query Plan Tool :

·        The Query Plan tool in the Developer Console can help speed up SOQL queries done over large volumes. Use the Query Plan tool to optimize and speed up queries done over large volumes.

4.     Limit the Number of Records Returned

·        Use the LIMIT clause to restrict the number of records returned by a query. This can significantly improve performance, especially when dealing with large datasets.

5.     Avoid Using "NULL" in WHERE Clauses:

·        Queries that include "IS NULL" or "IS NOT NULL" conditions can be less performant. Try to avoid these conditions if possible.

6.     Bulkify Your Code:

·        When writing triggers or batch processes, make sure your code can handle bulk data processing. Avoid using SOQL or DML statements inside loops to prevent hitting governor limits.

7.     Use Aggregate Queries Wisely:

·        Aggregate queries (e.g., using GROUP BY) can be resource-intensive. Use them judiciously and ensure that the fields you are aggregating are indexed.

8.     Query Plan Tool:

·        Use the Query Plan tool in the Developer Console to analyze the query performance. It helps you understand how Salesforce executes your query and identify any potential bottlenecks.

9.     Consider External Objects:

·        For large datasets that are not suitable for traditional Salesforce queries, consider using External Objects and External Data Sources to query data stored outside Salesforce.

10. Review Query Optimizer Warnings:

·        When running a query in the Developer Console, pay attention to any query optimizer warnings. These warnings provide insights into potential performance issues.

11. Use SOQL Best Practices:

·        Familiarize yourself with SOQL best practices outlined in the Salesforce documentation. This includes understanding query syntax, using relationships efficiently, and optimizing queries for performance.