Ad Hoc View Filter Values Caching Questions

Use Case

Customer asked for a couple of questions related to caching Ad Hoc View Filter values:

1. Is Ad Hoc View filter value is cached?
2. When we perform the filter within the Adhoc View, is the filter applied into the cached data or directly retrieve data from the data source?

Answers

A1. Yes, values of Ad Hoc View Filter are cached

A2. The cache is populated by the data that results from queries when creating or running Ad Hoc views.

The datasets are uniquely identified by a key that references the query itself, the data source URI, and parameters used when the query was issued.

Then you open an Ad Hoc View with filters for the first time, Ad Hoc generates SQLs for all filters (to select distinct values etc) as well as the main query to select data for the view.

This main query includes preselected filter values, like

select "freight", "orderdate", "shipcity", "shipcountry"

from "public"."orders"

where ("shipcountry" in ('Argentina', 'Austria', 'Belgium'))

order by "shipcountry"

As the view opened for the first time (or after the server restart or manual cache clearing), the returned dataset are cached.

When you open the same Ad Hoc View for the second time, the engine identifies that the cached dataset already exists and use it instead of performing request to the database.

Now, if you change the filters values and click on Apply, a new SQL is generated, for example, you add one more country from filter:

select "freight", "orderdate", "shipcity", "shipcountry"

from "public"."orders"

where ("shipcountry" in ('Argentina', 'Austria', 'Belgium','Brazil'))

order by "shipcountry"

Ad Hoc checks if there is existing cached data with the same(!) SQL. As it is not, a request is made to database and the returned dataset is cached.

If you try to apply the same filter values next time, cached data will be used.

JasperReports Server has a user interface to monitor Ad Hoc cache details:

- login as superuser

- go to Manage > Server Settings > Ad Hoc Cache menu

AS-20190823, case #01673185