The catalog search feature implements what is known as a vertical schema search. That is, it searches it searches through tables in which data is stored 'vertically' in name/value pairs, rather than horizontally in predefined columns. This flexibility impacts performance to some degree though as the data is not optimized for searching. Generally, the simple catalog search should not perform appreciably slower than you would expect for a simple text string search. Most customers will not notice any significant delay between launching their search and obtaining results, except those delays caused by common Internet traffic. The advanced catalog search may perform noticeably slower. Due to the complex SQL queries that are generated by the underlying databean, the searches can take considerably longer. The response times should not make usage of the feature prohibitive in most cases, but your customers should be aware that there are many factors which can adversely affect performance. Response time will increase accordingly with the size of your database, number of attributes, result set size, database configuration, hardware, memory, among other factors.
Note: It is strongly recommended that you test drive your queries before putting into a production environment.
Fortunately, there are some techniques that you can employ to improve the catalog search performance:
- Create DB2 summary tables which match common queries employed by the search databean on the catalog subsystem. A basic set of summary tables is available and can be created using the Configuration Manager during database schema creation.
- Creating summary tables prior to populating data can slow the population rate down by as much as 90%. When using large data sets, we recommend deferring 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.
- Search constraints which include rich attributes slow down performance. This degradation will be more noticeable if using more than 3 or 4 rich attribute constraints.
- Searching according to list price, stored in a single table, is much faster than searching according to standard price, which is stored in a summary table which summarizes many tables.
- Avoid searching on large datatypes (greater than 1KB) such as DB2 LONGVARCHAR.