Why am I receiving an alert message that my report processes too much data?

Environment:

Analytics

Issue:

Sometimes when I generate a report, I get a pop-up message indicating that the query processes too much data.

Unable to Process Report
This query processes too much data. You can reduce the amount of data processed by refining your existing search criteria and/or adding new search criteria to the report definition.

Resolution:

If you receive this message, you should reduce the size of the data set being queried. For more information on possible modifications to your report, refer to Answer ID 2380: Improving performance of reports. You can also use the Analyzer button to predict report performance. See below for more information on this option.

Borderline reports: It is possible for a report to sporadically give the Query processes too much data message. That is, sometimes the report will generate results, and other times the processing message will display.

A report that is close to pulling too much data can be on the threshold for months or years with no problem. Then, one day there may be 1 or 2 more entries in the tables which exceeds the threshold and generates the message until the amount of data decreases again.

That is, even though the report has not changed, the size of the database tables are continually increasing in size, including the incidents, contacts, organization, answers and transaction tables. As a result, the report query takes longer to run while processing against larger tables than it did in the past.

Cause:

When a report first runs, the first thing it does is get an estimate of the amount of data it will need to process all the data it must scan through in order return the actual results. This pre-scan estimate is in place as a safety check to protect your database and ensure consistent and reliable response time from the database.  Also in order to find the data that you are looking for, the database must scan more rows than are returned.

For example, when looking up a name in a phone book, you don't read the name you want first, you open the phone book to the appropriate part and then start spot checking names to see if you need to jump forward or backward.  The database works in a similar manner, and 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 informational message that the report processed too much data and that you must add more filters to reduce the scan of the report so that it can run.

Database row thresholds determine the maximum number of database rows that can be analyzed to return a result set that will be displayed in a report. There are different thresholds (also known as the max join size) for different databases. The same thresholds are used when a report is run from any part of the product, including the end-user pages in customer portal and via one of the Connect APIs. The amount that is allowed by the pre-scan when a report is run from the Reports Explorer is determined by three possible settings. If the report is on the operational database or reporting database and/or if the report is deferred.

Deferred - 200,000
Operation database - 2,000,000
Reporting database - 5,000,000

Note: These are mandatory settings and cannot be changed.

Analyze tool:  An estimate of these scans can be seen by opening the report for edit and selecting the Analyze button from the Home ribbon.  Refer to Answer ID 2844: Using the report analyzer with custom reports for information on this option.  With the analyze option, you may also see some recommendations on things that can be done to improve a report's performance.

Data Source: The database that a report uses can be changed by opening the report for edit and selecting Options > More Options, from the Home ribbon.

Reporting Database : The reporting database is an exact copy of your operational database but it is designed to handle reports that are too large to run off the operational database. Because of this, there are times when it may fall behind. As an example, the reporting database should be used for large historical reports, such as Incidents by Product for past 3 months, which is a report of all the incidents that an agent had worked in the past 3 months.

Operational database: We recommend the Operational database be used if you need up to the minute data on a small report. For example, My incidents or My Inbox, which are reports that show agents all of their current unresolved incidents.