Example of database indexing

Edit online

This topic provides a database index suggestion for ICN performance tuning. It was taken from the ICN documentation and did help to improve the response time for a long-running transaction in the ICN performance workload case study.

The ICN product documentation (topic Creating database indexes to improve performance) makes this recommendation:


"For a large IBM FileNet P8 repository, that is slow or times out too quickly 
when opening the all searches view, create a database index on the table named 
Docversion. The index is for the database table Docversion on the 
*_Searchtype column."

Here is an example of how the above tuning recommendation was implemented:

This DB2® searchtype command was used:


> db2 "describe table docversion" | grep -i searchtype
	UBE06_SEARCHTYPE               SYSIBM    INTEGER              4     0 Yes

This CREATE INDEX command was used:


-> CREATE INDEX DOC_SEARCH_TYPE_IDX ON DOCVERSION (UBE06_SEARCHTYPE)

After creating a database index related to the SQL query for the transaction in question (as shown in the above example), the response time decreased from 7000 to 200 milliseconds. This represented an improvement by a factor of 35. It also showed how database indexes can boost the performance for SQL queries that without an index, are expensive in terms of response time.

Long-running or CPU-expensive transactions can go back to a missing database index. It is worth taking time to analyze the SQL statements that belong to a transaction.

For example, you can use the db2top utility to identify long-running or CPU-expensive SQL statements.

In this case study, a full table scan was done when the problem transaction was executed. The solution was to create an additional database index on the FNP8 Object Store table Docversion.

Figure 1 shows the CPU loads on the ECM database server with and without the additional database index applied.

This graphic shows the CPU loads on the ECM database server with and without the application of the additional database index. — Figure 1. CPU loads on ECM database server with and without the additional database index applied

The ICN transaction with the high response time is executed during the virtual user rampup phase of the performance test. The virtual users are continuously login into ICN and start to execute a transaction mix. Every new virtual user executes the problem transaction onetime. Without the additional database index applied the CPU load on the database server is quickly at 50 to 60 % for 8 CPUs.

Repeating the same test run with the additional index applied the CPU load is only at 10-20%. The additional index does not only help to improve the transaction response time but also helps to save useful CPU capacity on the database server.