Basic query tuning of background searches

You can tune paged searches, create supporting indexes, and index the GROUP BY property in the table before the background search is started.

Tuning paged searches

Background searches are performed as Content Platform Engine paged (continuable) searches. As such, they naturally benefit from basic query tuning for paged searches. For information about query tuning, see Tuning IBM Content Cortex queries and Not every property needs to be indexed. For information about indexing, see the following technote: Indexing for IBMContent Cortex Content Engine Searches External link opens a new window or tab.

Creating supporting indexes

Background search queries that are run as background sweep processes within the Content Platform Engine. Because these queries tend to run for a relatively long time to aggregate data or perform other time-consuming tasks, the required performance tuning is similar to the tuning for policy-based sweeps and job sweeps. For information about sweep tuning, see Creating an index to improve sweep performance.

Sweep tuning can sometimes require the creation of covering indexes, unless sequential IDs are enabled by default, which is the case for Content Platform Engine Version 5.2.1 or later. Using sequential IDs means that the newly created objects are inserted into the database table according to the natural ordering of GUIDs in the database. The rows in the main table are therefore stored on disk in the same order as the key indexes that point to those rows. This alignment leads to faster key lookups for tables that do not already have a clustered index. Therefore, paged sweep queries might not require a covering index under the following circumstances:
  • The table contains only objects that were newly created in Content Platform Engine Version 5.2.1 or later.
  • The queries have no other selective and indexed predicates.
  • The queries order by ID.
For more information about this feature, see the documentation for the object store UseSequentialIds property. Example 1 shows a covering index:
  • Background search query SQL
    SELECT Creator as CmRptUser,
COUNT(Id) AS CmRptObjectCount
FROM Document
GROUP BY CmRptUser
  • Corresponding database SQL that is run during paging
    SELECT creator, object_id,
object_class_id,security_id, ...
FROM DocVersion
WHERE object_id > ?
ORDER BY object_id
Content Platform Engine adds the ORDER BY object_id clause because background searches are run as continuable (paged) searches. The object_id >? condition is also added for queries after the first page. For DB2®, create a covering index similar to the following example: 
CREATE UNIQUE INDEX I_DV_COVER1
ON DocVersion (object_id ASC)
INCLUDE (creator, object_class_id, security_id, home_id...)
Include all of the columns in the SELECT and WHERE clause so that the index covers the query. Performance tuning and sweep tuning have the following similarities:
Type of table Description
Oracle The columns must be part of the index itself. INCLUDE syntax is not supported.
SQL Server The INCLUDE syntax is supported. However, because DocVersion has a clustered index in this example, the covering index is not needed.
Some tables (including DocVersion on SQL Server) Content Platform Engine uses a clustered index on object_id. If a clustered index is used on the table that you want to search, a covering index might not be needed.
Tip: Combine similar indexes whenever possible. For example, the columns in the covering index for a sweep job might overlap columns in a supporting index for a background search query. Also, the supporting index for one background search query might be similar to the supporting index for another query. In such cases, you can drop the similar indexes and add a single covering index that includes the columns that are needed by both searches. In most cases, the optimizer is able to scan the single index.

Example 2 uses an index similar to the index that is described in the sweep documentation for sweeps that limit classes. 

SELECT CustomerName as CustomerName,
COUNT(Id) as ObjectCount
FROM ForeignPurchaseOrders
GROUP BY CustomerName
In the following example, the objects of the search class (ForeignPurchaseOrders) might be a small subset of the total objects. If so, create the following covering index (similar to the one that is shown in the sweep documentation):
CREATE UNIQUE INDEX I_TN_COVER2
ON <TableName> (object_class_id ASC, object_id ASC)
INCLUDE (creator, object_class_id, security_id, home_id, ...)

Name the index appropriately. Also, per the previous tip, verify whether the index can be combined with an existing similar index, such as one that is used for a sweep.

Indexing the GROUP BY property in the result table

In example 1, the result table can become large. A large table is typically one that contains more than a few thousand rows. This definition is a consequence of the way that a sweep process functions; each processed page must check to see whether a row exists for the GROUP BY property and update any existing row for the aggregation data. You can improve query performance for a large table by indexing the GROUP BY property in the table before the background search is started.

For example 1, such an index is of the following form:
CREATE index I_UT_DU1
ON UT_CmRptDocumentUsageByUserR (u<xy>_CmRptUser)

You can create this index from the table definition of the result class in the administration console. If multiple GROUP BY properties exist, a composite index can also be used.