Configuration Manager - Commerce - Catalog Search Configuration

You can specify search configuration parameters to optimize the performance of catalog search. Search configuration parameters are broadly classified as query optimization parameters and schema optimization parameters.
Query optimization parameters
Query optimization parameters are used to optimize the SQL statements that are built by the catalog search bean.
Upper threshold for number of pre-selected values
The SQL statements that are constructed by the catalog bean might have subqueries. These subqueries can be either pre-executed (and cached) or executed as part of a main query. Typically, a query that contains subqueries performs better when the subqueries are pre-executed and the results are merged with the main query by using the IN clause. However, if the merged query exceeds the maximum query length limit (64 KB), an exception is thrown by the database engine.

The value that is specified in this field defines the maximum number of results that a pre-executed subquery can return. If a pre-executed subquery returns a number of values greater than this number, the subquery statement is used in the primary query, instead of the results of the pre-executed subquery. This field accepts a number 0 - 1024. The default value of this field 0, which indicates that all subqueries are executed as part of the primary query.

Avoid pre-executing subqueries for catalogs that have many products and items in a particular category. If you do, you might encounter performance issues.

Category group relationship table contains products only
Select this check box if your catalog group relationship (CATGRPENREL) table contains only products. If your CATGRPENREL table contains information about both products and items, leave this check box cleared.

The SQL generated by search components takes advantage of data in the CATGPENREL table to improve search performance. Incorrect information in the CATGPENREL table results in either incorrect results or poor performance, or both.

Schema optimization parameters
Using the schema optimization parameters, you can create pre-computed joins or Automated Summary Tables (ASTs). The catalog search bean uses ASTs to improve the performance of search queries. For more information about ASTs, see the DB2 documentation.
Standard Price summary table
Select this check box to create a summary table of the standard price data in your catalog. This summary table is optional and is applicable if your site uses standard price instead of list price, and you choose to bias these tables for database reads over updates. This box is cleared by default.
Rich Attribute summary table without Category Group
Select this check box to create a summary table of the rich attribute data in your catalog. This summary table is optional and is applicable if your site offers vertical schema searches across multiple category groups and you choose to bias these tables for database reads over updates. This box is cleared by default.
Rich Attribute summary table with Category Group
Select this check box to create a summary table of the rich attribute data that is constrained by category groups in your catalog. This summary table is optional and is applicable if your site offers vertical schema searches within category groups and you choose to bias these tables for database reads over updates. This box is cleared by default.
Note: You can delete or re-create a summary table anytime by clearing or selecting the appropriate check box.