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
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
will gain more performance advantage than
Q2 with Prepared SQL
Prepared SQL Cache is configured by the
openjpa.jdbc.QuerySQLCache configuration property. This
property accepts a plugin string (see Section 4, “
with value of
false. The default
true. The execution statistics of the cached queries can be
optionally collected as
<property name="openjpa.jdbc.QuerySQLCache" value="true(EnableStatistics=true)">
QueryStatistics can be accessed via
Table 10.3. Pre-defined aliases
The default option. Uses a
|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
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();
p, whether the actual value of
nullor not will determine the generated SQL statement. Another example is collection valued parameter for
INexpression. 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.
SELECT count(p) FROM PObject pis never cached.
Several mechanisms are available to the application to bypass SQL caching for a JPQL query.
javax.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.
trueThe SQL query is removed from the cache. Also the JPQL query will never be cached again during the lifetime of the entire persistence unit.
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 cand
select d from Department d.