Reducing the time needed for advanced searches

Draft comment:
This topic is shared by BAW, CP4BA. Last updated on 2025-03-13 12:15

When you search over large numbers of items, you might need to create an index on any custom user properties that are used in a WHERE condition. If you do not create an index, the search can take a long time because it performs a table scan.

Symptoms

A search that contains a WHERE condition on a user property takes several minutes to complete.

Resolving the problem

To optimize performance of searches, ensure that you have an index that corresponds to each of the most important searches that are run during working hours. However, too many indexes can degrade performance. Therefore, if you have many different ORDER BY conditions or search conditions, create only those indexes that will improve performance for most searches.

Important: If a database contains a large amount of data, performance can be degraded even if you use indexes for searches. For example, searching a database that contains more than 250 KB of data will take a long time even if you use an index.

Tip: In addition to using indexes, you can improve search performance by including conditions to limit the number of rows that are returned by the search.

Typically, the type of searches each system uses varies. You must analyze your common search queries to determine which of the indexes shown in the following examples are correct for your searches.

Example 1

Assume that you have the following search:

SELECT * FROM ECMPerfCase1 WHERE (CmAcmCaseState = 2 AND 
  cmis:createdBy = 'user_2')
ORDER BY CmAcmCaseIdentifier ASC.

If this search involves more than one user and more than 20,000 cases, ask the database administrator to create an index as shown in the following examples.

Tip: The database administrator must use the actual column names for the indexed elements. To obtain the full column names, including the u<xy>_ prefix, issue the describe table command for the container table from any SQL tool.

If the search returns only a few results, use the following index:

create index I_CREATOR_STATE on Container (creator, 
u<xy>_CmAcmCaseState)

If the search returns a large number of matches, sorting by the ORDER BY CmAcmCaseIdentifier element might take too much time. In this situation, use the following index:
```
create index I_CREATOR_STATE_ID on Container (creator, 
u<xy>_CmAcmCaseState, u<xy>_CmAcmCaseIdentifier)
```

Example 2

Assume that you have the following search:

SELECT * FROM <case type> WHERE (CmAcmCaseState = 2 AND 
   cmis:lastModifiedBy = <user_name>) 
ORDER BY CmAcmCaseIdentifier ASC

Ask the database administrator to create the following composite index:

create index I_ModifyUser_STATE_ID on Container (modify_user,
u<xy>_CmAcmCaseState, u<xy>_CmAcmCaseIdentifier)

Example 3

Assume that you have the following search:

SELECT * FROM ECMPerfCase1 WHERE (CmAcmCaseState = 2 AND 
   cmis:createdBy = 'user_2' 
AND CCDM_customername= 'value1') 
ORDER BY CmAcmCaseIdentifier ASC

Ask the database administrator to create the following composite index if:

An index such as the I_CREATOR_STATE_ID index shown in Example 2 is not used in the query plan for this search.
An index such as the I_CREATOR_STATE_ID index is used, but the index scans are still taking a long time.

create index I_CNAME on container (u<xy>_CCDM_customername, creator, 
u<xy>_CmAcmCaseState, u<xy>_CmAcmCaseIdentifier)