Several considerations can be taken into account in relation to performance, particularly with reference to Data source. Any one or all of the following steps can be taken to ensure strong and fast application performance and a stable structure:

• Delete Tables and Fields unused by the Query wizard.

• Test your report with real and sufficient data.

• Check your table relations so that you do not get unnecessary data/structure.

• Empty ranges or no ranges often cause slow performance as all the records can get fetched.

• Add a field to an index. Fields that do not belong to any index can cause slow performance because a full table scan is used to find the related record(s). It is recommended to add a field to an index to avoid a potential full table scan and ensure performance.

• If a Data source has multiple child data sources at the same level with Fetch mode 1:1 where an inner join is required, consider using a subquery. It is possible to avoid performance issues and optimize the structure by adding subqueries to the existing Query (main Query).

A new option for disabling as well as re-enabling the data sources has been introduced within the Query wizard. It may be useful when optimizing performance and locating data sources (tables) causing no output.

To solve the performance issues using performance indicators, follow these steps:

1. Activate Performance indicators when optimizing performance for a report.

Info on performance indicators here: Performance - Formpipe Support Portal

2. Run the report to a destination (not Lasernet screen). It is recommended that you use to use either email or fax.

For example, use fax as the destination and 1234 as the print number.

3. Locate the part causing slow performance and disable data source(s) within this part of the Query (for instance, disabling half of the data sources).

4. Re-run the report and re-check the infolog in relation to the part of the structure which was causing slow performance.

5. Check the table used for the relations and indexes for the tables, causing a slow performance.

Data Source and Size Limits

SQL Server has a limit row size of 8060; if the size exceeds the limit, the following error is displayed:

The error occurs because you cannot have a row in SQL server larger than 8KB (the size of one page) because rows are not allowed to span pages – it is a basic limit of SQL Server. For more information, see the following:

docs.microsoft.com/.../pages-and-extents-architecture-guide. 

Data contained for all fields within an entity are contained in an SQL table with a maximum storage of 8060 bytes.

See documentation from MS:

https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?redirectedfrom=MSDN&view=sql-server-ver15

For more information, see the following links:

Error message: 1:N Fetch Mode is not allowed on Datasource having join condition with node other than its immediate parent

Solution

Error message: "Customer#, Invoice# was not delivered because of a print management setup issue" and "SQL server cannot create a row...size of 8060".

How Does the Data Get Selected? 

You can see the infolog with the SQL Query in the performance log (if enabled). Selecting the Full value in the Performance indicators drop-down combo box allows you to add a query statement (SQL statement) to the Infolog once a report is executed.

Grouping, Fetch mode (1:1/1:N) affects how the SQL Query is executed.

Fetch mode / Fetch 1:1 are creating the following SQL statement:

Fetch mode / Fetch 1:N are creating the following multiple and separate SQL statements: