Catalog search performance

The Catalog Search feature uses what is known as a vertical schema search. A vertical schema search searches through tables in which data is stored vertically in name/value pairs, rather than through data stored horizontally in predefined columns. This flexibility impacts performance to some degree because the data is not optimized for searching. The simple catalog search will perform about as fast as a simple text string search and customers will not experience a significant delay between launching a search and obtaining results, except those caused by common Internet traffic constraints.

Advanced catalog searches may be considerably slower due to the complex SQL queries that are generated by the underlying data bean. In most cases, the response times are not prohibitive but you may want to alert your customers that there are factors that can adversely effect performance, such as database size and configuration, number of search attributes, number of results, hardware, and memory.

Note: It is strongly recommended that you try your queries before putting them into a production environment.

Use the following techniques to improve Catalog Search performance:

Populate your data prior to creating summary tables. The data population rate can be slowed by as much as 90% if summary tables are created in advance. When using large data sets, defer the creation of summary tables until after the data population is completed on the base tables. Larger or additional primary and secondary DB2 log files may be needed to accommodate creation of summary tables in this manner.
Do not include rich attributes in search constraints or catalog search performance will slow down considerably; especially if you use more than three rich attributes in the search.
To increase speed, perform searches according to attributes that are stored in a single table, such as list price, rather than searching attributes that are stored in summary tables, such as standard price. Summary table searches take longer because they contain data from multiple tables.
Avoid searching on large data types (greater than 1KB), such as DB2 LONGVARCHAR.
Note that a wild card at the beginning of a search string causes a full table scan, or a full index scan in the database. Therefore, the response time of a search beginning with a wild card is significantly slower than a search that contains a wildcard in a position other than the beginning. For example, the query *ABC is slower than either of these queries: ABC* or ABC.
Perform database query using the UR (Uncommitted Read) option in DB2. In the WC_installdir/instances/instanceName/xml/instanceName.xml instance configuration file, add to the SQLInformation node a DB2QueryWithUR attribute. The SQLInformation node is a child node of the SearchConfiguration node. For example, the DB2QueryWithUR attribute entry is shown in bold:
```
<SearchConfiguration>
		<SQLInformation DB2QueryWithUR="true" ProductsOnlyInCatgpenrel="false" 
		SubSelectRSLength="0" display="false"/>
		<ASTInformation display="false" richAttributeAST="false"
		richAttributeWithCategoryAST="false" standardPriceAST="false"/>
		<HistogramInformation display="false" enabled="false"/>
</SearchConfiguration>
```