Tutorial: Search Performance Improvement
Attention: This tutorial is currently under revision. The content might contain errors or inaccuracies. Subscribe to this page to be notified when an updated version is available.
The IBM WebSphere Commerce Catalog Search is a lightweight bean that enables you to implement a search tool for the customers. By adding a simple interface to any Java Server Pages (JSP) file in the site, you provide the customers an additional way to locate the 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 the 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 check box.
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.
DB2 Database 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. DB2 offers functionality that would refresh the summary table with source data automatically or by using a scheduled command.
Requirements
To enable this tutorial, we will need to have Toolkit installed and connected to a database, either Apache Derby or DB2. For iSeries, we will need to have the Commerce Server connected to the iSeries DB2 Database. You must have the ConsumerDirect sample store enabled on the environment. Install the Workspace code and JSP file into the workspace within the Toolkit or Server environment. Then follow one of the three references below depending on which database you connect to.
- Tutorial: Search performance improvement for Apache Derby
In this tutorial we will create a unique table to enable a single WebSphere Commerce type of search. The example cannot be directly reused in every WebSphere Commerce solution. However, the basic design elements can be followed for customizing for any solution. The tutorial is provided in an 11-step program format. This format provides shows you a how to approach search implementations and customization. - Tutorial: Search performance improvement
In this tutorial, you create a unique summary table that permits a single search type. The example cannot be directly reused in a WebSphere Commerce solution; however, you can follow the basic elements. The tutorial is provided in an 11-step program format. This format provides a high-level format on how to approach future search implementations and customization.
Related tasks
Tutorial: Search performance improvement for Apache Derby
Tutorial: Search performance improvement