Query tables and query performance

IBM BPM, V8.0.1, All platforms > Programming IBM BPM > Developing client applications for BPEL processes and tasks > Queries on BPEL process and task data > Query tables in Business Process Choreographer
Query tables and query performance

Query tables introduce a clean programming model for developing client applications that retrieve lists of human tasks and BPEL processes in Business Process Choreographer. Using query tables improves the performance. Information is provided about the query table API parameters and other factors that affect the performance.
Query response times on query tables depend mainly on the authorization options, filters, and selection criteria used. The following are some general performance tips to consider.

Authorization options have considerable performance impact. Enable authorization using as few options as is possible, such as individual and group work items. Avoid using inherited work items. The authorization options can be further restricted when the query is run. Also, if not needed, specify that authorization using work items is not required.
If authorization using work items is required, specify an authorization filter.
For example, to allow only objects in the query table with a potential owner work item, use WI.REASON=REASON_POTENTIAL_OWNER.
Filtering on the primary query table is efficient.
For example, to allow only tasks in the ready state in the query table where TASK is the primary query table.
Filters on the query table and query filters, which are filters that are passed when the query is run, are less efficient as primary filters in terms of performance.
Avoid, where possible, using parameters in filters and selection criteria.
Avoid using LIKE operators in filters and selection criteria.

Composite query table definition

The following table provides information about the query performance impact of options that are defined on composite query tables. It also provides information other topics related to composite query table definitions. The impact given in column Performance Impact is an average performance impact, actual impact observations might vary.

Query performance impact of composite query table options
Object or topic Performance impact Description
Query table filter Negative Filters on query tables are the filters with the highest negative impact on query performance. These filters typically cannot use any defined indexes in the database.
Primary query table filter Positive A filter on the primary query table provides high performance filtering at an early stage of the query result set calculation. It is suggested to restrict the contents of the query table using a primary query table filter.
Authorization filter Positive A filter on authorization can improve the performance of the query, such as how the primary query table filter improves it. If possible, apply an authorization filter.
For example, if reader work items should not be considered, specify WI.REASON=REASON_READER.
Selection criteria None Some primary query table to attached query table relationships require the definition of a selection criterion in order to meet the one-to-one or one-to-zero relationship. A selection criterion typically has low performance impact because it is evaluated for a small numbers of rows only.
Parameters None Currently, using parameters in query tables has no negative performance impact. Nevertheless, use parameters only if they are needed.
Instance-based authorization Negative If instance-based authorization is used, each object in the query table must be checked against the existence of a work item. Work items are represented as entries in the WORK_ITEM query table. This verification affects performance.
Instance-based authorization:

everybody
individuals
groups
inherited

Negative Each type of work item that is specified for use in the query table has a performance impact. Applications with high volume queries should only use individual and group work items, or only one of those. Inherited work items are usually not required, in particular when defining task lists that return human tasks representing to-dos. They should be used only when it is clear that they are needed, for example, to return lists of tasks that belong to a BPEL process where a person might have read access based on the authorization for the enclosing BPEL process.
Role-based authorization or no authorization None If role-based authorization or no authorization is used, checks against work items are not made.
Number of defined attributes Currently none Currently, the number of attributes contained in a query table has no impact on performance. Nevertheless, include only those attributes that are needed as part of a query table.

Query table API

The following table provides information about the query performance impact of options that are specified on the query table API. The impact given in the Performance impact column is an average performance impact; actual impact observations might vary.

Query performance impact of query table API options
Option Performance impact Description
Selected attributes Negative (less is better) The number of attributes that are selected when a query is run on a query table impacts the number of attributes that need to be processed both by the database and by the Business Process Choreographer. Also, for composite query tables, information from attached query tables need be retrieved only if they are either specified by the selected attributes or referenced by the query table filter or by the query filter.
Query filter Negative If specified, the query filter currently has the same performance impact as the query table filter. However, it is a good practice if filters are specified on query tables rather than passed into the query table API.
Sort attributes Negative The sorting of query result sets is an expensive operation, and database optimizations are restricted if sorting is used. If not needed, sorting should be avoided. Most applications require sorting, however.
Threshold Positive The specification of a threshold can greatly improve the performance of queries. It is a best practice to always specify a threshold.
Skip count Negative Skipping a particular number of objects in the query result set is expensive and should be done only if required, for example, when paging over a query result.
Time zone None The time zone setting has no performance impact.
Locale None The locale setting has no performance impact.
Distinct rows Negative Use distinct in queries has some performance impact but might be necessary in order to retrieve non-duplicate rows. This option impacts only on row-based queries and is ignored otherwise.
Count queries Positive If only the total number of entities or the number of rows is needed, that is, the contents are not needed for all entries of the query table, use either the queryEntityCount or queryRowCount method. Business Process Choreographer can apply optimizations that are valid only for count queries.

Other considerations

Other factors to consider regarding performance are:

Query table performance: Other considerations
Item Description
Number of query tables on the system The number of query tables which are deployed on a Business Process Choreographer container does not influence the performance of query table queries. Also, currently, it does not influence the navigation of BPEL process instances, nor does it have impact on claim or complete operations on human tasks. Due to maintainability, keep the number of query tables at a reasonable level. Typically, one query table represents one task list or process list which is displayed on the user interface.
Database tuning Although optimized SQL is used to access the contents of a query table, database tuning needs to be implemented on a Business Process Choreographer database:

Database memory should be set to a maximum, taking into account other processes that are running on the database server, as well as hardware constraints.
Statistics on the database must be up-to-date, and should be updated on a regular basis. Typically, those procedures are already implemented in large topologies.
For example, collect database statistics for the optimizer once per week in order to reflect changes of the data in the database.
Database systems provide tools to reorganize (or defragment) the data containers. The physical layout of the data in a database can also influence query performance and access paths of queries.
Optimal indexes are the key for good query performance. Business Process Choreographer comes with predefined indexes which are optimized for both process navigation and query performance of typical scenarios. In customized environments, additional indexes might be necessary in order to support high volume task or process list queries. Use tools provided by the database in order to support the queries which are run on a query table.

Sorting and filtering To improve the performance of sorting and filtering, you can use the inline custom properties CUSTOM_TEXT1 through CUSTOM_TEXT8. These query table attributes are available for process and task instances and process and task templates. Because inline custom properties are stored in the same query table as the entity that they are associated with, filtering and sorting is more efficient. You can replace some or all of the non-inline custom properties for processes and tasks with inline custom properties.

Query tables in Business Process Choreographer

Related concepts:
Use custom properties for human tasks

Object or topic	Performance impact	Description
Query table filter	Negative	Filters on query tables are the filters with the highest negative impact on query performance. These filters typically cannot use any defined indexes in the database.
Primary query table filter	Positive	A filter on the primary query table provides high performance filtering at an early stage of the query result set calculation. It is suggested to restrict the contents of the query table using a primary query table filter.
Authorization filter	Positive	A filter on authorization can improve the performance of the query, such as how the primary query table filter improves it. If possible, apply an authorization filter. For example, if reader work items should not be considered, specify WI.REASON=REASON_READER.
Selection criteria	None	Some primary query table to attached query table relationships require the definition of a selection criterion in order to meet the one-to-one or one-to-zero relationship. A selection criterion typically has low performance impact because it is evaluated for a small numbers of rows only.
Parameters	None	Currently, using parameters in query tables has no negative performance impact. Nevertheless, use parameters only if they are needed.
Instance-based authorization	Negative	If instance-based authorization is used, each object in the query table must be checked against the existence of a work item. Work items are represented as entries in the WORK_ITEM query table. This verification affects performance.
Instance-based authorization: everybody individuals groups inherited	Negative	Each type of work item that is specified for use in the query table has a performance impact. Applications with high volume queries should only use individual and group work items, or only one of those. Inherited work items are usually not required, in particular when defining task lists that return human tasks representing to-dos. They should be used only when it is clear that they are needed, for example, to return lists of tasks that belong to a BPEL process where a person might have read access based on the authorization for the enclosing BPEL process.
Role-based authorization or no authorization	None	If role-based authorization or no authorization is used, checks against work items are not made.
Number of defined attributes	Currently none	Currently, the number of attributes contained in a query table has no impact on performance. Nevertheless, include only those attributes that are needed as part of a query table.

Option	Performance impact	Description
Selected attributes	Negative (less is better)	The number of attributes that are selected when a query is run on a query table impacts the number of attributes that need to be processed both by the database and by the Business Process Choreographer. Also, for composite query tables, information from attached query tables need be retrieved only if they are either specified by the selected attributes or referenced by the query table filter or by the query filter.
Query filter	Negative	If specified, the query filter currently has the same performance impact as the query table filter. However, it is a good practice if filters are specified on query tables rather than passed into the query table API.
Sort attributes	Negative	The sorting of query result sets is an expensive operation, and database optimizations are restricted if sorting is used. If not needed, sorting should be avoided. Most applications require sorting, however.
Threshold	Positive	The specification of a threshold can greatly improve the performance of queries. It is a best practice to always specify a threshold.
Skip count	Negative	Skipping a particular number of objects in the query result set is expensive and should be done only if required, for example, when paging over a query result.
Time zone	None	The time zone setting has no performance impact.
Locale	None	The locale setting has no performance impact.
Distinct rows	Negative	Use distinct in queries has some performance impact but might be necessary in order to retrieve non-duplicate rows. This option impacts only on row-based queries and is ignored otherwise.
Count queries	Positive	If only the total number of entities or the number of rows is needed, that is, the contents are not needed for all entries of the query table, use either the queryEntityCount or queryRowCount method. Business Process Choreographer can apply optimizations that are valid only for count queries.

Item	Description
Number of query tables on the system	The number of query tables which are deployed on a Business Process Choreographer container does not influence the performance of query table queries. Also, currently, it does not influence the navigation of BPEL process instances, nor does it have impact on claim or complete operations on human tasks. Due to maintainability, keep the number of query tables at a reasonable level. Typically, one query table represents one task list or process list which is displayed on the user interface.
Database tuning	Although optimized SQL is used to access the contents of a query table, database tuning needs to be implemented on a Business Process Choreographer database: Database memory should be set to a maximum, taking into account other processes that are running on the database server, as well as hardware constraints. Statistics on the database must be up-to-date, and should be updated on a regular basis. Typically, those procedures are already implemented in large topologies. For example, collect database statistics for the optimizer once per week in order to reflect changes of the data in the database. Database systems provide tools to reorganize (or defragment) the data containers. The physical layout of the data in a database can also influence query performance and access paths of queries. Optimal indexes are the key for good query performance. Business Process Choreographer comes with predefined indexes which are optimized for both process navigation and query performance of typical scenarios. In customized environments, additional indexes might be necessary in order to support high volume task or process list queries. Use tools provided by the database in order to support the queries which are run on a query table.
Sorting and filtering	To improve the performance of sorting and filtering, you can use the inline custom properties CUSTOM_TEXT1 through CUSTOM_TEXT8. These query table attributes are available for process and task instances and process and task templates. Because inline custom properties are stored in the same query table as the entity that they are associated with, filtering and sorting is more efficient. You can replace some or all of the non-inline custom properties for processes and tasks with inline custom properties.