Tutorial: Search Performance Improvement
Introduction
The IBM WebSphere Commerce Catalog Search is a lightweight bean that enables you to implement a search tool for your customers. By adding a simple interface to any Java Server Pages (JSP) file in your site, you provide your customers an additional way to locate your products. The IBM WebSphere Commerce Catalog Search bean is lightweight, yet powerful enough to search both vertical and horizontal constraints to locate products.
This tutorial will take you through an advanced scenario of customizing the WebSphere Commerce search capabilities. You will learn the basic path taken to run a customization. The concepts illustrated in this tutorial can be reused to create specialized summary tables in Windows and AIX environments. This tutorial also covers an iSeries scenario for materialized query tables (MQTs). Take notes from the 11-step program presented later in this tutorial because you can reuse it for a wide variety of search customizations. Even using this tutorial to review an out-of-the-box search will give you to have considerable insight into how your catalog data is being queried.
Catalog Search performance
Because of the complex SQL queries that are generated by the underlying data bean, search results when using the search bean may take some time. The response times should not make usage of the feature prohibitively slow in most cases. Response times will increase accordingly with the size of database size, number of attributes, result set size, database configuration complexity, hardware constraints, and memory load, among other factors.
When using the search bean, you can use Commerce out-of-the-box summary tables in order to greatly improve search performance. These summary tables improve searching across rich attribute tables, category references, and pricing. The WebSphere Commerce Suite Configuration Manager allows you to create summary tables for search by simply selecting a checkbox.
Note: Make sure to clear these when doing any mass loading data or publishing a SAR file. Select them again after mass loading data or publishing a SAR file. This will enable summary pages to be updated with the latest data.
Steps:
Create these summary tables with the Configuration Manager to significantly improve the search performance.
- Open Configuration Manager
- Select the Search configuration tab.
- Select/clear the appropriate options.
Standalone Summary Tables
The purpose to even consider using summary tables is to enhance performance of query time in when searching for Commerce data. One single summary table can be designed to represent a vast array of different tables in Commerce. This summary table will then have it's own indexing system augmenting the summarizing of a large number of different tables which otherwise may not be related. Of course, when searching for data, sometimes it is convenient to search across a large number of tables with a simple query. When a simple query in hand, then improved performance is expected.
IBM DB2 Universal Database product (DB2 UDB) provides a feature to augment the implementation of summary tables. This standalone table can contain all the expected fields that a specific store solution would require to search. One type of summary table cannot be generic across all different variations of Commerce solutions. However, a summary table can be designed to meet specific requirements of a specific Commerce solution. With that in place, the search bean would then compile a simple SQL query based on the summary table. IBM DB2 Universal Database product (DB2 UDB) offers functionality that would refresh the summary table with source data automatically or by using a scheduled command.
Requirements
To enable this tutorial, have Toolkit installed and connected to a database, either Cloudscape or DB2. For iSeries, you will need to have your Commerce Server connected to the iSeries DB2 Database. You must have the ConsumerDirect sample store enabled on your environment.
Install the Workspace code and JSP file into your workspace within your Toolkit or Server environment. Then follow one of the three references below depending on which database you connect to.(C) Copyright IBM Corporation 1996, 2006. All Rights Reserved.