Report Desk: Record Thresholds
Release date: 4/28/23
Version: 8.6
Background
Report Desk reports can be CPU intensive and potentially run for a long time, depending on the complexity of the report, the output format and the number of records involved. Accordingly, when a user runs a User Defined Report (UDR), the system will first build and execute the SELECT statement for the report, to count the number of records involved and to compare that number to the thresholds for each output format. If the number of records exceeds the Warning Limit for the selected output format, a dialog similar to the following will appear:
The bold text at the top contains the number of records found and a message describing how it compares to its Warning or Stop limit.
The table in the middle of the dialog contains the following:
- Warning and Stop Limits for each output format.
- Checkboxes which can be used to select the desired output format if the user choses to continue. The checkboxes are mutually exclusive.
- When the record count exceeds the Stop Limit for any format, that line will be disabled.
At the bottom are buttons for either continuing with or cancelling the report. If the record count exceeds all Stop Limits, only the Cancel Report button will be enabled.
Default Thresholds
Default threshold numbers were determined by testing several reports and noting the run times and amount of memory used. Generally, we set warning threshold defaults for reports that could take more than 10 minutes or use more than 8 GB of memory. The assumption is that you have at least 8 GB of RAM in your workstation but 16 GB RAM is preferred if you wish to use Report Desk. The maximum record defaults are set by doubling the warning level but may run significantly longer if you are out of memory. Of course, the number of columns in your reports can also affect the performance. We came up with the threshold values by assuming an average 14-column report. These threshold values are only approximations and can be customized. Here are the defaults:
Output Warning Maximum
Format Threshold Records
----------- ------------- ---------------
CSV 500,000 1,000,000
PDF 250,000 500,000
XLSX 500,000 1,000,000
Changing Default Thresholds
The default thresholds can be changed by running the <ElliottRoot>\Bin86\EL860CF.EXE utility. In the Report Desk tab, you can change the Record Thresholds:
If you login to Elliott 8.6 as SUPERVISOR, you can also run this configuration utility on the top left corner of your Control Center toolbar. Alternatively, you could modify these entries in <ElliottRoot>\Bin86\EL860.CFG:
You may not see these entries in the EL860.CFG file if you are using default values.
Sample Timings
The default threshold values are somewhat arbitrary. Below is a table that depicts the timings of a few reports of various record counts, field (column) counts and output formats. The elapsed times are in minutes.seconds. The maximum memory numbers are in megabytes.
Observe the following:
- The CSV and XLSX timings generally are more than twice as fast as the PDF timings.
- The CSV and XLSX maximum memory values generally are less than half the PDF values.
- The number of fields in the reports greatly affect the timings and maximum memory values.
- In our test cases, it is assumed that the execution time of the PSQL server is not a significant part of the overall performance. The Report Desk performance is mostly determined by the client side. Our testing workstation has 16 GB of RAM and 4 CPU.
- The values in red are not final numbers for those reports -- The system was running at 95% or more of available memory for many minutes before the reports were cancelled by the user. In these scenarios, it is likely we can get faster performance if we increase the RAM of the workstation.
Conclusions
For analysis purposes, we suggest that your output should be CSV or XLSX. PDF is not as suitable for analysis purposes and also runs more slowly. PDF should be chosen if a nicely formatted report is desired.
Modified programs: EL860RW, ELRW_SYRPTUDRJEG