Query optimization using indexes

Program guide > Access data with client applications > Retrive entities and objects (Query API) > Query performance tuning

Query optimization using indexes

Define and using indexes properly can significantly improve query performance.

WebSphere eXtreme Scale queries can use built-in HashIndex plug-ins to improve performance of queries. Indexes can be defined on entity or object attributes. The query engine will automatically use the defined indexes if its WHERE clause uses one of the following strings:

A comparison expression with the following operators: =, <, >, <= or >= (any comparison expressions except not equals <> )
A BETWEEN expression
Operands of the expressions are constants or simple terms

Requirements

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 may be used to automatically create static HashIndex plug-ins.
All single-attribute indexes must have the RangeIndex property set to true.
All composite indexes must have the RangeIndex property set to false.
All association (relationship) indexes must have the RangeIndex property set to false.

For information about configuring the HashIndex, refer to Configure the HashIndex.

For information regarding indexing, see Indexing.

For a more efficient way to search for cached objects, see Use a composite index

Use hints to choose an index

An index can be manually selected using the setHint method on the Query and ObjectQuery interfaces with the HINT_USEINDEX constant. This can be helpful when optimizing a query to use the best performing index.

Query examples that use attribute indexes

The following examples use simple terms: e.empid, e.name, e.salary, d.name, d.budget and e.isManager. The examples assume that indexes are defined over the name, salary and budget fields of an entity or value object. The empid field is a primary key and isManager has no index defined.

The following query uses both indexes over the fields of name and salary. It returns all employees with names that equal the value of the first parameter or a salary equal to the value of the second parameter:

SELECT e FROM EmpBean e where e.name=?1 or e.salary=?2

The following query uses both indexes over the fields of name and budget. The query returns all departments named 'DEV' with a budget that is greater than 2000.

SELECT d FROM DeptBean dwhere d.name='DEV' and d.budget>2000

The following query returns all employees with a salary greater than 3000 and with an isManager flag value that equals the value of the parameter. The query uses the index that is defined over the salary field and performs additional filtering by evaluating the comparison expression: e.isManager=?1.

SELECT e FROM EmpBean e where e.salary>3000 and e.isManager=?1

The following query finds all employees who earn more than the first parameter, or any employee that is a manager. Although the salary field has an index defined, query scans the built-in index that is built over the primary keys of the EmpBean field and evaluates the expression: e.salary>?1 or e.isManager=TRUE.

SELECT e FROM EmpBean e WHERE e.salary>?1 or e.isManager=TRUE

The following query returns employees with a name that contains the letter a. Although the name field has an index defined, query does not use the index because the name field is used in the LIKE expression.

SELECT e FROM EmpBean e WHERE e.name LIKE '%a%'

The following query finds all employees with a name that is not "Smith". Although the name field has an index defined, query does not use the index because the query uses the not equals ( <> ) comparison operator.

SELECT e FROM EmpBean e where e.name<>'Smith'

The following query finds all departments with a budget less than the value of the parameter, and with an employee salary greater than 3000. The query uses an index for the salary, but it does not use an index for the budget because dept.budget is not a simple term. The dept objects are derived from collection e. You do not need to use the budget index to look for dept objects.

SELECT dept from EmpBean e, in (e.dept) dept where e.salary>3000 and dept.budget<?

The following query finds all employees with a salary greater than the salary of the employees that have the empid of 1, 2, and 3. The index salary is not used because the comparison involves a subquery. The empid is a primary key, however, and is used for a unique index search because all the primary keys have a built-in index defined.

SELECT e FROM EmpBean e WHERE e.salary > ALL (SELECT e1.salary FROM EmpBean e1 WHERE e1.empid=1 or e1.empid =2 or e1.empid=99)

To check if the index is being used by the query, you can view the Query plan. Here is an example query plan for the previous query:

for q2 in EmpBean ObjectMap using INDEX SCAN
    filter ( q2.salary >ALL     temp collection defined as
       IteratorUnionIndex of

         for q3 in EmpBean ObjectMap using UNIQUE INDEX key=(1)
        )

         for q3 in EmpBean ObjectMap using UNIQUE INDEX key=(2)
        )

         for q3 in EmpBean ObjectMap using UNIQUE INDEX key=(99)
        )
       returning new Tuple( q3.salary )
   returning new Tuple( q2  )

for q2 in EmpBean ObjectMap using RANGE INDEX on salary with range(3000,)
     for q3 in  q2.dept
      filter ( q3.budget
<  ?1 )
   returning new Tuple( q3  )

Indexing attributes

Indexes can be defined over any single attribute type with the constraints previously defined.

Define entity indexes using @Index

To define an index on an entity, simply define an annotation:

Entities using annotations

     @Entity
     public class Employee {
     @Id int empid;
     @Index String name
     @Index double salary
     @ManyToOne Department dept;
}
@Entity
     public class Department {
     @Id int deptid;
     @Index String name;
     @Index double budget;
     boolean isManager;
     @OneToMany Collection<Employee> employees;
     }

With XML

Indexes can also be defined using XML:

     Entities without annotations

     public class Employee {
     int empid;
     String name
     double salary
     Department dept;
     }
      
     public class Department {
     int deptid;
     String name;
     double budget;
     boolean isManager;
     Collection employees;
     }

ObjectGrid XML with attribute indexes

<?xml version="1.0" encoding="UTF-8"?>
     <objectGridConfig xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="http://ibm.com/ws/objectgrid/config ../objectGrid.xsd"
     xmlns="http://ibm.com/ws/objectgrid/config">
     <objectGrids>
     <objectGrid name="DepartmentGrid" entityMetadataXMLFile="entity.xml>
     <backingMap name="Employee" pluginCollectionRef="Emp"/>
     <backingMap name="Department" pluginCollectionRef="Dept"/>
     </objectGrid>
     </objectGrids>
     <backingMapPluginCollections>
     <backingMapPluginCollection id="Emp">
     <bean id="MapIndexPlugin" className="com.ibm.websphere.objectgrid.plugins.index.HashIndex">
     <property name="Name" type="java.lang.String" value="Employee.name"/>
     <property name="AttributeName" type="java.lang.String" value="name"/>
     <property name="RangeIndex" type="boolean" value="true"
     description="Ranges are must be set to true for attributes." />
     </bean>
     <bean id="MapIndexPlugin" className="com.ibm.websphere.objectgrid.plugins.index.HashIndex">
     <property name="Name" type="java.lang.String" value="Employee.salary"/>
     <property name="AttributeName" type="java.lang.String" value="salary"/>
     <property name="RangeIndex" type="boolean" value="true"
     description="Ranges are must be set to true for attributes." />
     </bean>
     </backingMapPluginCollection>
     <backingMapPluginCollection id="Dept">
     <bean id="MapIndexPlugin" className="com.ibm.websphere.objectgrid.plugins.index.HashIndex">
     <property name="Name" type="java.lang.String" value="Department.name"/>
     <property name="AttributeName" type="java.lang.String" value="name"/>
     <property name="RangeIndex" type="boolean" value="true"
     description="Ranges are must be set to true for attributes." />
     </bean>
     <bean id="MapIndexPlugin" className="com.ibm.websphere.objectgrid.plugins.index.HashIndex">
     <property name="Name" type="java.lang.String" value="Department.budget"/>
     <property name="AttributeName" type="java.lang.String" value="budget"/>
     <property name="RangeIndex" type="boolean" value="true"
     description="Ranges are must be set to true for attributes." />
     </bean>
     </backingMapPluginCollection>
     </backingMapPluginCollections>
     </objectGridConfig>

Entity XML

<?xml version="1.0" encoding="UTF-8"?>
     <entity-mappings xmlns="http://ibm.com/ws/projector/config/emd"
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="http://ibm.com/ws/projector/config/emd ./emd.xsd">
      
     <description>Department entities</description>
     <entity class-name="acme.Employee" name="Employee" access="FIELD">
     <attributes>
     <id name="empid" />
     <basic name="name" />
     <basic name="salary" />
     <many-to-one name="department"
     target-entity="acme.Department"
     fetch="EAGER">
     <cascade><cascade-persist/></cascade>
     </many-to-one>
     </attributes>
     </entity>
     <entity class-name="acme.Department" name="Department" access="FIELD">
     <attributes>
     <id name="deptid" />
     <basic name="name" />
     <basic name="budget" />
     <basic name="isManager" />
     <one-to-many name="employees"
     target-entity="acme.Employee"
     fetch="LAZY" mapped-by="parentNode">
     <cascade><cascade-persist/></cascade>
     </one-to-many>
     </attributes>
     </entity>
     </entity-mappings>

Define indexes for non-entities using XML

Indexes for non-entity types are defined in XML. There is no difference when creating the MapIndexPlugin for entity maps and non-entity maps.

Java bean
public class Employee {
     int empid;
     String name
     double salary
     Department dept;

    public class Department {
     int deptid;
     String name;
     double budget;
     boolean isManager;
     Collection employees;
     }

ObjectGrid XML with attribute indexes

<?xml version="1.0" encoding="UTF-8"?>
     <objectGridConfig xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="http://ibm.com/ws/objectgrid/config ../objectGrid.xsd"
     xmlns="http://ibm.com/ws/objectgrid/config">
     <objectGrids>
     <objectGrid name="DepartmentGrid">
     <backingMap name="Employee" pluginCollectionRef="Emp"/>
     <backingMap name="Department" pluginCollectionRef="Dept"/>
     <querySchema>
     <mapSchemas>
     <mapSchema mapName="Employee" valueClass="acme.Employee"
     primaryKeyField="empid" />
     <mapSchema mapName="Department" valueClass="acme.Department"
     primaryKeyField="deptid" />
     </mapSchemas>
     <relationships>
     <relationship source="acme.Employee"
     target="acme.Department"
     relationField="dept" invRelationField="employees" />
     </relationships>
     </querySchema>
     </objectGrid>
     </objectGrids>
     <backingMapPluginCollections>
     <backingMapPluginCollection id="Emp">
     <bean id="MapIndexPlugin" className="com.ibm.websphere.objectgrid.plugins.index.HashIndex">
     <property name="Name" type="java.lang.String" value="Employee.name"/>
     <property name="AttributeName" type="java.lang.String" value="name"/>
     <property name="RangeIndex" type="boolean" value="true"
     description="Ranges are must be set to true for attributes." />
     </bean>
     <bean id="MapIndexPlugin" className="com.ibm.websphere.objectgrid.plugins.index.HashIndex">
     <property name="Name" type="java.lang.String" value="Employee.salary"/>
     <property name="AttributeName" type="java.lang.String" value="salary"/>
     <property name="RangeIndex" type="boolean" value="true"
     description="Ranges are must be set to true for attributes." />
     </bean>
     </backingMapPluginCollection>
     <backingMapPluginCollection id="Dept">
     <bean id="MapIndexPlugin" className="com.ibm.websphere.objectgrid.plugins.index.HashIndex">
     <property name="Name" type="java.lang.String" value="Department.name"/>
     <property name="AttributeName" type="java.lang.String" value="name"/>
     <property name="RangeIndex" type="boolean" value="true"
     description="Ranges are must be set to true for attributes." />
     </bean>
     <bean id="MapIndexPlugin" className="com.ibm.websphere.objectgrid.plugins.index.HashIndex">
     <property name="Name" type="java.lang.String" value="Department.budget"/>
     <property name="AttributeName" type="java.lang.String" value="budget"/>
     <property name="RangeIndex" type="boolean" value="true"
     description="Ranges are must be set to true for attributes." />
     </bean>
     </backingMapPluginCollection>
     </backingMapPluginCollections>
     </objectGridConfig>

Indexing relationships

WebSphere eXtreme Scale stores the foreign keys for related entities within the parent object. For entities, the keys are stored in the underlying tuple. For non-entity objects, the keys are explicitly stored in the parent object.

Add an index on a relationship attribute can speed up queries that use cyclical references or use the IS NULL, IS EMPTY, SIZE and MEMBER OF query filters. Both single- and multi-valued associations may have the @Index annotation or a HashIndex plug-in configuration in an ObjectGrid descriptor XML file.

Define entity relationship indexes using @Index

The following example defines entities with @Index annotations:

Entity with annotation

@Entity
public class Node {
    @ManyToOne @Index
    Node parentNode;

    @OneToMany @Index
    List<Node> childrenNodes = new ArrayList();

    @OneToMany @Index
    List<BusinessUnitType> businessUnitTypes = new ArrayList();
}

Define entity relationship indexes using XML

The following example defines the same entities and indexes using XML with HashIndex plug-ins:

     Entity without annotations

     public class Node {
     int nodeId;
     Node parentNode;
     List<Node> childrenNodes = new ArrayList();
     List<BusinessUnitType> businessUnitTypes = new ArrayList();
     }

ObjectGrid XML

<?xml version="1.0" encoding="UTF-8"?>
     <objectGridConfig xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="http://ibm.com/ws/objectgrid/config ../objectGrid.xsd"
     xmlns="http://ibm.com/ws/objectgrid/config">
     <objectGrids>
     <objectGrid name="ObjectGrid_Entity" entityMetadataXMLFile="entity.xml>
     <backingMap name="Node" pluginCollectionRef="Node"/>
     <backingMap name="BusinessUnitType" pluginCollectionRef="BusinessUnitType"/>
     </objectGrid>
     </objectGrids>
     <backingMapPluginCollections>
     <backingMapPluginCollection id="Node">
     <bean id="MapIndexPlugin" className="com.ibm.websphere.objectgrid.plugins.index.HashIndex">
     <property name="Name" type="java.lang.String" value="parentNode"/>
     <property name="AttributeName" type="java.lang.String" value="parentNode"/>
<property name="RangeIndex" type="boolean" value="false"
     description="Ranges are not supported for association indexes." />     </bean>
     <bean id="MapIndexPlugin" className="com.ibm.websphere.objectgrid.plugins.index.HashIndex">
     <property name="Name" type="java.lang.String" value="businessUnitType"/>
     <property name="AttributeName" type="java.lang.String" value="businessUnitTypes"/>

<property name="RangeIndex" type="boolean" value="false"
     description="Ranges are not supported for association indexes." />
    </bean>
     <bean id="MapIndexPlugin" className="com.ibm.websphere.objectgrid.plugins.index.HashIndex">
     <property name="Name" type="java.lang.String" value="childrenNodes"/>
     <property name="AttributeName" type="java.lang.String" value="childrenNodes"/>
<property name="RangeIndex" type="boolean" value="false"
     description="Ranges are not supported for association indexes." />
     </bean>
     </backingMapPluginCollection>
     </backingMapPluginCollections>
     </objectGridConfig>
      
 Entity XML

     <?xml version="1.0" encoding="UTF-8"?>
     <entity-mappings xmlns="http://ibm.com/ws/projector/config/emd"
     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="http://ibm.com/ws/projector/config/emd ./emd.xsd">
      
     <description>My entities</description>
     <entity class-name="acme.Node" name="Account" access="FIELD">
     <attributes>
     <id name="nodeId" />
     <one-to-many name="childrenNodes"
     target-entity="acme.Node"
     fetch="EAGER" mapped-by="parentNode">
     <cascade><cascade-all/></cascade>
     </one-to-many>
     <many-to-one name="parentNodes"
     target-entity="acme.Node"
     fetch="LAZY" mapped-by="childrenNodes">
     <cascade><cascade-none/></cascade>
     </one-to-many>
     <many-to-one name="businessUnitTypes"
     target-entity="acme.BusinessUnitType"
     fetch="EAGER">
     <cascade><cascade-persist/></cascade>
     </many-to-one>
</attributes>
     </entity>
     <entity class-name="acme.BusinessUnitType" name="BusinessUnitType" access="FIELD">
     <attributes>
     <id name="buId" />
     <basic name="TypeDescription" />
     </attributes>
     </entity>
     </entity-mappings>

Use the previously defined indexes, the following entity query examples are optimized:

SELECT n FROM Node n WHERE n.parentNode is null
SELECT n FROM Node n WHERE n.businessUnitTypes is EMPTY
     SELECT n FROM Node n WHERE size(n.businessUnitTypes)>=10
     SELECT n FROM BusinessUnitType b, Node n WHERE b member of n.businessUnitTypes and b.name='TELECOM'

Define non-entity relationship indexes

The following example defines a HashIndex plug-in for non-entity maps in an ObjectGrid descriptor XML file:

<?xml version="1.0" encoding="UTF-8"?>
<objectGridConfig xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  xsi:schemaLocation="http://ibm.com/ws/objectgrid/config ../objectGrid.xsd"
  xmlns="http://ibm.com/ws/objectgrid/config">
 
<objectGrids>
   
<objectGrid name="ObjectGrid_POJO">
     
<backingMap name="Node" pluginCollectionRef="Node"/>
     
<backingMap name="BusinessUnitType" pluginCollectionRef="BusinessUnitType"/>
     
<querySchema>
       
<mapSchemas>
         
<mapSchema mapName="Node" 
                    valueClass="com.ibm.websphere.objectgrid.samples.entity.Node"
            primaryKeyField="id" />
         
<mapSchema mapName="BusinessUnitType"
            valueClass="com.ibm.websphere.objectgrid.samples.entity.BusinessUnitType"
            primaryKeyField="id" />
       
</mapSchemas>
       
<relationships>
         
<relationship source="com.ibm.websphere.objectgrid.samples.entity.Node" 
            target="com.ibm.websphere.objectgrid.samples.entity.Node"
            relationField="parentNodeId" invRelationField="childrenNodeIds" />
         
<relationship source="com.ibm.websphere.objectgrid.samples.entity.Node" 
            target="com.ibm.websphere.objectgrid.samples.entity.BusinessUnitType"
            relationField="businessUnitTypeKeys" invRelationField="" />
       
</relationships>
     
</querySchema>
   
</objectGrid>
 
</objectGrids>
 
<backingMapPluginCollections>
   
<backingMapPluginCollection id="Node">
     
<bean id="MapIndexPlugin" className="com.ibm.websphere.objectgrid.plugins.index.HashIndex">
       
<property name="Name" type="java.lang.String" value="parentNode"/>
<property name="Name" type="java.lang.String" value="parentNodeId"/>
<property name="AttributeName" type="java.lang.String" value="parentNodeId"/>
<property name="RangeIndex" type="boolean" value="false"
     description="Ranges are not supported for association indexes." />
     
</bean>
     
<bean id="MapIndexPlugin" className="com.ibm.websphere.objectgrid.plugins.index.HashIndex">
       
<property name="Name" type="java.lang.String" value="businessUnitType"/>
       
<property name="AttributeName" type="java.lang.String" value="businessUnitTypeKeys"/>

<property name="RangeIndex" type="boolean" value="false"
     description="Ranges are not supported for association indexes." />
  
</bean>
     
<bean id="MapIndexPlugin" className="com.ibm.websphere.objectgrid.plugins.index.HashIndex">
<property name="Name" type="java.lang.String" value="childrenNodeIds"/>
     <property name="AttributeName" type="java.lang.String" value="childrenNodeIds"/>     
 
<property name="RangeIndex" type="boolean" value="false"
                     description="Ranges are not supported for association indexes." />
</bean>
   
</backingMapPluginCollection>
 
</backingMapPluginCollections>
</objectGridConfig>

Given the above index configurations, the following object query examples are optimized:

SELECT n FROM Node n WHERE n.parentNodeId is null
SELECT n FROM Node n WHERE n.businessUnitTypeKeys is EMPTY
SELECT n FROM Node n WHERE size(n.businessUnitTypeKeys)>=10
SELECT n FROM BusinessUnitType b, Node n WHERE 
    b member of n.businessUnitTypeKeys and b.name='TELECOM'

Parent topic:

Query performance tuning

Related concepts

Indexing

Data access with indexes (Index API)

Configure the HashIndex