Dynamic query service performance considerations

You can use the dynamic query service to build and execute queries against entity beans constructed dynamically at runtime, rather than defining them at deployment time. By using dynamic query you gain the flexibility of queries defined at runtime and utilize the power of EJB-Query Language (QL). Apart from supporting all of the capabilities of an EJB-QL query, dynamic query adds additional functionality not available to standard static query. Two examples are the ability to select multiple data fields directly from the bean itself (static queries currently only allow one) and executing business methods directly in the query.

When used as a direct replacement for an equivalent static query, dynamic query is slower that the static variation. This slowdown is due to the need for parsing and building a plan for the query, in addition to executing it. In the static variation, these costs are paid at deploy time. In WebSphere Application Server Enterprise 5.0, expect to see 60% degradation in performance between executing a predefined static query and the equivalent dynamic query. Through optimizations implemented in WebSphere Application Server Enterprise 5.01, a cache for recently used query plans and other performance improvements, this degradation is reduced to about 25%.

You can effectively create more efficient and less resource intensive applications with dynamic query. For example, two data fields are required from the results of a query. Because a standard EJB-QL query can only select one data field, it is necessary to select the entire EJB object and extract the needed data from the returned results through data access methods, possibly traversing Container Managed Relationships (CMR) boundaries in the process. However, when using dynamic query, you can get both pieces of data directly from the query without additional CMR traversal or accessor methods. This principle is the key to evaluating whether or not dynamic query can be used for performance gain. You should review the amount of data that must be retrieved, in addition to the amount of business logic needed to retrieve it, for example, CMR traversal or accessor methods.

Using parameters in the query rather than literal values is another performance consideration. Under most circumstances, it is better to define conditional values as parameters in the query and then pass those parameters through the appropriate mechanisms. By using this method, you have a greater chance of matching a cached query plan and you eliminate the need to parse and build the plan from scratch. For example, "SELECT Object(o) FROM schemaname AS o WHERE o.fieldname LIKE foo", is more appropriately expressed as "SELECT Object(o) FROM schemaname AS o WHERE o.fieldname LIKE ?1" with the value foo passed as a parameter to the executeQuery method. The result is that any subsequent execution of a dynamic query structure that is the same, except for different string literal conditions, are registered as a plan cache hit affecting observed performance.

The dynamic query service is inherently handicapped from a performance viewpoint because of the necessary work of parsing the supplied queries dynamically and building the subsequent query plans. Despite this, the added functionality gained through the use of dynamic query, specifically the ability to select multiple data fields in a single query even across CMRs, creates opportunities to utilize dynamic query for the sake of performance improvement. The additional benefits of dynamic query in WebSphere Application Server Enterprise 5.01, like the use of parameters within the query, further improves the performance through caching and reusing a more general set of plans.




Searchable topic ID:   rprf_dynqtune
Last updated: Jun 21, 2007 8:07:48 PM CDT    WebSphere Business Integration Server Foundation, Version 5.0.2
http://publib.boulder.ibm.com/infocenter/wasinfo/index.jsp?topic=/com.ibm.wasee.doc/info/ee/ae/rprf_dynqtune.html

Library | Support | Terms of Use | Feedback