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
- Query plan
All eXtreme Scale queries have a query plan. The plan describes how the query engine will interact with ObjectMaps and indexes. Display the query plan to determine if the query string or indexes are being used appropriately. The query plan can also be used to explore the differences that subtle changes in a query string make in the way eXtreme Scale runs a query.
- Query optimization using indexes
Defining and using indexes properly can significantly improve query performance.
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
Reference for eXtreme Scale queries
Use objects other than keys to find partitions (PartitionableKey interface)
Plug-in evictor performance best practices
Locking performance best practices
ObjectTransformer interface best practices