How do you design high-performance reports?

i-net Clear Reports
1

How do you design high-performance reports?

To design reports for optimal performance, the following 4 points must be considered:

  • Database query
  • Render report
  • Writing output
  • Configuration

Optimizing the database query

  • Less is faster: Reduction of tables and joins, use of a single data source and limitation of data records.
  • Joins should be selected so that the database can use indexes.
  • When filtering the data records, the expressions should be selected so that they can always be executed on the database and the database can use an index for this. Downloading unnecessary data records and filtering within i-net Clear-Reports requires a lot of traffic and time. Also, if the database has to check the condition for each record individually, this is also very slow.
  • Performance optimization starts with the modeling of the data model. Queries for subsequent reports must be taken into account here.
  • SQL-Commands can be faster than the generic SQL of i-net Clear-Reports if an optimized SQL is created. However, no “Record Selection Filter” should then be used and no additional tables should be added via join. An SQL command is a black box and is sent directly to the database. Any further manipulation within i-net Clear-Reports is then only carried out on the data records received. As a result, much more data is transferred from the database than would be necessary. If possible, a view should therefore be created in the database rather than using a command. Then the “Record Selection Filter” or other table joins can be executed on the database.

Optimization of the report rendering

  • Less is more: Reduction of fields, labels and data records.
  • Avoid “Keep Together” and “Print at Bottom of Page” to minimize expensive rollbacks.
  • Minimize the number of font variants (font name, size, style) to reduce the calculation of font metrics.
  • Conversion of complex formulas into Java code and use of expander functions.
  • A split into main report and subreport instead of a join between tables if the fields in the detail area are not required in the headers. With a join, all columns for all data records must be transferred from the database.

Optimization of the data output

  • The smaller the generated output, the faster. “Suppress if Duplicated” could avoid many repetitions.
  • The savings are particularly significant if the same images only need to be output once, e.g. a logo in the page header from the database. Two images with the same “Image Key” are only written once in the output, provided the format allows this.
  • Limitation of the number of embedded fonts.

Configuration

The performance for the productive user can also be improved with some configuration settings. For example:

  • If the structure of the database (tables/columns) no longer changes, the “Meta Data Cache” can speed things up. Without this flag, this is queried again and again for each report.
  • Specify as few fonts as possible for font embedding.
  • Deactivate compression. However, this is at the expense of bandwidth.