Why are the ROQL queries involving Incidents table taking too long to execute or running into the error: “Poor performing query – too many rows examined”
ROQL, Oracle B2C Service 20B and above.
After upgrading from an Oracle B2C Service Release 20A or below to an Oracle B2C Service Release 20B or above, the ROQL query I used no longer worked. The query takes much longer or returns a “Poor performing query – too many rows examined”.
Optimize the query with the following:
- Include filters to reduce the data set returned.
- Examine if the queries use the right indices; if not, make sure to optimize the query by including the right indices.
- If the queries involve OR conditions in the WHERE clause, break out the OR clauses into multiple separate ROQL queries (each using a key), instead of one large combined query and then process the results in the code.
- If the queries involve selecting max(ID) from incidents table, modify the query to add the filter and do a binary search for the max ID. i.e. adding a WHERE clause and halving the ID space with each query to arrive at the result in as few queries as possible.
Beginning in the 20B release, the underlying incidents table is split into two tables: one holding the standard incident fields and the other holding the custom fields/attributes of incidents. While these underlying changes result in better scanned row estimates, it may result in queries being prohibited from executing that previously were incorrectly allowed to execute. Such queries might take longer to execute or run into max join size error and will need to be corrected.