When we run reports, there are a few reports that take a long time to execute. To make it faster we can improve our report performance by following these steps:
Prompt Page:
We have to ensure that the parameter definition matches the parameter usage. Parameters are defined in filters and calculations. The resulting parameters are used in prompts.
Some prompts don’t handle large volumes of data well. For example, a value prompt that picks 100000 entries will be slow and unusable. For this type of scenario we can use Select and Search Prompts, Cascading prompts.
We can use a Select and Search prompt instead of a Value Prompt if the list of values are very long. A long value list could be the reason for slow performance.
Also if we have the option of choosing Product Code and Product Type to use as a prompt, always choose ‘use value’ because a Product Code is always better option to improve performance.
Performance Affected due to heavy usage of Fonts:
We can avoid usage of heavy fonts. Always use fonts like Arial.
Filters:
We can always use a date filter from the Fact table to improve the performance. Hence it will directly hit the partition.
Complex Calculations and Filters always use in Frame Work Manager to improve report performance.
Various Options in the query properties for improving query performance:
Database only, Limited Local
Specifies whether the query engine will pick up a minimal amount of processing. Local Processing will only occur if the database cannot handle the load.
Maximum Tables:
Specifies the maximum the number of tables that the query can retrieve. If the value 0 is specified, no error is returned and there is no restriction on the number of tables that can be queried.
Maximum Rows Retrieved:
Specifies the maximum number of database rows the query can retrieve. An error occurrs if the number of database rows returned exceeds the specified values.
Use Local Cache:
IF set to Yes, the query engine can reuse existing SQL results. IF set to No, the cached results will not be used. The query will be executed. Improve performance by reusing cache data when running a report.
Execution Method:
Specifies whether the query is allowed to run concurrently or sequentially. Concurrent Query execution can improve performance in some cases.
Use for Parameter Info:
Prompt Query with this property set to Yes will always check parameter information first. Setting this property to Yes on parameterized queries can improve performance in displaying prompt pages.
