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.
Note:
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