💃 Guide to selective SOQL 🕺

Good morning, Salesforce Nerds! How many times have you heard users complain about waiting for data to load? Reports, list views, Apex taking forever? ⌛️ 

Many times this can be the result of a poorly written query. 🫠 But, how do you know?

Luckily, Salesforce provides a cheat sheet for us to ensure we’re optimizing 🔥 our interactions with the database!

Let’s nerd out over this and check out how to make sure our queries are FAST! 💨 

Agenda for today includes

• Guide to selective SOQL

• Daily Principle

• All the Memes

Guide to selective SOQL

First, we need some context. Just what does “selective” mean? 🤔 

In short, a query is considered selective if:

1. one of its filters is on an indexed field and 

2. this filter reduces the number of records returned to less than the system-defined threshold

❗️❗️❗️ This is an important concept to understand. ❗️❗️❗️ 

If the queries running against your org aren’t selective then this means they’re performing full table scans. Don’t be that person.

Now we know we want to include indexed fields in our WHERE clauses we need to understand how to find these. 👀 

By default, Salesforce indexes the following fields for you on each Object:

• Primary keys (ID, Name, and Owner fields).

• Foreign keys (lookup or master-detail relationship fields).

• Audit dates (such as SystemModStamp).

• Custom fields marked as External ID or Unique.

If you have the option, include at least one of these in your queries! The mothership 🛸 can also add custom indexes for you if you need one.

This is step ☝️ towards a selective query!

The last thing to do is determine if the indexed filters in your query return less than the system-defined threshold. 😕 

It’s a simple formula really:

If the filter does not exceed these thresholds, then it’s selective and you’re good to go! 💯 

Let’s see an example lifted straight off the official Salesforce article! Just follow these steps to determine the selectivity of your queries:

1. Check the total number of records in your Object ✔️ 

SELECT count() FROM Appointment__c

# of records: 239,619

Threshold for standard indexes: 30% x 239,619 = 71,885
Threshold for custom indexes: 10% x 239,619 = 23,961

2. Write your SOQL query and replace the list of fields with the count() operator ✔️ 

SELECT count() 
FROM Appointment__c 
WHERE CreatedDate = THIS_YEAR
AND Status__c = 'Available'

3. The only filter in the WHERE clause with an index is CreatedDate, so we will only consider the filter on this field ✔️ 

Partial SOQL query: 

SELECT count() 
FROM Appointment__c 
WHERE CreatedDate = THIS_YEAR

# of records: 84,567 

We can see the amount of records returned here is more than the system-defined threshold of 30% so this query would not be considered selective by the platform.

The next time you’re building a SOQL query, report, or list view remember these steps and your user base will thank you!

Daily Principle

and now....Your Daily Memes