Program guide > Performance considerations



Query performance tuning

To tune the performance of the queries, use the following techniques and tips.


Use parameters

When a query runs, the query string must be parsed and a plan developed to run the query, both of which can be costly.WebSphere eXtreme Scale caches query plans by the query string. Since the cache is a finite size, it is important to reuse query strings whenever possible. Using named or positional parameters also helps performance by fostering query plan reuse. Positional Parameter Example Query q = em.createQuery("select c from Customer c where c.surname=?1"); q.setParameter(1, "Claus");


Use indexes

Proper indexing on a map might have a significant impact on query performance, even though indexing has some overhead on overall map performance. Without indexing on object attributes 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 allow 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, then balance between query performance improvement and indexing overhead on the map. See Indexing for more information.

When plain old Java™ objects (POJO) are stored in a map, proper indexing can avoid a Java reflection. In the following example, query replaces the WHERE clause with range index search, if the budget field has an index built over it. Otherwise, query scans the entire map and evaluates the WHERE clause by first getting the budget using Java reflection and then comparing the budget with the value 50000:

SELECT d FROM DeptBean d WHERE d.budget=50000

See Query plan for details on how to best tune individual queries and how different syntax, object models and indexes can affect query performance.


Use pagination

In client-server environments, the query engine transports the entire result map to the client. The data that is returned should be divided into reasonable chunks. The EntityManager Query and ObjectMap ObjectQuery interfaces both support the setFirstResult and setMaxResults methods that allow the query to return a subset of the results.


Return primitive values instead of entities

With the EntityManager Query API, entities are returned as query parameters. The query engine currently returns the keys for these entities to the client. When the client iterates over these entities using the Iterator from the getResultIterator method, each entity is automatically inflated and managed as if it were created with the find method on the EntityManager interface. The entire entity graph is built from the entity ObjectMap on the client. The entity value attributes and any related entities are eagerly resolved.

To avoid building the costly graph, modify the query to return the individual attributes with path navigation.

For example:

// Returns an entity
SELECT p FROM Person p
// Returns attributes SELECT p.name, p.address.street, p.address.city, p.gender FROM Person p


Parent topic:

Retrive entities and objects (Query API)


Parent topic:

Performance considerations for application developers


Related concepts

Query data in multiple time zones

Insert data for different time zones

Use the ObjectQuery API

EntityManager Query API

Reference for eXtreme Scale queries

Use objects other than keys to find partitions (PartitionableKey interface)

JVM tuning

CopyMode best practices

Byte array maps

Plug-in evictor performance best practices

Locking performance best practices

Serialization performance

ObjectTransformer interface best practices


+

Search Tips   |   Advanced Search