Result Set Caching
Drillbridge 3.1.0 and later offer a result caching feature. This is a per-report setting that allows for Drillbridge to cache the results of a SQL query. The most common use of this feature is to provide fast performance and paging through large result sets.
For example, consider a drill-through query that returns 100,000 or more rows. Certain browser/version combinations are not adept at showing this many rows of data without very sluggish performance for the client. It may be more practical to show the results as a series of pages, such as 1,000 or 10,000 rows of data at a time. Relational database systems all have different mechanisms for paging through rows, and all offer differing levels of performance. In many cases, it would be impractical to try and rely on the database to page the results as it might require the user to wait for too long in between different pages of data.
For this reason, Drillbridge offers the ability to cache the results of a query. Based on a given report’s page size setting, Drillbridge will break the results of a query up into several pages. Drillbridge then compresses the data into a binary object in order to conserve as much memory as possible. The first page is shown to the user performing the query. If there are more pages, then the user can simply click on the Next button to go to the next page. This data is shown to the user almost instantaneously as Drillbridge does not need to issue a potentially long running query to fetch more data. It consults its "BLOB server for the compressed data, uncompresses it, and then sends it back to the user.
BLOB Servers
A BLOB object is a "binary large object". As it pertains to Drillbridge, the compressed rows of data are turned into a binary object. Additionally, BLOB is a column type in a relational data store. Drillbridge can be configured to store BLOB (temporary result sets) in one of two places: either locally (using memory), or in a relational database. If Drillbridge is configured to store BLOB/cache data in a database, then a connection must be specified and tables must be created for Drillbridge to use.
BLOB Server Configuration
Create a database as you normally would using the tool for your RDBMS. You may wish to create a new user specifically for Drillbridge caching. Then, create tables using the DDL for your database vendor as noted below. There is no initial data that needs to be populated into the tables. Drillbridge will populate the tables as needed as cached results need to be stored.
H2 Database Configuration
` CREATE TABLE DRB_PAGE_CACHE (`
` SESSION_ID int(11) NOT NULL,`
` SERVER_TAG int(11) NOT NULL,`
` PAGE_NUM int(11) NOT NULL,`
` DATA blob NOT NULL,`
` PRIMARY KEY (SESSION_ID, SERVER_TAG, PAGE_NUM)`
` );`
` CREATE TABLE DRB_SESSION_CACHE (`
` SESSION_ID int(11) NOT NULL,`
` SERVER_TAG int(11) NOT NULL,`
` CREATED timestamp NOT NULL,`
` LAST_ACCESSED timestamp NOT NULL,`
` PRIMARY KEY(SESSION_ID, SERVER_TAG)`
` );`
Drillbridge Cached Result Set Setup
After the database/schema has been created/configured to store BLOB results, some configuration changes must then be made to Drillbridge to activate the use of the schema and turn on the caching feature. Note that database configuration does not need to be performed if the "In Memory" result caching feature is used. That said, it is not recommend to use the In Memory option in a production environment.
In order to configure result caching, edit the application.properties file in the /config subfolder of your Drillbridge installation.
Find the following configuration entries:
` dtb.cache.pov.max-entries=10000`
` dtb.cache.pov.expire-minutes=15`
` `
` dtb.blobserver.cleanup-interval=600000`
` dtb.blobserver.cleanup-enabled=true`
` dtb.blobserver.initial-delay=300`
` dtb.blobserver.period=60`
` `
` # Choose INMEM or JDBC or CONN`
` dtb.blobserver.type=INMEM`
` dtb.blobserver.url=jdbc:mysql://diskstation.corp.saxifrages.com/drilluser?noDatetimeStringSync=true`
` dtb.blobserver.username=drilluser`
` dtb.blobserver.password=drillpass`
Note that Drillbridge must be stopped and started in order for changes to the configuration to take effect.