How can a report that returns only a few rows of data, display a "Report processes too much data" warning?

Environment:

Reports, all sites.

Issue:

A report which returns only a few rows of data, displays a "Report processes too much data" warning.

Resolution:

A report is limited not by the number of rows returned, but rather, by how many rows must be scanned in order to find the rows returned.

To make up a simple example from the imaginary tables dragons, and dragon_family_trees: assume that neither of these tables are indexed, and that each of them have 100 rows of data.

If we built a report and wanted to find the records for all dragons named Smaug, the report would have to scan every single row of the dragons table, as it is unindexed. The report would not be able to stop once it found a row with that name as it is possible that there is more than a single dragon with that name. This is referred to as a full table scan.

This would result in 100 rows scanned, but only 1 row returned.

Now, if we were to add to that report another column, such as the dragon's children, we would need to join the dragons table with the dragon_family_trees table. Depending on the join conditions, this can cause the rows scanned to explode. This process is multiplicative rather than additive. For each row in the first table, all rows in the second table must be scanned.

Even if there was still only 1 row returned, the report would have to scan 10,000 rows of data to find that one result.

This is in a report with only two tables, and each of them having only 100 rows. As soon as you start adding additional tables, the numbers can get very large, very quickly, especially if the tables are unindexed, or use indexes with low cardinality. (Cardinality refers to the uniqueness of a column.)

Note that a great deal of this searching can be alleviated by indexing a table well. See Answer 8618: Creating Indexes on a custom object to learn more about indexing. The Report Analyzer (Answer 2488: Using the report analyzer with custom reports) can be helpful when you encounter too much data in a report. Be aware that it returns approximate numbers only. The analyzer cannot return the exact number of rows because this would require it to run the entire query (which would defeat the purpose of analyzing the report before running it.

Answer 4278: Report query processes too much data provides further information on reducing the size of custom reports.