Configuring the Query Results Cache

This topic describes how to manage caching for stored queries in BEA Liquid Data for WebLogic^TM. It contains the following sections:

• Understanding Results Caching
• Setting up the Results Cache Database
• Enabling the Results Cache
• Configuring Results Caching for Stored Queries

Notes: Caching is used with stored queries only. Caching does not apply to ad hoc queries, which are never cached.

 

---

Understanding Results Caching

After the Query Processor executes a query, it returns to the client the data that resulted from query execution. If Liquid Data results caching is enabled, the first time a query is run, Liquid Data saves its results into a query results cache. The next time the query is run with the same parameters, Liquid Data checks the cache configuration and, if the results have not expired, quickly retrieves the results from the cache rather than re-running the query. In this way, for queries that are executed repeatedly with the same parameters, using the results query cache reduces processing time and enhances overall system performance.

The query results cache is disabled by default and must be enabled according to the instructions in Enabling the Results Cache. Once enabled, you can configure the cache for individual stored queries as needed, specifying how long query results are stored in the cache before they expire (time out), and explicitly flushing the query cache. For more information, see Configuring Results Caching for Stored Queries.

In general, the results cache should be periodically refreshed to reflect data changes in the underlying data stores. The more dynamic the underlying data, the more frequently the cache should be set to expire. For queries on static data, you can configure the results cache so that it never expires.

If the cache policy expires for a particular query, Liquid Data flushes the cache result automatically on the next invocation.

In the event of a Liquid Data Server shutdown, the contents of the results cache are retained. Upon server restart, the Liquid Data Server resumes caching as before. Upon the first invocation of a cached query, the Liquid Data Server checks the results cache to determine whether the cached results for this query are valid or have expired, and then proceeds accordingly.

Note: The query results cache is stored in a database that you must explicitly configure. For more information, see Setting up the Results Cache Database.

 

---

Setting up the Results Cache Database

To use results caching, you must first set up the results cache database. To set up the results cache database you need to do the following:

• Step 1: Install and Configure the Database Server
• Step 2: Run the SQL Script to Create the Cache Database
• Step 3: Create the JDBC Data Source for the Cache Database

Step 1: Install and Configure the Database Server

To use results caching, you first need to install and configure the database server according to your vendor's instructions. Liquid Data supports the following relational databases for caching:

• Oracle
• Microsoft SQL Server

Step 2: Run the SQL Script to Create the Cache Database

After you have installed and configured a database server, you need to run a SQL script on your database server that creates the Liquid Data cache database. Liquid Data provides the following scripts (in %WL_HOME%\liquiddata\server\dbscripts):

Table 19-1 Cache Database Creation Scripts  

Database	Script File Name
Oracle	ldcache_oracle.ddl
Microsoft SQL Server	ldcache_mssqlserver.ddl

 

Step 3: Create the JDBC Data Source for the Cache Database

After you have created the Liquid Data cache database, you need to create a JDBC data source in WebLogic Server that points to the Liquid Data cache database. Using the Administration Console, create a JDBC data source named ldCacheDS according to the instructions for Creating a JDBC Data Source.

Note: You must use the exact data source name, ldCacheDS, for caching to work.

Once created, you can enable the result cache, as described in the following section, Enabling the Results Cache

 

---

Enabling the Results Cache

By default, results caching is disabled in Liquid Data. To use results caching, you must explicitly enable it in the General tab on the Liquid Data node in the Administration Console. Before you enable caching, make sure that you have set up the Liquid Data cache database as described in Setting up the Results Cache Database.

Note: If caching is enabled but the cache data source has not been configured, an exception is thrown in the Administration Console.

To enable result caching in Liquid Data:

1. In the left pane of the Administration Console, click the Liquid Data node.

2. In the right pane, click the Configuration tab.

3. Click the General tab.

4. On the General tab, select (check) Cache Results.

5. Click Apply.

Once caching is enabled, you must explicitly configure the results cache according to the instructions in the following section, Configuring Results Caching for Stored Queries.

 

---

Configuring Results Caching for Stored Queries

Each stored query has its own cache policy that determines how Liquid Data manages results caching for that stored query. This topic describes how to configure the cache policy for stored queries. It includes the following sections:

• Creating the Cache Policy
• Editing the Cache Policy
• Removing the Cache Policy

Creating the Cache Policy

To configure the results cache for a stored query initially:

1. Navigate to the Repository tab on the Liquid Data node, as described in Navigating to the Repository Tab.

2. On the Repository tab, click the stored_queries folder.

   The Administration Console displays a list of stored queries in the server repository.

3. Select the stored query for which you want to configure caching.

4. Click Cache.

   The Administration Console displays the Configure Cache tab.

5. Enter the information described in the following table.

   Table 19-2 Configure Cache Settings  

   Permission	Task
   Query Name	Name of the selected stored query.
   Never Expires	If selected (checked), the results cache for the selected stored query never expires. If cleared (not checked), you can configure an expiration time in the Expires After fields.
   Expires After	Expiration time, which is calculated from when the query cache is first created. Days—Number of days, up to 999 days. Hours—Number of hours, up to 99 hours. Minutes—Number of minutes, up to 99 minutes. If Days, Hours, and Minutes are all set to zero (0), then the results cache never expires.

   
    

   Note: The expiration time should reflect the degree to which the data in the underlying data source(s) is expected to change. In general, for more dynamic data (such as real-time data feeds), specify a shorter expiration time. For more static data (such as general product or personnel information), specify a longer expiration time.

6. Click Apply.

   The Administration Console creates the cache policy and Liquid Data begins caching results for the selected query. Any expiration times are calculated starting with the time that the cache policy was initially created.

Editing the Cache Policy

To edit the cache policy for a stored query:

1. In the left pane, click Liquid Data.

2. Click the Cache tab.

   The Administration Console displays the Cache tab.

   
    

3. Click the name of the stored query whose cache you want to configure.

4. Change the information described in Table  19-2, as needed.

5. Click Apply.

   The Administration Console updates the selected cache policy and flushes any cached results for this stored query.

Removing the Cache Policy

To remove the cache policy for a stored query:

1. In the left pane, click Liquid Data.

2. Click the Cache tab.

   The Administration Console displays the Cache tab.

   

   
    

3. Click the trash can next to the stored query whose cache you want to delete.

4. When prompted, click Yes to confirm removal.

   The Administration Console removes the selected cache policy and flushes any cached results for this query.

   Note: If you delete a stored query for which caching is enabled, Liquid Data also deletes the cached query plan and any cached results. For more information, see Deleting Folders and Files in the Server Repository.

 

---

Flushing the Cache

Liquid Data flushes the cached query result for a given stored query whenever:

• the stored query is updated or deleted
• caching is disabled on the Liquid Data Server

Liquid Data flushes the cached query result for a given stored query on the next invocation whenever:

• the query results have expired per the cache policy
• the cache policy for a stored query is updated or deleted