Building fast Salesforce reports, list views, and SOQL queries is easily achievable, even when they target very large objects (say, greater than one million records), as long as you follow this basic best practice:
Create efficient Salesforce reports, list views, and SOQL queries by including at least one selective filter condition that uses optimizable operators and references fields that have indexes.
If you aren’t familiar with the specifics of this best practice or the basics of Salesforce query optimization and tuning, please make sure to read these other interesting blog posts that provide you with the necessary background.
Identifying Salesforce fields that have an index
Indexes in a database are like the index in a book–they help you find what you are looking for fast. When you’re designing filters for a report, list view, or SOQL query, you can confirm that your filter condition fields have indexes by checking the field lists on an object’s detail page. Notice that the Indexed column clearly indicates whether a field has an index.
You might recall that the Salesforce documentation and the Database Query & Search Optimization Cheat Sheet note standard field indexes and certain types of custom fields that the Salesforce Platform indexes by default (e.g., Master-Detail fields). Apart from these field types, you might also notice other fields with indexes. So how’d these custom indexes get there? There are a couple of ways custom indexes come to exist.
One way that custom indexes get created is by Salesforce’s auto-indexer. The platform constantly analyzes custom index candidates, and when it sees that a custom index would make a noticeable difference in the response time for one or more queries, it automatically creates the index for you. Cool!
Another other way custom indexes appear is even simpler–someone in your org might have proactively logged a case with Salesforce Support requesting a custom index to help the response time for a query. You can request a custom index too (and should) when you are working with large objects and know that an index will help your application perform more efficiently and faster.
So remember, fast scalable reports, list views, and SOQL queries use selective filter conditions with optimizable operators that reference indexed fields. Here are a few related blog posts and articles that you don’t want to miss if you want to learn more about this best practice.
- Maximizing the Performance of Force.com SOQL, Reports, and List Views
- Developing Selective Force.com Queries through the Query Resource Feedback Parameter
- Collecting Selectivity Statistics for Force.com Queries
- Force.com Query Optimizer Secrets You Can Use Today
About the Author
Steve Bobrowski is an Architect Evangelist within the Technical Enablement team of the salesforce.com Customer-Centric Engineering group. The team’s mission is to help customers understand how to implement technically sound Salesforce solutions. Check out all of the resources that this team maintains on the Architect Core Resources page of the Salesforce Developer network.