Dynamic query performance considerations
While using a dynamic query can be convenient, there are times when it can have an impact on the application performance.
General performance
considerations
Use of the following elements in the dynamic query can diminish application performance somewhat:
- Datatype converters and Java methods
Why: In general, query operations and predicates are translated into SQL so that the database server can perform them. If the query includes datatype converters (for EJB to RDB mapping, for example) or Java methods, however, the associated predicates and operations of the query must be performed in the memory of the application server.
- EJB methods and criteria that call for the return
of EJB references
Why: Queries that incorporate these elements trigger full activation of EJBs in the memory of the application server. (Returning a list of CMP fields from a query does not cause an EJB to be activated.)
When assessing application performance, you should also be aware that dynamic queries share connections with the persistence manager. Consequently, an application that includes a mixture of finder methods, CMR navigation, and dynamic queries relies on a single shared connection between the persistence manager and the dynamic query service to perform these tasks.
Limiting the return collection size
- Remote interface queries:
The QueryIterator class of the remote
interface mandates that all of the query results materialize in application server memory over the course of one method call. The SQL cursor(s)
used to run the EJB query are closed upon completion of that call. Because this requirement
poses a high risk for creating bottlenecks within the database server, we need to limit the size of any potentially large result collections.
- Local interface queries: In most situations, the QueryLocalIterator
object behaves as a wrapper around an SQL cursor. It is demand-driven;
it causes data to be returned incrementally. For each record retrieval
from
the database, the next() method must be called on the iterator.
Use of certain operations in local interface queries, however, overrides the demand-driven behavior. In these cases, the query results fully materialize in memory just as do the result collections of remote interface queries. An example of such a case is:
select e.myBusinessMethod( ) from EmpBean e where e.salary < 50000 order by 1 desc
This query requires performance of an EJB method to produce the final result collection. Consequently, the full dataset from the database must be returned in one collection to application server memory, where the EJB method can be run on the dataset in its entirety. For that reason, local interface query operations that invoke EJB methods are generally not demand-driven. We cannot control the return collection size for such queries.
Because they are demand-driven, all other local interface queries allow you to control the size of return collections. Use a call of the close() method on the QueryLocalIterator object to close the query loop after the desired number of return values has been fetched from the datastore. Otherwise, the SQL cursor(s) used to run the EJB query are not closed until the full result set accumulates in memory, or the transaction ends.
Related concepts
Related tasks
Concept topic