Tutorials > Catalog search > Search Performance Improvement


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.


Procedure

  1. Acknowledge search performance improvement areas.

    For example, create DB2 summary tables that match common queries employed by the search bean on the catalog subsystem. A basic set of summary tables is available and can be created using the Configuration Manager during database schema creation.

    To have a committed enhanced performance improvement, create a summary table instead of using the WebSphere Commerce summary tables.

  2. Acknowledge search limitations.

    The new summary table does not have LONGVARCHAR data types. Any existing LONGVARCHAR fields are divided into substring pieces of summary table fields.

  3. Examine the catalog search features to see how they can fit the requirements.

    For example, to minimize the features to a controlled set of parameters to search on. The Boolean search imposes an OR expression across all fields since there is only one summary table to search against.

  4. Perform a search comparison against all default features provided in WebSphere Commerce.

    For example, the simple search method illustrates a basic example of improving search performance.

  5. Consider and decide which parameters are to be used against a specific search.

    Where:

    resultCatEntryType (String)

    For this example, only products are to be returned in the search result - sending a value of 2 through form values. The code is customized such that this parameter is not needed.

    manufacturer (String)

    The value of this variable is used in a search on a manufacturer name (table columns searched: CATENTRY.MFNAME). This parameter is passed from search to locate products within a manufacturer.

    beginIndex (String)

    This variable is used for paging the result set. The value must be the index of the first result row in a page. This parameter has not been changed from the default representation.

    pageSize (String)

    The value of this variable specifies the number of search result rows to be displayed per page. The value was changed to represent 18 (that is, 18 displayed items per page).

    resultType (String)

    Merchant can specify if they want to show Products or Items or both Products and Items in a search result. The value must be 1 (Items only), 2 (Products only) or 3 (both products and items). The value was restricted to 3 (both products and items).The code is customized such that this parameter is not needed.

    searchTerm (String)

    The value of this variable is used in a search on a word (table columns searched: Catentdesc.name, Catentdesc.Shortdescription, Catentdesc.Longdescription). This parameter has not been changed from the default representation. The code is customized such that this parameter searches the summary table fields instead.

    sku (String)

    The value of this variable is used in a search on SKU (table columns searched: Catentry.Sku). This parameter has not been changed from the default representation. The code is customized such that this parameter searches the summary table fields instead.

    orderBy1 (String)

    Result set is sorted by this attribute. The value was changed to point to a new external RuleQuery class. Base code must be checked to find the extended RuleQuery to point to the summary table field declared in the class reference: 'ENTRYSHORTDESCAttributeInfo'

    coSearchSKUenabled (Boolean)

    Search input terms (one contiguous term only) for descriptions and SKU column. This parameter has not been changed from the default representation. Test using two or more search term value inputs. If more than one search term is entered in the search term parameter, the default representation does not include SKU searches,

    isProduct (Boolean)

    Returns results of 'product data bean' catentry type, which is false by default. This parameter has not been changed from the default representation. The code is customized such that this parameter is not needed.

    isItem (Boolean)

    Returns results of 'item data bean' catentry type, which is false by default. This parameter has not been changed from the default representation. The code is customized such that this parameter is not needed.

    narrowBy (Boolean)

    Returns results of a narrowed search for Categories and Vendors. A new parameter for customized search.

    categoryList (String)

    Passes the user-selected category to filter search results. A new parameter for customized search.

    vendorList (String)

    Passes the user-selected vendor to filter search results. A new parameter for customized search.

    pageIndex (String)

    Passes an index for page size to structure the 10-pages listing per view. When pages exceed 10, only a given 10 pages are shown while the other remaining pages are only provided with a forwarding hypertext link. A new parameter for customized search.

  6. Run tests and output the generated SQL. Dissect this SQL to design the Summary Table.

    An initial test of the search bean is run. Since WebSphere Commerce catalog search is available with all WebSphere Commerce starter stores. The WC_SEARCH component must be enabled to write the appropriate logs. A test search term that is used for this particular tutorial is 'component'. There is a search text input available on the Advanced Search page. From the logs produced as a result of the submission of the search term 'component', show the following output:

    XX:XX:XX.XXX *
    com.ibm.commerce.search.rulequery.RuleQuery.execute(Cursor aCursor)
    Servlet.Engine.Transports : 0 com.ibm.websphere.commerce.WC_SEARCH
      Query = select distinct CATENTRY.CATENTRY_ID, CATENTDESC.SHORTDESCRIPTION
      from CATENTRY, CATENTDESC, CATGPENREL
      where ((((((upper(CATENTRY.PARTNUMBER) like '%COMPONENT%'
                    or  upper(CATENTDESC.SHORTDESCRIPTION) like
    '%COMPONENT%'
                    or  upper(CATENTDESC.LONGDESCRIPTION) like
    '%COMPONENT%'
                    or  upper(CATENTDESC.NAME) like '%COMPONENT%')
            and  CATGPENREL.CATALOG_ID = 10001))
            and  (((CATENTDESC.PUBLISHED = 1
            and  CATENTDESC.LANGUAGE_ID = -1)))))
            and  (CATENTRY.CATENTRY_ID = (CATGPENREL.CATENTRY_ID)
            and  CATENTRY.CATENTRY_ID = (CATENTDESC.CATENTRY_ID)))
            order by CATENTDESC.SHORTDESCRIPTION asc
    

    This SQL is a result of some small customizations already completed from a previous development cycle to prepare this tutorial. The buyable constraint was removed. The preceding SQL statement shows that expected fields to be searched is refreshed into a summary table. The preceding SQL statement is just one example of a number of possible fields. Other fields include the CATGRPDESC table as part of a requirement to search parents of products for a search term match.

  7. Create the summary tables based on the expected fields to be searched against.

    A summary table can be created based on a prediction of which catalog fields to search. For example, the summary table, ITEMSFORPERF:

    CREATE SUMMARY TABLE ITEMSFORPERF
    AS (SELECT STORECENT.STOREENT_ID, CATENTRY.CATENTRY_ID,         UPPER(CATENTDESC.SHORTDESCRIPTION) AS ENTRYSHORTDESC,         UPPER(CATENTRY.PARTNUMBER) AS PARTNUMBER,         UPPER(CATENTRY.MFPARTNUMBER) AS MFPARTNUMBER,         UPPER(CATENTRY.MFNAME) AS MFNAME,         UPPER(CATENTDESC.NAME) AS ENTRYNAME,         UPPER(CATENTDESC.KEYWORD) AS KEYWORD,         UPPER(CATGRPDESC.NAME) AS GROUPNAME,         UPPER(CATGRPDESC.SHORTDESCRIPTION) AS GROUPSHORTDESC
    FROM CATGPENREL CATGPENREL
    LEFT OUTER JOIN CATENTRY CATENTRY ON CATGPENREL.CATENTRY_ID =
    CATENTRY.CATENTRY_ID
    LEFT OUTER JOIN CATENTDESC CATENTDESC ON CATENTRY.CATENTRY_ID =
    CATENTDESC.CATENTRY_ID
    LEFT OUTER JOIN CATGRPDESC CATGRPDESC ON CATGPENREL.CATGROUP_ID =
    CATGRPDESC.CATGROUP_ID
    RIGHT OUTER JOIN STORECENT STORECENT ON CATENTRY.CATENTRY_ID =
    STORECENT.CATENTRY_ID
    WHERE CATENTDESC.PUBLISHED = 1 AND CATENTDESC.LANGUAGE_ID = -1 AND
    CATGRPDESC.LANGUAGE_ID = -1
    AND CATENTRY.CATENTTYPE_ID = 'ProductBean'
    AND CATGPENREL.CATALOG_ID =
    (SELECT CATALOG_ID FROM CATALOG WHERE IDENTIFIER =
    'ConsumerDirect'))
    DATA INITIALLY DEFERRED REFRESH IMMEDIATE NOT LOGGED INITIALLY;
    ALTER TABLE ITEMSFORPERF ACTIVATE NOT LOGGED INITIALLY;
    REFRESH TABLE ITEMSFORPERF;
    

  8. Develop and implement the customization of the search bean to work against each summary table.

    The catalog search fields are in a single table, only one TableInfo class is created for ITEMSFORPERF. There are several new fields searchable within the ITEMSFORPERF table, and therefore, multiple AttributeInfo classes is created for each field with the following naming convention: CATENTRY_IDAttributeInfo, ENTRYSHORTDESCAttributeInfo, PARTNUMBERAttributeInfo, MFPARTNUMBERAttributeInfo, MFNAMEAttributeInfo, ENTRYNAMEAttributeInfo, KEYWORDAttributeInfo, GROUPNAMEAttributeInfo, GROUPSHORTDESCAttributeInfo. The RuleQuery class must be extended to map these new attributeInfo class references. Also, some extra methods must override to control the orderBy clause. An entry is not required in search.xml file since we are only searching within one table for optimal performance improvement.

    The class CatEntrySearchListDataBean must be extended to revamp the SQL to map to the new summary table. The execute method overrides references to product type constraints. The summary table already has the product types embedded within it. This tutorial must override the method setPredefinedAttributes. Constuct the summary table SQL reference for this search bean for the DB2 database. Therefore, if the database type is DB2 (Apache Derby), then the method setPredefinedAttributes of the class CatEntrySearchListDataBean is used instead to call the deftault tables. Otherwise, the summary table ITEMSFORPERF is referenced and the setPredefinedAttributes overrides all table and field references. All references to q.addFilterAttribute method include the new AttributeInfo classes instead of the default AttributeInfo class references.

  9. Run tests and output the generated SQL. An initial test of the search bean is run. Use a WebSphere Commerce starter store to run a simple search. The WC_SEARCH component must be enabled to write the appropriate logs. A test search term that is used for this particular tutorial is 'component'. There is a search text input available on the Advanced Search page. From the logs produced as a result of the submission of the search term 'component', see output similar to the following example:

    XX:XX:XX.XXX *
    com.ibm.commerce.search.rulequery.RuleQuery.execute(Cursor aCursor)
    Servlet.Engine.Transports : 0 com.ibm.websphere.commerce.WC_SEARCH
    Query =
            select distinct ITEMSFORPERF.Catentry_id, ITEMSFORPERF.EntryShortDesc
            from ITEMSFORPERF
            where ((((((
                    ITEMSFORPERF.PartNumber like '%COMPONENT%'
                    or ITEMSFORPERF.EntryShortDesc like '%COMPONENT%'
                    or  ITEMSFORPERF.EntryName like '%COMPONENT%')
                    or  ITEMSFORPERF.Keyword like '%COMPONENT%')
                    or (ITEMSFORPERF.GroupShortDesc like '%COMPONENT%'
                    or  ITEMSFORPERF.GroupName like '%COMPONENT%')))))
            order by ITEMSFORPERF.EntryShortDesc asc
    

    The preceding SQL statement has some performance improvements compared to catalog search bean SQL. There is only one table being searched for this customized instance.

  10. Measure performance improvements. The performance improvements can be measured by running the SQL statements directly on the database apart from the application. This provides true performance results at the database level without confounding variables of application constraints.

    Some tools are available that stress test the server and can provide feedback on application response to multiple users. With stress feedback in place, the searching can be verified against the customer performance requirements.

  11. Refresh the summary tables.

    The summary tables that are in place must be refreshed. The DB2 summary table IMMEDIATE clause allows the summary tables to be updated automatically. The REFRESH TABLE command itself might impose memory usage on the database (for example, tempspace). Therefore, running the REFRESH TABLE command during offline periods outside of peak traffic times is best.


Related tasks

Tutorial: Search Performance Improvement

Tutorial: Search performance improvement for Apache Derby


+

Search Tips   |   Advanced Search