Efficient search expressions for background searches

In a background search, certain elements of the query must be considered for best performance.

Ordering internal search results by grouped properties

When you create a background search template in the administration console, you can select the Order internal search results by grouped properties property. Doing so causes the underlying database query that is run by Content Platform Engineto include an ORDER BY clause. The properties that are referenced by this clause are the grouped properties (the GROUP BY properties). Usually, this option is not needed since, by default, background search efficiently updates aggregations to the results table during processing of each page of search results.

Restriction: The GROUP BY properties in a background search expression cannot include aggregate or custom search functions in the following circumstances:
  • When the Order internal search results by grouped properties property is selected.
  • When an explicit ORDER BY clause is used in a background search expression. You might want to use the ORDER BY clause to apply a wanted ORDER BY column to the underlying database query for purposes of performance tuning.
In the following example, the system can typically have a few dozen objects for each customer name. If so, it can sometimes help to select the Order internal search results by grouped properties property. Doing so reduces the result table updates for the aggregations that are performed for each processed page during background search. In addition to selecting this property, create a supporting index for the search. 
SELECT CustomerName as CustomerName,
COUNT(Id) as ObjectCount
FROM PurchaseOrders
GROUP BY CustomerName
When the Order internal search results by grouped properties property is selected, create the covering index as indicated by the following example: 
CREATE UNIQUE INDEX I_TN_COVER3
ON (uxyz_CustomerName ASC, object_id ASC)
INCLUDE (security_id, home_id...)

Add the remaining columns from the SELECT and WHERE clause into the index to cover the query. For DB2® and SQL Server databases, add the columns as INCLUDE columns. Name the index appropriately.

Important: As a default rule, avoid the following actions:
  • Selecting the Order internal search results by grouped properties property
  • Adding an explicit ORDER BY clause to the search expression

Following this rule helps to avoid the following problems with large covering indexes: degraded ingestion performance, large storage space requirements, and resource contention. Circumstances that might require an exception to the rule include query plans that result in poor performance or a need for a better index.

Adding an explicit ORDER BY property

In the following example, the search results are for a small date range. To avoid a long running background search, add an ORDER BY property as indicated and create a supporting index.
SELECT CmRpt::FormatDate(DateCreated, 'MM/yyyy')
AS CmRptDate,
COUNT(Id) as CmRptObjectCount
FROM DocVersion
WHERE DateCreated > @StartDate@
AND DateCreated < @EndDate@
GROUP BY CmRptDate
ORDER BY DateCreated
Without the added ORDER BY clause and a supporting database index, the database query plan is one of the following alternatives:
  • A table scan
  • The use of the primary key index to repetitively look up rows followed by applying the range conditions on the main table. This query can take a long time for a large table.
In the following example, a composite index on create_date and object_id suffices if the number of rows that satisfy the date range condition is not large. Alternatively, you can append the other columns in the SELECT and WHERE clauses to make it a covering index as well. The following example shows this alternative: 
CREATE UNIQUE INDEX I_DV_COVER4
ON DocVersion (create_date ASC, object_id ASC)
INCLUDE (security_id, home_id...)
Important: Important: An ORDER BY clause can be appended to the search expression either explicitly or implicitly. (The implicit appendage occurs by selecting the Order internal search results by grouped properties property.) In either case, the ORDER BY clause affects only the internal database query that is run during the background search sweep. It does not affect the search results that are displayed in theAdministration Console for Content Platform Engine. When you view the displayed results, rows are retrieved from the result table by running a paged search, which orders the rows by ID. To view them in a different order, use a different ORDER BY clause in an interactive search against the result class.

Avoid using an asterisk in the COUNT() expression

A background search expression that contains an aggregate function can potentially cause many columns to be added to the SELECT clause in the database SQL query. (The database SQL query is the query that Content Platform Engine sends to the database.) The following example demonstrates such an expression:
SELECT COUNT(*) FROM Document
The alternative is to use the Id property as shown in the following example:
SELECT COUNT(Id) FROM Document