Why am I receiving the exception 'Poor performing query - too many rows examined' in an API call response?
Connect Web Services for SOAP (CWS) API, REST API, Connect for PHP API, ROQL, All product versions
When ROQL query is run from one of the Oracle B2C Service public APIs runs an estimate of database table rows scanned by the query is performed. This estimate is in place as a safety check to protect your database and ensure consistent and reliable response time from the database. This estimate is an estimate of the number of rows it must scan, not the number of rows returned.
If the pre-scan is determined to be too large then you will receive an exception indicating 'Poor performing query - too many rows examined'. There are different thresholds (also known in more technical terms as the max join size) for different databases. The amount that is allowed by the pre-scan when a query is run from one of the public APIs is as follows:
Operational database - 2,000,000
Replication server - 5,000,000
Note: These are mandatory settings and cannot be changed.
Requests using ROQL can be configured to go to either the Operational database or replication server. The replication server is an exact copy of your operational database but it is designed to handle queries (including those used by reports) that are too large to run off the operational database. Because of this there are times when it may fall behind.
If you encounter such an error when executing your ROQL queries we suggest you optimize your query before running the query again. Adding a WHERE clause can help or add additional filters to the WHERE clause. Including LIMIT to the query does not affect the pre-scan as it will only constrain the number of rows returned in the output.
Database indexes can help also. Custom field indexes can be added from the console and indexes on standard fields can be analyzed and implemented with an SR to Technical Support used to engage Cloud Engineering resources.
For further information on the process used to add indexes to standard fields refer to Answer ID 8702: Working with Technical Support to analyze for and implement database indexes.
For further information refer to Answer ID 5169: Technical Documentation and Sample Code.