Report Server Cache Execution Time

The following information provides an overview of InetSoft's reporting software, which is flexible enough to allow users to make the needed changes to improve report server execution time. View the example below to learn more about the Style Intelligence solution.

The 'Dataset Caching' option specifies whether query results are cached and shared across reports and/or Viewsheets. If the option is disabled, then the query results are only shared within the same report and/or Viewsheet if multiple elements are bound to the same query.

The 'Data Cache Size' option specifies the number of datasets that can be cached and reused.

The 'Data cache timeout' controls the persistence (ms) of a dataset in the data cache. If the dataset is not used for the specified time period, it is removed from the cache. The default timeout is 30 seconds.

The 'Paging Threshold' setting controls the initiation of dataset paging. If a dataset exceeds the threshold, it is paged to disk in order to conserve memory.

The 'Page/Data Streaming' option allows reports to be processed in parallel with data loading. If the 'Page/Data Streaming' option is checked, the engine begins processing the report when data becomes partially available, resulting in quicker report display. Users may see a partially completed report marked with a “*” in the page total, indicating that there are more pages to come.

The 'Minimum Pages' option controls the initiation of page streaming. For small reports that have fewer pages than the minimum, the streaming will be disabled so that end users do not see a partially generated report.

The 'Clear Cube Cache' button deletes the cached OLAP cube structure. This is useful in the event that the cache becomes corrupted, which might occur if the application server fails to terminate properly. (An error such as “Locking failed due to time out” is a possible symptom of this.)

Several properties are available to control the size of queries returned during preview and runtime operation. These properties provide important safeguards that prevent end-users from erroneously requesting very large amounts of data while designing Worksheets or Ad Hoc reports.

The first group of properties below can be configured from the 'Performance' page in Enterprise Manager.

query.preview.maxrow
query.preview.timeout
query.adhoc.maxrow
query.adhoc.timeout

Other safeguard properties are as follow:

query.runtime.maxrow
query.runtime.timeout
asset.sample.maxrows

What Are Ways to Improve Report Server Execution Time?

Improving report server execution time is crucial for ensuring fast, efficient, and scalable data reporting. Whether you're using a dedicated report server like SQL Server Reporting Services (SSRS), InetSoft's reporting tool, or another solution, there are several strategies to enhance performance. Here's a detailed breakdown:

1. Optimize Data Queries

Since report execution often depends on database queries, optimizing them is one of the most effective ways to improve performance.

Use Indexed Tables

• Ensure tables used in reports have appropriate indexes, particularly on columns used in JOIN, WHERE, and ORDER BY clauses.
• Consider covering indexes to minimize unnecessary reads.

Avoid SELECT*

• Only fetch the necessary columns rather than using SELECT *, which can lead to unnecessary data retrieval.

Optimize Joins and Subqueries

• Reduce the use of complex joins when possible.
• Replace subqueries with joins or Common Table Expressions (CTEs) where it improves efficiency.

Use Stored Procedures

• Instead of embedding complex SQL logic in report queries, use stored procedures, which are compiled and cached by the database for faster execution.

Aggregate Data in SQL, Not in Reports

• Pre-aggregate data using GROUP BY and summary tables instead of performing calculations in the report itself.

Use Partitioning and Filtering

• If dealing with large datasets, use table partitioning to improve query performance.
• Implement report parameters to filter and reduce the amount of data being retrieved.

2. Improve Report Design

Report structure and formatting can significantly impact execution time.

Minimize Data Rendering Complexity

• Avoid excessive conditional formatting, row-level calculations, and dynamic visibility settings.
• Use simple table structures instead of overly complex nested or matrix reports.

Use Pre-Processed Datasets

• Consider caching frequently used datasets to avoid redundant database queries.
• Use materialized views if the database supports them.

Reduce Number of Expressions and Calculated Fields

• Perform calculations at the database level where possible.
• Avoid using unnecessary expressions in report cells.

Optimize Charts and Graphs

• Limit the number of data points used in charts.
• Use summarized data instead of raw transaction-level data.

3. Leverage Caching and Report Snapshots

Caching can drastically reduce report execution time by avoiding repeated data retrieval.

Enable Report Caching

• Store report execution results for commonly accessed reports to reduce the need for frequent database queries.

Use Report Snapshots

• Generate and store report snapshots at scheduled intervals instead of processing the report in real time for every request.

Pre-Load Data for Frequently Accessed Reports

• If certain reports are accessed frequently, pre-load their data using scheduled jobs.

4. Optimize Report Server Configuration

Server-side settings and configurations can have a major impact on report execution.

Increase Report Server Memory Allocation

• If the server is constrained by RAM, allocate more memory to the reporting service.
• Ensure sufficient memory is available to handle report processing.

Scale Out the Report Server

• If your reporting workload is high, consider a scale-out deployment with multiple reporting servers handling different workloads.

Tune Report Server Execution Timeout

• Adjust execution timeout settings to prevent long-running queries from consuming excessive resources.

Optimize Parallel Processing

• If the report server supports parallel query execution, enable it to process multiple datasets concurrently.

5. Use a Data Mashup Tool

If your reports pull data from multiple sources, using a data mashup tool like InetSoft can significantly enhance performance.

How InetSoft's Data Mashup Helps:

• Pre-aggregates data from multiple sources, reducing the need for on-the-fly transformations.
• Combines data efficiently using in-memory processing, improving response time.
• Allows for smart caching, reducing repetitive queries to slow data sources.
• Enables lightweight transformations, minimizing complex logic in the report server.