Search configuration parameters allow you to specify additional parameters to optimize the performance of the catalog search and Product Advisor. 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 built by the catalog search bean and Product Advisor.
- Upper threshold for number of pre-selected values
- The SQL statements constructed by the catalog bean may have sub-queries. These sub-queries can be either pre-executed (and cached) or executed as part of a main query. Typically, a query that contains sub-queries performs better when the sub-queries are pre-executed and the results are merged with the main query using the IN clause. However, if the merged query exceeds the maximum query length limit (64KB), an exception will be thrown by the database engine.
The value specified in this field defines the maximum number of result that a pre-executed sub-query can return. If a pre-executed sub-query returns a number of values greater than this number, the sub-query statement is used in the primary query, instead of the results of the pre-executed sub-query. This field accepts a number between 0 and 1024. The default value of this field 0, which indicates that all sub-queries are executed as part of the primary query.
In catalogs with large numbers of products and items in any particular category, pre-executed sub-queries can negatively impact performance, and should therefore be avoided.
Note: Only catalog search uses this value for optimizing its queries.
- 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 deselected. This is deselected by default.
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 and Product Advisor will use ASTs to improve the performance of search queries. For more detail on AST see DB2 help.
- 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 is deselected 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 is deselected by default.
- Rich Attribute summary table with Category Group
- Select this check box to create a summary table of the rich attribute data 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 is deselected by default.
Note: You can delete or recreate a summary table anytime by clearing or selecting the appropriate check box.