3.11.3. Prepared SQL Cache
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 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().
| Alias | Value | Notes |
|---|---|---|
|
|
The default option. Uses a
CacheMap to store SQL string. |
|
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.
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 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 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.
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 Cachedoes 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 ofpisnullor not will determine the generated SQL statement. Another example is collection valued parameter forINexpression. 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 pis 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_QUERYor"openjpa.hint.IgnorePreparedQuery"totruevia standardjakarta.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_QUERYor"openjpa.hint.InvalidatePreparedQuery"totrue. 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.QuerySQLCachecan 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 candselect d from Department d.