You can tune paged searches, create supporting indexes, and index the GROUP BY property in the table before the background search is started.
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 FileNet P8 queries and Not every property needs to be indexed. For information about indexing, see the following technote: Indexing for IBM FileNet P8 Content Engine Searches.
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.
SELECT Creator as CmRptUser,
COUNT(Id) AS CmRptObjectCount
FROM Document
GROUP BY CmRptUser
SELECT creator, object_id,
object_class_id,security_id, ...
FROM DocVersion
WHERE object_id > ?
ORDER BY object_id
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. |
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
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.
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.
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.