Well-performing queries are a key component of successful Force.com applications. In the Salesforce1 Platform’s multitenant architecture, the Force.com query optimizer generates the most efficient query for your SOQL, reports, and list views. You probably already know the basics of how it operates: It tries to drive the query with the most efficient index or table, and you can improve overall query performance by adding at least one selective filter to your queries. The questions that remain unanswered by this general advice are much more nuanced and organization specific: What will the impact of adding a second selective filter to my query be? How does my restrictive sharing model affect query performance? For my large data set, would a specific custom index perform better than a standard index?
Although salesforce.com’s documentation about query performance and query tuning has come a long ways over the last few releases, developers are still trying to understand the limits, rules, and optimizations that salesforce.com has built into its platform over the years. Because these developers have had to run their queries to see how these queries perform, they sometimes end up finding out that queries are non-selective only after deploying code to their organizations. When you factor in the platform’s query result caching, which automatically enables the platform to retrieve query result data quickly but doesn’t expose that data to users, you can see that it has been difficult to get objective measurements about query performance—until now.
Validating the Selectivity of Your Queries
With the Spring ’14 release, all Force.com developers have access to an exciting new tool: an “explain” parameter option in the platform’s REST API. John Tan of Technical Enablement and guest author Chris Peterson have written this article, which explains the Query Resource Feedback Parameter pilot and shows you how to use it to retrieve the query execution options that the Force.com query optimizer is considering. The article also gives you a simple Visualforce page and Apex controller to wrap the REST API call.
After reading the article, you should be able to:
- Understand what a selective filter is
- Test your query’s selectivity without running that query
- Determine whether your query contains a selective filter
It’s hard to overstate just how powerful this tool can be when you use it properly. You can make simple, minor changes to an application that cause absolutely massive performance improvements, as some of the examples in the article illustrate. Now, you can quickly and easily confirm that your changes are helping, not hurting, your query performance—without needing to call salesforce.com Customer Support to evaluate your queries.
Related Resources
- Developing Selective Force.com Queries through the Query Resource Feedback Parameter Pilot
- Architect Core Resources
- Database Query & Search Optimization Cheat Sheet
- Get Feedback on Query Performance
- Inside the Force.com Query Optimizer
About the Authors
John Tan 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.com solutions. Check out all of the resources that this team maintains on the Architect Core Resources page of Salesforce Developers.
Chris Peterson is a Force.com MVP and a Senior Developer with FinancialForce.com for its Professional Services Automation application. He has been building ISV applications on the Force.com platform since 2009. He blogs somewhat regularly at http://www.ca-peterson.com.