The webinar,
Inside the Force.com Query Optimizer delivered in April, 2013 is still featured as a relevant resource on the
Architect Core Resources page. While the webinar explains many key considerations for designing queries, many questions linger. Let's take a closer look at these open questions, and let me know if you have any of your own to add!
Why does query optimization affect me as an admin? I don't write code.
If I were a betting man, I would bet that under the hood, SOQL queries, reports, list views and related lists on detail pages all tap into the same query execution framework. So, if you manage page layouts, reports and/or list views, you should care about the Force.com Query Optimizer.
What is a selective query?
A
selective query is a query that leverages indexes in filters to avoid full table scans and to reduce the number of records in your result set below the selectivity threshold.
Stupid question, but what is an index?
This is a great question. Simply put, an
index is a field-based mechanism by which a query can execute significantly faster, compared to execution without the index. Salesforce technical architects don't need to know how an index actually works behind the scenes, much like office workers don't need to know how a Keurig machine makes coffee at the press of a button.
A technical architect probably just needs to know that fields are either indexed or not indexed, and that indexed fields should be used in query filters. The technical architect should probably also know off-hand what
standard indexes are available, and that custom indexes have lower selectivity thresholds and can only be
created by Salesforce Support.
Anyone hardcore enough to dig into the Oracle database-level index machinery may want to check out the
Database Systems course, offered gratis through MIT OpenCourseWare.
What's considered a standard index?
What is the selectivity threshold? And why should I care?
The
selectivity threshold is the maximum number of records that can be returned in a result set, without disqualifying the index-based optimization option for a query. See the
Query & Search Optimization Cheat Sheet for the exact calculations used with standard indexes and custom indexes.
Is there a difference between using nested queries vs. separate queries?
This was the first question asked on the
Inside the Force.com Query Optimizer webinar during Q&A, "Is using nested queries good practice?" But I don't think the answer fully addressed the question. My personal guess (which needs to be validated in an org that actually contains large data volumes) is that
nested queries have negligible impact on the execution time of a query, as long as the queries are not constructed in a way that uses the
NOT operator.
The basis for my conjecture is a best guess that nested queries are executed sequentially, following an order of operations that allows the result set from one query to be used in another query. How true is this? I suppose I'll need to test all of the following query structures with selective filters applied:
More importantly, however, it's worth noting that in some instances executing separate queries in Apex is unfeasible, and
the only viable alternative is to use nested queries. I could be wrong (and please correct me if I am), but one such situation could be an Apex method that needs to iterate through a result set using a
for loop, processing child records for each record returned.
