WebSphere eXtreme Scale Best Practices for Operation and Management

134 WebSphere eXtreme Scale Best Practices for Operation and Management

6.5 Query performance tuning

If you are beginning with WebSphere eXtreme Scale, you might rely too heavily on the query

capability of WebSphere eXtreme Scale, especially when incorporating it into an existing

application that is tailored to database access. WebSphere eXtreme Scale is primarily a

caching provider. The query functionality is a valid convenience feature of the product, but it is

not backed by all the sophisticated query optimizers that are found in top-end databases.

Therefore, WebSphere eXtreme Scale is most efficient when finding an object by its key

rather than when locating that same object by a query.

If you have to use query in WebSphere eXtreme Scale, try to use an index (or, better, the

reverse index pattern) and avoid parallel queries where possible. If all partitions are touched,

queries can only be as fast as the slowest individual system, which effectively eliminates

scaling. To tune the performance of your queries, use the techniques and tips that we

describe in this section.

6.5.1 Using parameters

When a query runs, the query string must be parsed and a plan must be developed to run the

query, both of which can be costly. WebSphere eXtreme Scale caches query plans by the

query string. Because the cache is a finite size, it is important to reuse query strings

whenever possible.

Using named or positional parameters also helps the performance by fostering query plan

reuse, as shown in the following example:

Query q = em.createQuery("select u from User where u.userId=?1");

q.setParameter(1, 123);

6.5.2 Using indexes

Proper indexing on a map might have a significant positive impact on query performance,

even though indexing has overhead on overall map performance. Without indexing on the

object attributes that are involved in queries, the query engine performs a table scan for each

attribute. The table scan is the most expensive operation during a query run.

Indexing on object attributes that are involved in queries allows the query engine to avoid an

unnecessary table scan, improving the overall query performance. If the application is

designed to use query intensively on a read-most map, configure indexes for object attributes

that are involved in the query. If the map is mostly updated, you must balance between query

performance improvement and indexing overhead on the map.

When POJOs are stored in a map, proper indexing can avoid a Java reflection. In the

following example query, the WHERE clause uses a range index search if the user ID field

has an index built over it. Otherwise, the query scans the entire map and evaluates the

WHERE clause by first getting the user ID using Java reflection and then comparing the user

ID with the value 500.

SELECT u FROM User u WHERE u.userId < 500

Indexes have the following requirements when used by query:

򐂰 All indexes must use the built-in HashIndex plug-in.

򐂰 All indexes must be statically defined. Dynamic indexes are not supported.

򐂰 The @Index annotation can be used to automatically create static HashIndex plug-ins.

򐂰 All single-attribute indexes must have the RangeIndex property set to true.