You want to access a small percentage of the rows in a table, such as less than 5% for a small table and less than 15% for a larger table. Some of the stock IBM Business Process Manager tables can be accessed with a rather large amount of data being retrieved. So, that is why it is important to do it on a per custom index basis.
- The index itself can be used to answer the query. For example, it is advantageous to use the primary key index to answer the "select count(*) from T" query using a fast full index scan. It is advantageous because the index is generally many times smaller than the table itself.
Indexes can be a bother when you have over-indexed the table. For example, you have an index on (a,b,c) and (a,b) and (a) or indexes that are never used. Extraneous indexes slow down the database manipulation language (DML) operations of an insert, update, or delete operation unnecessarily.
Indexes are only used to speed up the search for a matching field within the records. It stands to reason that using indexing fields only for output are a waste of disk space and processing time when doing an insert or delete operation. Thus, they should be avoided. Also, given the nature of a binary search, the cardinality or uniqueness of the data is important. Indexing on a field with a cardinality of 2 splits the data in half. Whereas, a cardinality of 1,000 returns approximately 1,000 records. With such a low cardinality, the effectiveness is reduced to a linear sort, and the query optimizer avoids using the index if the cardinality is less than 30% of the record number, which effectively makes the index a waste of space.
Consider the following information when you are determining whether to use database indexes with IBM Business Process Manager:
All applications are different. This fact is why we tend to be conservative at installation time. We only create indexes that we think will benefit all applications. It is not abnormal for a bit of index customization to help an application. If the database design tools recommend creating new indexes to improve query response time, I would recommend that you take that advice. However, I suggest verifying that the indexes provide a benefit using measurements in your lower regions before moving them up to production.
There are costs at run time to maintaining the indexes. Usually, the savings in query response time justify the costs at insert or update operation time. However, I occasionally see the Advisor tools recommend new indexes that provide only marginal benefit when I actually measure them against my application. In these cases, I tend to remove the index from regular use. On general principle, I run with the smallest number of indexes I need to get the best results.
Your database administrator would be the best person to advise you on what indexes he or she believes your IBM Business Process Manager environment would benefit most from because, in most of the cases, it's very application or solution dependent.
Make sure to keep a good list of those custom indexes and temporarily remove them before applying any upgrade. This approach is recommended so that any database modification phase in the upgrade process does not fail because it wants to put an index on something that already has an existing custom index.
Keep in mind that if you report a product problem, IBM Support might ask you to remove any custom indexes (temporarily, at least) as part of troubleshooting or fault isolation processes.
- Make sure to keep your database in a good shape by performing regular cleanups of completed instances and tasks. For more information, see the Data querying takes a long time and process server database tables are using too much disk space with WebSphere Lombardi Edition (WLE) and the IBM Business Process Manager (BPM) products technote. It explains the technique that can be used "as is" or you can use it to create your own custom stored procedure based on your cleanup requirements. It is important to perform this task on regular basis; otherwise, the stock IBM Business Process Manager queries response times will increase and additional custom indexes can only make it worse.
Sergii Malynovskyi, who is based Kyiv, Ukraine, is a Team Lead for the WebSphere Lombardi Edition and IBM Business Process Manager Level 2 Support Team.
We encourage you to leave feedback on this article below.