Shulou(Shulou.com)06/03 Report--

It is an indisputable fact that using caching can improve the performance of reports. Generally, high-end report tools provide report caching, which caches the calculation results of the entire report in the file system, so that users can quickly read the cached results and display them the next time they visit a report with the same parameters. However, in some cases, report developers also want to have more accurate and flexible control over the cached content, such as caching not the whole report result but a part of it, and the cached content can be reused by other reports or programs. and set different timeouts for different cached results, so as to deal with different situations of data volume and real-time performance. At this point, the general report cache can not meet the demand.

When used in conjunction with reports, aggregators can help developers flexibly control cached content. Here we refer to the report cache content that developers can flexibly control using the aggregator as a controllable cache. Controllable cache can bring more flexibility and benefits, and fully solve the report performance problems in practical applications. Let's discuss the above-mentioned three aspects: partial caching, cache reuse and setting different timeouts.

Partial caching

In report development, it is sometimes not desirable to cache all report results, which avoids excessive cache costs (disk space and application server resource overhead). In addition, when part of the data in the report requires high real-time performance and needs to interact with the database for data query in real time, then this part of data is not suitable for caching.

The intermediate results that do not change frequently can be cached through the controllable cache of the collector. when the report is requested again, the data with high real-time requirements is still read from the database in real time, and the report is calculated with the non-real-time data in the cache. get the final report result set.

The conventional caching scheme does not have the function of caching some results, so it can only set whether the whole report is cached or not, so that the report will contradict when it involves different timeliness data, and the controllable cache implemented by the aggregator is obviously more flexible and more efficient.

Give an example

The data in the order (Orders) data will not change for more than 3 months (cold data).

The data structure is as follows:

Now inquire about the order details of the past 6 months and summarize the monthly order quantity and amount.

The sample report is as follows:

Although the implementation of retrieving data directly from the database order table for 6 months is the simplest, it is obviously not very efficient to retrieve all the data from the database for each query. The data that remains unchanged for more than 3 months can be cached during the initial query, but the data within 3 months can still be read from the database, so that when you check it later, you can directly read the cache and database data to speed up the report performance, which can significantly reduce the database computing time and JDBC transmission time.

The aggregator will judge based on the query month:

If the historical data is queried for the first time to query the database and write to the cache, read the cache directly later

If the query is real-time data, it is read from the database every time.

If the queried data contains historical data and real-time data, the historical data is written and cached, and the real-time data is read into the database.

Aggregator implementation parameters

The query parameter is the start and end month (the first day of each month). In the implementation, the date filtering data are processed by the start and end parameters.

Data preparation of aggregator

ABC1=filePath= "/ usr/report/cache/" / cache directory

2SecretreportName = "orders_customer_month" / report name, cached using report name + parameter naming

3=elapse@m (pdate@m (now ()),-3) / date three months ago

4historical hisdate = [end,A3] .min () / Historical data date end value

5=pdate@me (end) / query date end value

6roomsql = "select company name customer, order ID, order date, order amount from order, customer where order. Customer ID= customer. Customer ID and ordering date > =? and ordering date

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.