Dataset Cache

1) FineReport provides a function to cache datasets. The idea is to save the results of database query locally and they can be reused when the same dataset is used. This function reduce the repetitive work to query the database if the SQL query statement is the same, and the speed to load data can be improved.

2) Both cpt files and frm files support the function of cache.

3) The logic of cache:

a) Consider there are multiple templates and each of them has a dataset called ds1. If the SQL statement and dataset parameters of ds1 in each template are the same, the templates will use the same cache file to reuse the result of ds1.

b) Consider there are multiple templates and each of them has a dataset called ds1. If the SQL statement and dataset parameters of ds1 in each template are different, the templates will use different cache files to reuse the result of ds1.

4) The location to store cached datasets: you can choose to cache the datasets to memory or to disk depending on the volume of data.

1. Create a new DB Query dataset

1) Create dataset [ds1].

2) Use the following SQL statement.

SELECT Classno,StudentNO,Name,Sex,Course,GradeFROM STSCOREorder by Grade,Classno ASC

2. Cache to memory

1) Method: Check [Shared Dataset] and choose [Save All Records in Memory]

2) Result of the setting: After executing the query, the query results will be cached to memory. The next time this dataset is used, templates will directly read the result from the memory.

Note

• Pros & cons: The space of memory is limited. However, the speed at which the data are loaded is very fast.

• Usage scenarios: This setting is frequently used when the data volume is not too large.

3. Cache to disk

1) Method: Check [Shared Dataset] and choose [Cache to disk when records more than]. Enter the number of rows as 100.

2) Result of the setting: After executing the query, results are stored in the server's disk. By default, all cache files are located in C:\Users\Username\.FineReport100\cache (in a Windows system).

Note

• After setting cache to disk, you may find out that there is no folder named cache. Don't worry, this folder will be created after executing the query and there are data to be stored in disk during this process.

3) Result of the setting: 

• If you enter 0 as the number of rows: All queried data are stored in disk.

• If you enter 100 as the number of rows: The first 100 rows of data are stored in memory while the rest are stored in disk.

Note

• Pros & cons: The space of disk is large so there is no limit on the volume of data. However, the speed at which the data are loaded can be rather slow.

• Usage scenarios: For reports that require large volume of data, you can cache the data in the first N pages to memory (since these pages are frequently viewed) while cache the rest to disk.

4. Set the attributes of cache

1) The administrator login the decision-making platform and go to [Manage] > [System] > [Cache].

2) Meaning of the options: 

a) Max Datasets No.: The maximum number of datasets that can be cached. If this limit is exceeded, the server will remove cache files based on Cache Principle.

b) Cache Principle

• Recently Used: Remove the cache files that are not used for the longest time.

• The Least Used: Remove the cache files that are used for the least time.

• First In First Out: Remove the cache files that are generated the most early.

c) Max Idle Time: The time interval between the last time the cache is accessed and the time the cache is removed.

d) Max Time to Live: The time interval between the time the cache is created and the time the cache is removed.