Marketing searches that use the CampaignItemManager.Search method take a long time when the Contains member is specified for the ExplicitComparisionOperator enumeration in Commerce Server 2009

Article translations Article translations
Article ID: 980075 - View products that this article applies to.
Expand all | Collapse all

On This Page

SYMPTOMS

Consider the following scenario:
  • You have a computer that is running Microsoft Commerce Server 2009.
  • You use the CampaignItemManager.Search method on a Commerce Server site to search the marketing database and to retrieve campaign items.
  • In the CampaignItemManager.Search method, you use the Contains member that is in the ExplicitComparisonOperator enumeration.
  • The marketing database contains many campaign items.
In this scenario, search results take a long time to be returned.

Additionally, you experience poor performance when you visit a Commerce Server site that uses the CampaignItemManager.Search method.

Note The Contains member tests whether a property contains the value of a specified argument.

CAUSE

This issue occurs because the Contains member maps to the LIKE operator in the Transact-SQL statement. This behavior causes the poor performance of the SQL query that is generated when the CampaignItemManager.Search method is called.

RESOLUTION

After you apply the hotfix, a new member is added to the ExplicitComparisonOperator enumeration under the Microsoft.CommerceServer namespace. This new member is the ContainsItem member. The ContainsItem member tests whether a property contains the value of a specified argument. The ContainsItem member maps to the CONTAINS operator in Transact-SQL.

Additionally, a new full-text index is created in the mktg_creative_property_value table of the marketing database. Therefore, when you use the CampaignItemManager.Search method on a Commerce Server site together with a ContainsItem member to construct a search statement, the performance is improved.

Notes
  • The ContainsItem member can be used only on values that are mapped to columns of the full-text index in the database table.
  • The ContainsItem member supports space that is included in the input string. For example, you create a search clause that resembles the following:
    
    SearchClause search = discountSearchFactory.CreateClause (ExplicitComparisonOperator.ContainsItem, "Name", "Discount F")
    
    
    When you use the CampaignItemManager.Search method together with this search clause, the method may return a "Discount Fall" value. However, the method does not return any of the following values:
    • Discount
    • DiscountFall
    • DiscountSummer

Hotfix information

A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing the problem described in this article. This hotfix might receive additional testing. Therefore, if you are not severely affected by this problem, we recommend that you wait for the next software update that contains this hotfix.

If the hotfix is available for download, there is a "Hotfix download available" section at the top of this Knowledge Base article. If this section does not appear, contact Microsoft Customer Service and Support to obtain the hotfix.

Note If additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or to create a separate service request, visit the following Microsoft Web site:
http://support.microsoft.com/contactus/?ws=support
Note The "Hotfix download available" form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.

Prerequisites

You must have Commerce Server 2009 installed to apply this hotfix.

Restart requirement

You do not have to restart the computer after you apply this hotfix. However, you must restart Internet Information Services (IIS) after you apply this hotfix. To restart IIS, at a command prompt, type the following command, and then press ENTER:
IISRESET /RESTART
Note This option stops and then restarts all IIS services that are running.

Hotfix replacement information

This hotfix does not replace any other hotfixes.

File information

The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time item in Control Panel.
Collapse this tableExpand this table
File nameFile versionFile sizeDateTimePlatform
Cs2009hotfixhelper.exe6.0.4171.2112,13625-Feb-201010:49x86
Microsoft.commerceserver.marketing.datamanagement.dll6.0.4171.21518,05625-Feb-201010:49x86
mktg_schema.sqlNot applicable326,76104-Feb-201011:10Not applicable
Microsoft.commerceserver.crosstiertypes.dll6.0.4171.21190,35225-Feb-201010:57x86

Installation instructions

After you extract the hotfix package, you obtain the following files:
  • CommerceServer2007SP3-KB980075-ENU.exe
  • KB980075_patch_mktg_creative_property_value.sql
  • Hotfix.txt
To install this hotfix, follow these steps:
  1. Install the CommerceServer2007SP3-KB980075-ENU.exe.

    Note This file adds a new member to the ExplicitComparisonOperator enumeration. This new member is the ContainsItem member. Additionally, a full-text index is created in the mktg_creative_property_value table for new marketing databases.
  2. Open the SQL Server Management Studio management tool.
  3. Open the marketing database for each Commerce Server site.
  4. Run the SQL script from the KB980075_patch_mktg_creative_property_value.sql file. This script creates a new full-text index in the mktg_creative_property_value table.
  5. Restart IIS so that the update takes effect. To restart IIS, run the following command:
    IISRESET /RESTART
IMPORTANT To successfully execute the steps, you must have SQL Server Full-Text Search Component installed and the associated Full-Text Service enabled.

Known issue

After you uninstall this hotfix for Commerce Server 2009, both "Commerce Server 2009" and "Commerce Server 2007" appear on the Start menu. You can safely ignore or delete the "Commerce Server 2007" menu item.

STATUS

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

MORE INFORMATION

This hotfix includes two DLL updates. If you want to uninstall this hotfix, you must remove the following two entries by using the Add or Remove Programs item in Control Panel.
  • Microsoft Commerce Server 2007 Client Tools Service Pack 3 (SP3) - KB980075
  • Microsoft Commerce Server 2007 Enterprise Edition SP3 - KB980075
For more information about the CampaignItemManager.Search method, visit the following Microsoft TechNet Web site:
Search methods in the CampaignItemManager class
For more information about the ExplicitComparisonOperator enumeration, visit the following Microsoft Developer Network (MSDN) Web site:
The ExplicitComparisonOperator enumeration
For more information about mktg_creative_property_value table, visit the following Microsoft TechNet Web site:
The mktg_creative_property_value table
For more information about the CreateClause method, visit the following MSDN Web site:
About the CreateClause method (ExplicitComparisonOperator, String, Object)
For more information about the LIKE operator, visit the following MSDN Web site:
The LIKE (Transact-SQL) operator
For more information about the CONTAINS operator, visit the following MSDN Web site:
The CONTAINS (Transact-SQL) operator

Properties

Article ID: 980075 - Last Review: November 30, 2010 - Revision: 2.0
APPLIES TO
  • Microsoft Commerce Server 2009 Enterprise
  • Microsoft Commerce Server 2009 Standard
Keywords: 
kbautohotfix kbexpertiseadvanced kbsurveynew kbqfe KB980075

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com