Improving Ad Hoc Report design performance and Domain Design considerations

Introduction

Typically when wanting to improve ad hoc performance, we consider the domain itself and figure out ways to optimize its structure, volume, or security. There are other strategies we can try that fall outside fixing the domain itself.

Domain Optimizations

By analyzing the actual SQL queries that the domain generates, we can often get a sense of what needs to be optimized. A slow-running query can often be optimized by adding indexes to the database, along with other database-specific strategies.

Filter Issues

When creating filters, the queries should ideally hit the dimension tables and not the fact tables; the fact tables are usually much, much larger, and can really slow down filter creation.

By analyzing a domain, we can see where the table joins are being made; the display values should reference the values from the dimension tables, where appropriate. This may need some re-factoring of the domain definition.

A key goal here is that it if there are existing ad hoc reports, that the new domain definition doesn't break any existing ad hoc reports.

Two Options

There are two other options to think about to get better ad hoc performance. These options are outside what we typically do with domains, in that our typical strategies focus on tuning the domain itself along with the underlying database. As a recap, those strategies include:

• Limiting the size of the domain - applying filters to the data
• Denormalizing the database somewhat, to simplify the domain definition - this speeds up the domain's performance
• Optimizing database lookups by adding indexes

The other two options fall outside the sphere of domains, but would accomplish the goal of getting better performance.

Option 1 : Using Topics Instead

Topics are a lot simpler than domains. You can create a topic by simply creating a report that you then save in the topics folder. The user would then create an ad hoc report by choosing the "Topics" link that pops up, where it would display the topic you saved there.

The advantage of this approach is that since a topic has an SQL-based report behind the scenes, you can tune and optimize the SQL request to whatever degree you want, in whatever way is appropriate for the situation, without having the server have to go through the extra step of processing the domain's request before turning it into SQL (SQL that's not necessarily optimal). The result of this would be faster times at the report design stage.

Since topics are based on a single SQL query, that means they would contain less data than you would find in a domain, so you could conceivably separate the "Assignment Data" set of your Analytics domain into a single (but complex) query; you would have to see if that makes sense in your case.

The disadvantage is that you would have to find other ways than using the domain's security file to impose row- and column-level security; you would have to incorporate that into the topic query itself. That means that you would have to have access to a user's profile information, which is available to you in iReport via a special variable.

Option 2 : Using one environment for ad hoc report design, and another for its execution

In this approach, you have the user go through the report design process in an environment that has the same exact domain definition but less data in the database. The advantage of this is that the report design stage goes a lot faster (since you control the volume of data in the database). The challenge then becomes how to take that ad hoc report and have it point to the real database. There's some options in here, but they would involve customizing the application somewhat; you wouldn't be modifying the way ad hoc itself behaves but rather what datasource the ad hoc report points to after it gets saved to the repository.  

• Note on Option 2: Since Jaspersoft 4.7 a feature was introduced to allow limiting data at design time ("Sample Data") and issuing a "LIMIT BY" style clause. You can read more here.