3. Prepared SQL Cache

3. Prepared SQL Cache
Prev	Chapter 10. Caching	Next

Prepared SQL Cache caches SQL statements corresponding to JPQL queries. If a query is executed more than once in the same or different persistence contexts, the SQL statement generated during the first execution is cached and executed directly for subsequent execution. Direct execution of SQL offers significant performance gain as it saves the cost of parsing query string and, more importantly, populating the query expression tree during every execution. Relative performance gain becomes higher as the complexity of forming a SQL query from a JPQL string increases. For example, a JPQL query Q1 that involves multiple joins across tables takes more computation to translate into a SQL statement than a JPQL query Q2 to select by simple primary key identifier. Correspondingly, repeated execution of Q1 will gain more performance advantage than Q2 with Prepared SQL Cache.

Prepared SQL Cache is configured by the openjpa.jdbc.QuerySQLCache configuration property. This property accepts a plugin string (see Section 4, “ Plugin Configuration ”) with value of true or false. The default is true. The execution statistics of the cached queries can be optionally collected as

  <property name="openjpa.jdbc.QuerySQLCache" value="true(EnableStatistics=true)">

The QueryStatistics can be accessed via PreparedQueryCache.getStatistics().

Table 10.3. Pre-defined aliases

Alias	Value	Notes
`true`	`org.apache.openjpa.util.CacheMap`	The default option. Uses a `CacheMap` to store SQL string. `CacheMap` maintains a fixed number of cache entries, and an optional soft reference map for entries that are moved out of the LRU space. So, for applications that have a monotonically increasing number of distinct queries, this option can be used to ensure that a fixed amount of memory is used by the cache.
`false`	none	Disables the SQL cache.

Following salient points to be noted regarding usage of Prepared Query Cache.

Prepared Query Cache uses the original JPQL string as the key to index the corresponding SQL statement. Hence the JPQL strings that are semantically identical but differ by character case or identification variables are considered as different by this cache. One of the implications is that the applications can gain better advantage from the Prepared Query Cache by using parameters in their JPQL query rather than concatenating the parameter values in the query string itself .

For example, contrast the following two examples of executing JPQL queries.
Example 10.26. Hardcoded Selection Value in JPQL Query
```
	String jpql = "SELECT p FROM Person p WHERE p.name='John'";
	List johns = em.createQuery(jpql).getResultList();
	jpql = "SELECT p FROM Person p WHERE p.name='Tom'";
	List toms = em.createQuery(jpql).getResultList();
	
```
In Example 10.26, “Hardcoded Selection Value in JPQL Query”, the queries have hardcoded the selection value for the p.name field. Prepared Query Cache will not recognize the second execution as same as the first, though both will result in same SQL statement.

While in Example 10.27, “Parameterized Selection Value in JPQL Query”, the selection value for the p.name field is parameterized. Prepared Query Cache will recognize the second execution as same as the first, and will execute the cached SQL statement directly.
Example 10.27. Parameterized Selection Value in JPQL Query
```
	String jpql = "SELECT p FROM Person p WHERE p.name=:name";
	List johns = em.createQuery(jpql).setParameter("name","John").getResultList();
	List toms = em.createQuery(jpql).setParameter("name","Tom").getResultList();
	
```
A JPQL query may not always translate into a single SQL query. The JPQL queries that require multiple select statements are never cached.
Same JPQL query may result into different SQL statements under different execution context. Execution context parameters such as fetch configuration or locking mode determine the resultant SQL. However, Prepared SQL Cache does not capture the execution context parameters while caching a generated SQL.
The named or positional parameters of a JPQL query can be set to different values across executions. In general, the corresponding cached SQL statement will be re-parameterized accordingly. However, the parameter value itself can determine the SQL query. For example, when a JPQL query compares a relation field for equality against a parameter p, whether the actual value of p is null or not will determine the generated SQL statement. Another example is collection valued parameter for IN expression. Each element of a collection valued parameter results into a SQL parameter. If a collection valued parameter across executions are set to different number of elements, then the parameters of the cached SQL do not correspond. If such situations are encountered while re-parameterizing the cached SQL, the cached version is not reused and the original JPQL query is used to generate a new SQL statement for execution.
JPQL query that returns a numeric value such as SELECT count(p) FROM PObject p is never cached.

Several mechanisms are available to the application to bypass SQL caching for a JPQL query.

A user application can disable Prepared SQL Cache for entire lifetime of a persistence context by invoking the following method on OpenJPA's EntityManager SPI interface:
```
	OpenJPAEntityManagerSPI.setQuerySQLCache(boolean)
	
```
A user application can instruct particular execution of a JPQL query to ignore any cached SQL query, by setting QueryHints.HINT_IGNORE_PREPARED_QUERY or "openjpa.hint.IgnorePreparedQuery" to true via standard jakarta.persistence.Query.setHint(String, Object) method. If a SQL query has been cached corresponding to the JPQL query prior to this execution, then the cached SQL remains in the cache and will be reused for any subsequent execution of the same JPQL query.
A user application can instruct all subsequent execution of a JPQL query to ignore any cached SQL query, by setting QueryHints.HINT_INVALIDATE_PREPARED_QUERY or "openjpa.hint.InvalidatePreparedQuery" to true The SQL query is removed from the cache. Also the JPQL query will never be cached again during the lifetime of the entire persistence unit.
Plug-in property openjpa.jdbc.QuerySQLCache can be configured to exclude certain JPQL queries as shown below.
```
	<property name="openjpa.jdbc.QuerySQLCache" value="true(excludes='select c from Company c;select d from Department d')"/>
	
```
will never cache JPQL queries select c from Company c and select d from Department d.

Prev	Up	Next
2. Query Compilation Cache	Home	Chapter 11. Encryption Provider