FIX: Catalog caching is not used when you use the CategoryConfiguration class together with the CategoryConfiguration.ChildProducts.SqlWhereClause property in Commerce Server 2009

Symptoms

Consider the following scenario:

• You enable caching in the catalog system in the Web.config file for a website that is running Microsoft Commerce Server 2009.

• You use the CategoryConfiguration class to specify the preloaded configuration of a Category object.

• You set the LoadChildProducts property to True.

• You specify a filtering clause in the CategoryConfiguration.ChildProducts.SqlWhereClause property.

• You use the GetCategory method or the GetProduct method to retrieve child categories or child products of the CategoryConfiguration object.

In this scenario, catalog caching may not be used to retrieve the child categories and child products. Therefore, the website may experience decreased performance when there is high load.

For example, you have a website that uses the AdventureWorks sample catalog. You run the following code example to retrieve the display name of a Category object. In this example, the code queries data from the Catalog database every time, although catalog caching is enabled.

CategoryConfiguration categoryConfiguration = new CategoryConfiguration();

  categoryConfiguration.LoadChildProducts = true;

  categoryConfiguration.ChildProducts.SqlWhereClause = "[IsVisible] = 1";

  var catalogContext = CommerceContext.Current.CatalogSystem.CatalogContext;

  Category category = catalogContext.GetCategory("AdventureWorks Catalog", "SleepingBags");

  Response.Write(category.DisplayName);

Note This same problem occurs when you use the GetProduct method in the code example.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Resolution

To resolve this problem, apply this hotfix to the server that is running Commerce Server 2009.

After you apply this hotfix, caching child products and child categories in the catalog cache is allowed when you use a "whereClause" filter. By default, any query that contains a where clause bypasses the catalog cache. In order to enable caching, you must add a "cacheFilter" section and configure it correctly in the Web.config file for the Commerce Server website.

The "cacheFilter" section provides the following attributes:

• The "whereClause" attribute.

• The "exactMatch" attribute.

You can use one of the following options to manage which where clause to cache:

• Specify the entire where clause in the "whereClause" attribute, and then set the "exactMatch" attribute to True.

• Specify a where clause in the "whereClause" attribute, and then set the "exactMatch" attribute to False. This enables Commerce Server to cache any where clause that has a partial match to what was specified in the configuration file.

Note The "exactMatch" attribute is optional. By default, if you omit the "exactMatch" attribute, it is set to True.

The following example shows how to modify the configuration section. This example also shows the usage of the "exactMatch" attribute.

<catalog>

<catalogSets .../>

      <cache enable="true">

        <cacheFilter>

          <add whereClause="WHERE [ListPrice] between 0 AND 1000'" />

          <add whereClause="isActive" exactMatch="False" />

          ... 

        </cacheFilter> 

      </cache> 

</catalog>

Rules in defining the "whereClause" attribute value in the cacheFilter section

Note The value of the "whereClause" attribute in the "cacheFilter" section is string-matched. For example, the following rules must be followed:

• Only one white space is allowed among the "whereClause" attribute value keywords. For example:

  • whereClause="ProductID like 'AW2%'" is not matched

  • whereClause="ProductID like 'AW2%'" is matched

• No white space(s) is allowed at the beginning or end of the "whereClause" attribute value if there is a multiple keywords value. For example:

  • whereClause=" ProductID like 'AW2%'" is not matched

  • whereClause="ProductID like 'AW2%' " is not matched

• The singled-quoted sub-value within the "whereClause" attribute must be fully string-matched. For example, assume that the search query is "ProductID like 'AW2%'"; in this case,

  • whereClause="ProductID like 'AW2' is not a match

  • whereClause="ProductID like 'AW2%' is a match

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

More Information

Please be aware of the following two points during and after the uninstallation of the hotfix:

1. A dialog window may be displayed. The dialog window warns you that some applications need to be closed before you can continue with the uninstallion process. In this situation, accept the default selection and click the OK button to continue.

2. After you uninstall the hotfix for Commerce Server 2009, both Commerce Server 2009 and Commerce Server 2007 may appear in the Start menu. In this situation, you can ignore or delete the Commerce Server 2007 menu item.
   

For more information, visit the following Microsoft Developer Network (MSDN) websites:

catalog Element

CategoryConfiguration Class

GetCategory Method

To download Commerce Server 2009 Template Pack for SharePoint 2010, visit the following Microsoft website:

http://www.microsoft.com/download/en/details.aspx?FamilyID=CFD0C459-5A11-4DE6-9085-52D59E8D38E6&displaylang=en