Search errors

[jonw2m]

We're seeing a lot of errors on the search page (both desktop and mobile), also some on Admin for an R9 using FTS search (it crashes more often with Lucene or SQL). Trying to replicate the error sometimes works, but sometimes doesn't and we get results without any errors. There are only 18000 products in the store.

Any idea how we can prevent this from happening?

Thank you for your help.

Here is the front end error:

An error has occured at http://www.xxxxxxxx.com/Search.aspx?k=SAM-05
Exception: Exception of type 'System.Web.HttpUnhandledException' was thrown. Stack Trace: at System.Web.UI.Page.HandleError(Exception e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest() at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context) at System.Web.UI.Page.ProcessRequest(HttpContext context) at ASP.search_aspx.ProcessRequest(HttpContext context) at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) Inner Exception: could not execute query [ SELECT TOP (@p0) P.ProductId, P.Name, P.Sku, P.Price, P.MSRP, P.CostOfGoods, P.Weight, P.Length, P.Width, P.Height, P.IsFeatured , MIN(PC.OrderBy) AS OrderBy FROM (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) WHERE PC.CatalogNodeTypeId = 1 AND P.ProductId IN (SELECT DISTINCT P.ProductId FROM ac_Products P WHERE P.StoreId = @p1 AND P.EnableGroups = 0 AND (P.SearchKeywords LIKE @p2 OR P.Name LIKE @p2 OR P.Summary LIKE @p2 OR P.Description LIKE @p2 OR P.ExtendedDescription LIKE @p2 OR P.Sku LIKE @p2 OR P.ModelNumber LIKE @p2) AND P.VisibilityId = 0) GROUP BY P.ProductId, P.Name, P.Sku, P.Price, P.MSRP, P.CostOfGoods, P.Weight, P.Length, P.Width, P.Height, P.IsFeatured ORDER BY Name ASC ] Name:storeId - Value:1 Name:keyword - Value:%SAM-05% [SQL: SELECT TOP (@p0) P.ProductId, P.Name, P.Sku, P.Price, P.MSRP, P.CostOfGoods, P.Weight, P.Length, P.Width, P.Height, P.IsFeatured , MIN(PC.OrderBy) AS OrderBy FROM (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) WHERE PC.CatalogNodeTypeId = 1 AND P.ProductId IN (SELECT DISTINCT P.ProductId FROM ac_Products P WHERE P.StoreId = @p1 AND P.EnableGroups = 0 AND (P.SearchKeywords LIKE @p2 OR P.Name LIKE @p2 OR P.Summary LIKE @p2 OR P.Description LIKE @p2 OR P.ExtendedDescription LIKE @p2 OR P.Sku LIKE @p2 OR P.ModelNumber LIKE @p2) AND P.VisibilityId = 0) GROUP BY P.ProductId, P.Name, P.Sku, P.Price, P.MSRP, P.CostOfGoods, P.Weight, P.Length, P.Width, P.Height, P.IsFeatured ORDER BY Name ASC] Inner Exception Stack Trace: at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters) at NHibernate.Impl.SqlQueryImpl.List() at CommerceBuilder.Search.Providers.SearchProviderBase.AdvancedSearch(String keyword, Int32 categoryId, Int32 manufacturerId, Boolean searchName, Boolean searchDescription, Boolean searchSKU, Decimal lowPrice, Decimal highPrice, Boolean onlyFeatured, IList`1 choices, String sortExpression, Int32 maximumRows, Int32 startRowIndex) at CommerceBuilder.Search.Providers.SqlFtsSearchProvider.AdvancedSearch(String keyword, Int32 categoryId, Int32 manufacturerId, Boolean searchName, Boolean searchDescription, Boolean searchSKU, Decimal lowPrice, Decimal highPrice, Boolean onlyFeatured, IList`1 choices, String sortExpression, Int32 maximumRows, Int32 startRowIndex) at CommerceBuilder.Products.ProductRepository.AdvancedSearch(String keyword, Int32 categoryId, Int32 manufacturerId, Boolean searchName, Boolean searchDescription, Boolean searchSKU, Decimal lowPrice, Decimal highPrice, Boolean onlyFeatured, IList`1 choices, String sortExpression, Int32 maximumRows, Int32 startRowIndex) at CommerceBuilder.Products.ProductDataSource.AdvancedSearch(String keyword, Int32 categoryId, Int32 manufacturerId, Boolean searchName, Boolean searchDescription, Boolean searchSKU, Decimal lowPrice, Decimal highPrice, IList`1 choices, Int32 maximumRows, Int32 startRowIndex, String sortExpression) at AbleCommerce.Search.BindProductList() at AbleCommerce.Search.BindSearchResultsPanel() at AbleCommerce.Search.Page_Load(Object sender, EventArgs e) at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.EventHandler.Invoke(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Inner Exception: could not execute query [ SELECT TOP (@p0) P.ProductId, P.Name, P.Sku, P.Price, P.MSRP, P.CostOfGoods, P.Weight, P.Length, P.Width, P.Height, P.IsFeatured , MIN(PC.OrderBy) AS OrderBy FROM (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) WHERE PC.CatalogNodeTypeId = 1 AND P.ProductId IN (SELECT DISTINCT P.ProductId FROM ac_Products P WHERE P.StoreId = @p1 AND P.EnableGroups = 0 AND (P.SearchKeywords LIKE @p2 OR P.Name LIKE @p2 OR P.Summary LIKE @p2 OR P.Description LIKE @p2 OR P.ExtendedDescription LIKE @p2 OR P.Sku LIKE @p2 OR P.ModelNumber LIKE @p2) AND P.VisibilityId = 0) GROUP BY P.ProductId, P.Name, P.Sku, P.Price, P.MSRP, P.CostOfGoods, P.Weight, P.Length, P.Width, P.Height, P.IsFeatured ORDER BY Name ASC ] Name:storeId - Value:1 Name:keyword - Value:%SAM-05% [SQL: SELECT TOP (@p0) P.ProductId, P.Name, P.Sku, P.Price, P.MSRP, P.CostOfGoods, P.Weight, P.Length, P.Width, P.Height, P.IsFeatured , MIN(PC.OrderBy) AS OrderBy FROM (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) WHERE PC.CatalogNodeTypeId = 1 AND P.ProductId IN (SELECT DISTINCT P.ProductId FROM ac_Products P WHERE P.StoreId = @p1 AND P.EnableGroups = 0 AND (P.SearchKeywords LIKE @p2 OR P.Name LIKE @p2 OR P.Summary LIKE @p2 OR P.Description LIKE @p2 OR P.ExtendedDescription LIKE @p2 OR P.Sku LIKE @p2 OR P.ModelNumber LIKE @p2) AND P.VisibilityId = 0) GROUP BY P.ProductId, P.Name, P.Sku, P.Price, P.MSRP, P.CostOfGoods, P.Weight, P.Length, P.Width, P.Height, P.IsFeatured ORDER BY Name ASC] Inner Exception Stack Trace: at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters) at NHibernate.Impl.SqlQueryImpl.List() at CommerceBuilder.Search.Providers.SearchProviderBase.AdvancedSearch(String keyword, Int32 categoryId, Int32 manufacturerId, Boolean searchName, Boolean searchDescription, Boolean searchSKU, Decimal lowPrice, Decimal highPrice, Boolean onlyFeatured, IList`1 choices, String sortExpression, Int32 maximumRows, Int32 startRowIndex) at CommerceBuilder.Search.Providers.SqlFtsSearchProvider.AdvancedSearch(String keyword, Int32 categoryId, Int32 manufacturerId, Boolean searchName, Boolean searchDescription, Boolean searchSKU, Decimal lowPrice, Decimal highPrice, Boolean onlyFeatured, IList`1 choices, String sortExpression, Int32 maximumRows, Int32 startRowIndex) at CommerceBuilder.Products.ProductRepository.AdvancedSearch(String keyword, Int32 categoryId, Int32 manufacturerId, Boolean searchName, Boolean searchDescription, Boolean searchSKU, Decimal lowPrice, Decimal highPrice, Boolean onlyFeatured, IList`1 choices, String sortExpression, Int32 maximumRows, Int32 startRowIndex) at CommerceBuilder.Products.ProductDataSource.AdvancedSearch(String keyword, Int32 categoryId, Int32 manufacturerId, Boolean searchName, Boolean searchDescription, Boolean searchSKU, Decimal lowPrice, Decimal highPrice, IList`1 choices, Int32 maximumRows, Int32 startRowIndex, String sortExpression) at AbleCommerce.Search.BindProductList() at AbleCommerce.Search.BindSearchResultsPanel() at AbleCommerce.Search.Page_Load(Object sender, EventArgs e) at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.EventHandler.Invoke(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Inner Exception: could not execute query [ SELECT TOP (@p0) P.ProductId, P.Name, P.Sku, P.Price, P.MSRP, P.CostOfGoods, P.Weight, P.Length, P.Width, P.Height, P.IsFeatured , MIN(PC.OrderBy) AS OrderBy FROM (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) WHERE PC.CatalogNodeTypeId = 1 AND P.ProductId IN (SELECT DISTINCT P.ProductId FROM ac_Products P WHERE P.StoreId = @p1 AND P.EnableGroups = 0 AND (P.SearchKeywords LIKE @p2 OR P.Name LIKE @p2 OR P.Summary LIKE @p2 OR P.Description LIKE @p2 OR P.ExtendedDescription LIKE @p2 OR P.Sku LIKE @p2 OR P.ModelNumber LIKE @p2) AND P.VisibilityId = 0) GROUP BY P.ProductId, P.Name, P.Sku, P.Price, P.MSRP, P.CostOfGoods, P.Weight, P.Length, P.Width, P.Height, P.IsFeatured ORDER BY Name ASC ] Name:storeId - Value:1 Name:keyword - Value:%SAM-05% [SQL: SELECT TOP (@p0) P.ProductId, P.Name, P.Sku, P.Price, P.MSRP, P.CostOfGoods, P.Weight, P.Length, P.Width, P.Height, P.IsFeatured , MIN(PC.OrderBy) AS OrderBy FROM (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) WHERE PC.CatalogNodeTypeId = 1 AND P.ProductId IN (SELECT DISTINCT P.ProductId FROM ac_Products P WHERE P.StoreId = @p1 AND P.EnableGroups = 0 AND (P.SearchKeywords LIKE @p2 OR P.Name LIKE @p2 OR P.Summary LIKE @p2 OR P.Description LIKE @p2 OR P.ExtendedDescription LIKE @p2 OR P.Sku LIKE @p2 OR P.ModelNumber LIKE @p2) AND P.VisibilityId = 0) GROUP BY P.ProductId, P.Name, P.Sku, P.Price, P.MSRP, P.CostOfGoods, P.Weight, P.Length, P.Width, P.Height, P.IsFeatured ORDER BY Name ASC] Inner Exception Stack Trace: at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters) at NHibernate.Impl.SqlQueryImpl.List() at CommerceBuilder.Search.Providers.SearchProviderBase.AdvancedSearch(String keyword, Int32 categoryId, Int32 manufacturerId, Boolean searchName, Boolean searchDescription, Boolean searchSKU, Decimal lowPrice, Decimal highPrice, Boolean onlyFeatured, IList`1 choices, String sortExpression, Int32 maximumRows, Int32 startRowIndex) at CommerceBuilder.Search.Providers.SqlFtsSearchProvider.AdvancedSearch(String keyword, Int32 categoryId, Int32 manufacturerId, Boolean searchName, Boolean searchDescription, Boolean searchSKU, Decimal lowPrice, Decimal highPrice, Boolean onlyFeatured, IList`1 choices, String sortExpression, Int32 maximumRows, Int32 startRowIndex) at CommerceBuilder.Products.ProductRepository.AdvancedSearch(String keyword, Int32 categoryId, Int32 manufacturerId, Boolean searchName, Boolean searchDescription, Boolean searchSKU, Decimal lowPrice, Decimal highPrice, Boolean onlyFeatured, IList`1 choices, String sortExpression, Int32 maximumRows, Int32 startRowIndex) at CommerceBuilder.Products.ProductDataSource.AdvancedSearch(String keyword, Int32 categoryId, Int32 manufacturerId, Boolean searchName, Boolean searchDescription, Boolean searchSKU, Decimal lowPrice, Decimal highPrice, IList`1 choices, Int32 maximumRows, Int32 startRowIndex, String sortExpression) at AbleCommerce.Search.BindProductList() at AbleCommerce.Search.BindSearchResultsPanel() at AbleCommerce.Search.Page_Load(Object sender, EventArgs e) at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.EventHandler.Invoke(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

And here's the admin error:

An error has occured at https://www.xxxxxxx.com/Admin/Search.as ... =beadlocks
Exception: Exception of type 'System.Web.HttpUnhandledException' was thrown. Stack Trace: at System.Web.UI.Page.HandleError(Exception e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest() at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context) at System.Web.UI.Page.ProcessRequest(HttpContext context) at ASP.admin_search_aspx.ProcessRequest(HttpContext context) at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) Inner Exception: could not execute query [ SELECT TOP (@p0) P.* , FTS.RANK FROM ac_Products P INNER JOIN (SELECT [KEY], SUM(weightRank) as [RANK] FROM ( SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, Name, @p1) UNION ALL SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, Sku, @p1) UNION ALL SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, ModelNumber, @p1) UNION ALL SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, SearchKeywords, @p1) UNION ALL SELECT [KEY], (RANK * 1.5) AS weightRank FROM CONTAINSTABLE(ac_Products, Summary, @p1) UNION ALL SELECT [KEY], (RANK * 1.0) AS weightRank FROM CONTAINSTABLE(ac_Products, [Description], @p1) UNION ALL SELECT [KEY], (RANK * 1.0) AS weightRank FROM CONTAINSTABLE(ac_Products, ExtendedDescription, @p1) ) AS INNERFTS GROUP BY [KEY]) AS FTS ON FTS.[KEY] = P.ProductId WHERE P.StoreId=@p8 AND CONTAINS((P.Name,P.Sku,P.ModelNumber), @p1) ORDER BY FTS.RANK DESC ] Name:storeId - Value:1 Name:keyword - Value:"beadlocks" [SQL: SELECT TOP (@p0) P.* , FTS.RANK FROM ac_Products P INNER JOIN (SELECT [KEY], SUM(weightRank) as [RANK] FROM ( SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, Name, @p1) UNION ALL SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, Sku, @p1) UNION ALL SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, ModelNumber, @p1) UNION ALL SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, SearchKeywords, @p1) UNION ALL SELECT [KEY], (RANK * 1.5) AS weightRank FROM CONTAINSTABLE(ac_Products, Summary, @p1) UNION ALL SELECT [KEY], (RANK * 1.0) AS weightRank FROM CONTAINSTABLE(ac_Products, [Description], @p1) UNION ALL SELECT [KEY], (RANK * 1.0) AS weightRank FROM CONTAINSTABLE(ac_Products, ExtendedDescription, @p1) ) AS INNERFTS GROUP BY [KEY]) AS FTS ON FTS.[KEY] = P.ProductId WHERE P.StoreId=@p8 AND CONTAINS((P.Name,P.Sku,P.ModelNumber), @p1) ORDER BY FTS.RANK DESC] Inner Exception Stack Trace: at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List[T](NativeSQLQuerySpecification spec, QueryParameters queryParameters) at NHibernate.Impl.SqlQueryImpl.List[T]() at CommerceBuilder.Search.Providers.SqlFtsSearchProvider.Search(String searchPhrase, Boolean includeDescriptions, Int32 maximumRows, Int32 startRowIndex, String sortExpression) at CommerceBuilder.Search.SearchDataSource.a(String A_0, Int32 A_1) at CommerceBuilder.Search.SearchDataSource.Search(String keywords, SearchArea searchArea, Int32 maximumRows) at AbleCommerce.Admin.Search.Page_Load(Object sender, EventArgs e) at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.EventHandler.Invoke(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Inner Exception: could not execute query [ SELECT TOP (@p0) P.* , FTS.RANK FROM ac_Products P INNER JOIN (SELECT [KEY], SUM(weightRank) as [RANK] FROM ( SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, Name, @p1) UNION ALL SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, Sku, @p1) UNION ALL SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, ModelNumber, @p1) UNION ALL SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, SearchKeywords, @p1) UNION ALL SELECT [KEY], (RANK * 1.5) AS weightRank FROM CONTAINSTABLE(ac_Products, Summary, @p1) UNION ALL SELECT [KEY], (RANK * 1.0) AS weightRank FROM CONTAINSTABLE(ac_Products, [Description], @p1) UNION ALL SELECT [KEY], (RANK * 1.0) AS weightRank FROM CONTAINSTABLE(ac_Products, ExtendedDescription, @p1) ) AS INNERFTS GROUP BY [KEY]) AS FTS ON FTS.[KEY] = P.ProductId WHERE P.StoreId=@p8 AND CONTAINS((P.Name,P.Sku,P.ModelNumber), @p1) ORDER BY FTS.RANK DESC ] Name:storeId - Value:1 Name:keyword - Value:"beadlocks" [SQL: SELECT TOP (@p0) P.* , FTS.RANK FROM ac_Products P INNER JOIN (SELECT [KEY], SUM(weightRank) as [RANK] FROM ( SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, Name, @p1) UNION ALL SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, Sku, @p1) UNION ALL SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, ModelNumber, @p1) UNION ALL SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, SearchKeywords, @p1) UNION ALL SELECT [KEY], (RANK * 1.5) AS weightRank FROM CONTAINSTABLE(ac_Products, Summary, @p1) UNION ALL SELECT [KEY], (RANK * 1.0) AS weightRank FROM CONTAINSTABLE(ac_Products, [Description], @p1) UNION ALL SELECT [KEY], (RANK * 1.0) AS weightRank FROM CONTAINSTABLE(ac_Products, ExtendedDescription, @p1) ) AS INNERFTS GROUP BY [KEY]) AS FTS ON FTS.[KEY] = P.ProductId WHERE P.StoreId=@p8 AND CONTAINS((P.Name,P.Sku,P.ModelNumber), @p1) ORDER BY FTS.RANK DESC] Inner Exception Stack Trace: at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List[T](NativeSQLQuerySpecification spec, QueryParameters queryParameters) at NHibernate.Impl.SqlQueryImpl.List[T]() at CommerceBuilder.Search.Providers.SqlFtsSearchProvider.Search(String searchPhrase, Boolean includeDescriptions, Int32 maximumRows, Int32 startRowIndex, String sortExpression) at CommerceBuilder.Search.SearchDataSource.a(String A_0, Int32 A_1) at CommerceBuilder.Search.SearchDataSource.Search(String keywords, SearchArea searchArea, Int32 maximumRows) at AbleCommerce.Admin.Search.Page_Load(Object sender, EventArgs e) at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.EventHandler.Invoke(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Inner Exception: could not execute query [ SELECT TOP (@p0) P.* , FTS.RANK FROM ac_Products P INNER JOIN (SELECT [KEY], SUM(weightRank) as [RANK] FROM ( SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, Name, @p1) UNION ALL SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, Sku, @p1) UNION ALL SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, ModelNumber, @p1) UNION ALL SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, SearchKeywords, @p1) UNION ALL SELECT [KEY], (RANK * 1.5) AS weightRank FROM CONTAINSTABLE(ac_Products, Summary, @p1) UNION ALL SELECT [KEY], (RANK * 1.0) AS weightRank FROM CONTAINSTABLE(ac_Products, [Description], @p1) UNION ALL SELECT [KEY], (RANK * 1.0) AS weightRank FROM CONTAINSTABLE(ac_Products, ExtendedDescription, @p1) ) AS INNERFTS GROUP BY [KEY]) AS FTS ON FTS.[KEY] = P.ProductId WHERE P.StoreId=@p8 AND CONTAINS((P.Name,P.Sku,P.ModelNumber), @p1) ORDER BY FTS.RANK DESC ] Name:storeId - Value:1 Name:keyword - Value:"beadlocks" [SQL: SELECT TOP (@p0) P.* , FTS.RANK FROM ac_Products P INNER JOIN (SELECT [KEY], SUM(weightRank) as [RANK] FROM ( SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, Name, @p1) UNION ALL SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, Sku, @p1) UNION ALL SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, ModelNumber, @p1) UNION ALL SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, SearchKeywords, @p1) UNION ALL SELECT [KEY], (RANK * 1.5) AS weightRank FROM CONTAINSTABLE(ac_Products, Summary, @p1) UNION ALL SELECT [KEY], (RANK * 1.0) AS weightRank FROM CONTAINSTABLE(ac_Products, [Description], @p1) UNION ALL SELECT [KEY], (RANK * 1.0) AS weightRank FROM CONTAINSTABLE(ac_Products, ExtendedDescription, @p1) ) AS INNERFTS GROUP BY [KEY]) AS FTS ON FTS.[KEY] = P.ProductId WHERE P.StoreId=@p8 AND CONTAINS((P.Name,P.Sku,P.ModelNumber), @p1) ORDER BY FTS.RANK DESC] Inner Exception Stack Trace: at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List[T](NativeSQLQuerySpecification spec, QueryParameters queryParameters) at NHibernate.Impl.SqlQueryImpl.List[T]() at CommerceBuilder.Search.Providers.SqlFtsSearchProvider.Search(String searchPhrase, Boolean includeDescriptions, Int32 maximumRows, Int32 startRowIndex, String sortExpression) at CommerceBuilder.Search.SearchDataSource.a(String A_0, Int32 A_1) at CommerceBuilder.Search.SearchDataSource.Search(String keywords, SearchArea searchArea, Int32 maximumRows) at AbleCommerce.Admin.Search.Page_Load(Object sender, EventArgs e) at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.EventHandler.Invoke(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

[mazhar]

I think the reason maybe the keyword "SAM-05 ". Full Text Searches can't search for punctuation marks like - so in order to search a keyword containing - we pass it to SQL search provider. This could increase search time since we are using LIKE operators in SQL provider and if you have lot of data in Summary, Description and ExtendedDescription fields then this query can take longer to complete. This maybe causing timeouts . I think we shouldn't look into these fields when keyword contains - since it would be mostly about SKU.

If you have back end source code then a small fix can be tried in AdvanceSearch and AdvanceSearchCount of SQL FTS Provider where when we pass the query to base SQL provider depending upon keywords you pass false for searchDescripton field. If you don't have back end code then for Search.aspx you can modify the UI logic to pass false to make sure it doesn't look inside Summary, Description and Extended Description when keywords have - in it.

[jonw2m]

Hi Mazhar,

Thank you for your quick answer.

Unfortunately for our client limiting the search is not an option. They want the customer to be able to search in description, summary, etc. The errors I posted first shows searches with "-" but there are errors for searches without punctuation:

An error has occured at http://www.xxxxxxx.com/Search.aspx?k=TOOLBOXES
Exception: Exception of type 'System.Web.HttpUnhandledException' was thrown. Stack Trace: at System.Web.UI.Page.HandleError(Exception e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest() at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context) at System.Web.UI.Page.ProcessRequest(HttpContext context) at ASP.search_aspx.ProcessRequest(HttpContext context) at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) Inner Exception: could not execute query [ SELECT M.ManufacturerId, M.Name, COUNT(DISTINCT P.ProductId) As ProductCount FROM (ac_Products P LEFT JOIN ac_Manufacturers M ON P.ManufacturerId = M.ManufacturerId) WHERE P.StoreId = @p0 AND P.EnableGroups = 0 AND CONTAINS(P.*, @p1) AND P.VisibilityId = 0 GROUP BY M.ManufacturerId, M.Name ] Name:storeId - Value:1 Name:keyword - Value:"TOOLBOXES" [SQL: SELECT M.ManufacturerId, M.Name, COUNT(DISTINCT P.ProductId) As ProductCount FROM (ac_Products P LEFT JOIN ac_Manufacturers M ON P.ManufacturerId = M.ManufacturerId) WHERE P.StoreId = @p0 AND P.EnableGroups = 0 AND CONTAINS(P.*, @p1) AND P.VisibilityId = 0 GROUP BY M.ManufacturerId, M.Name] Inner Exception Stack Trace: at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters) at NHibernate.Impl.SqlQueryImpl.List() at CommerceBuilder.Search.Providers.SqlFtsSearchProvider.AdvancedSearchCountByManufacturer(String keyword, Int32 categoryId, Boolean searchName, Boolean searchDescription, Boolean searchSKU, Decimal lowPrice, Decimal highPrice, IList`1 choices) at CommerceBuilder.Products.ProductRepository.AdvancedSearchCountByManufacturer(String keyword, Int32 categoryId, Boolean searchName, Boolean searchDescription, Boolean searchSKU, Decimal lowPrice, Decimal highPrice, IList`1 choices) at CommerceBuilder.Products.ProductDataSource.AdvancedSearchCountByManufacturer(String keyword, Int32 categoryId, Boolean searchName, Boolean searchDescription, Boolean searchSKU, Decimal lowPrice, Decimal highPrice, IList`1 choices) at AbleCommerce.Search.Page_Load(Object sender, EventArgs e) at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.EventHandler.Invoke(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Inner Exception: could not execute query [ SELECT M.ManufacturerId, M.Name, COUNT(DISTINCT P.ProductId) As ProductCount FROM (ac_Products P LEFT JOIN ac_Manufacturers M ON P.ManufacturerId = M.ManufacturerId) WHERE P.StoreId = @p0 AND P.EnableGroups = 0 AND CONTAINS(P.*, @p1) AND P.VisibilityId = 0 GROUP BY M.ManufacturerId, M.Name ] Name:storeId - Value:1 Name:keyword - Value:"TOOLBOXES" [SQL: SELECT M.ManufacturerId, M.Name, COUNT(DISTINCT P.ProductId) As ProductCount FROM (ac_Products P LEFT JOIN ac_Manufacturers M ON P.ManufacturerId = M.ManufacturerId) WHERE P.StoreId = @p0 AND P.EnableGroups = 0 AND CONTAINS(P.*, @p1) AND P.VisibilityId = 0 GROUP BY M.ManufacturerId, M.Name] Inner Exception Stack Trace: at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters) at NHibernate.Impl.SqlQueryImpl.List() at CommerceBuilder.Search.Providers.SqlFtsSearchProvider.AdvancedSearchCountByManufacturer(String keyword, Int32 categoryId, Boolean searchName, Boolean searchDescription, Boolean searchSKU, Decimal lowPrice, Decimal highPrice, IList`1 choices) at CommerceBuilder.Products.ProductRepository.AdvancedSearchCountByManufacturer(String keyword, Int32 categoryId, Boolean searchName, Boolean searchDescription, Boolean searchSKU, Decimal lowPrice, Decimal highPrice, IList`1 choices) at CommerceBuilder.Products.ProductDataSource.AdvancedSearchCountByManufacturer(String keyword, Int32 categoryId, Boolean searchName, Boolean searchDescription, Boolean searchSKU, Decimal lowPrice, Decimal highPrice, IList`1 choices) at AbleCommerce.Search.Page_Load(Object sender, EventArgs e) at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.EventHandler.Invoke(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Inner Exception: could not execute query [ SELECT M.ManufacturerId, M.Name, COUNT(DISTINCT P.ProductId) As ProductCount FROM (ac_Products P LEFT JOIN ac_Manufacturers M ON P.ManufacturerId = M.ManufacturerId) WHERE P.StoreId = @p0 AND P.EnableGroups = 0 AND CONTAINS(P.*, @p1) AND P.VisibilityId = 0 GROUP BY M.ManufacturerId, M.Name ] Name:storeId - Value:1 Name:keyword - Value:"TOOLBOXES" [SQL: SELECT M.ManufacturerId, M.Name, COUNT(DISTINCT P.ProductId) As ProductCount FROM (ac_Products P LEFT JOIN ac_Manufacturers M ON P.ManufacturerId = M.ManufacturerId) WHERE P.StoreId = @p0 AND P.EnableGroups = 0 AND CONTAINS(P.*, @p1) AND P.VisibilityId = 0 GROUP BY M.ManufacturerId, M.Name] Inner Exception Stack Trace: at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results) at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters) at NHibernate.Impl.SqlQueryImpl.List() at CommerceBuilder.Search.Providers.SqlFtsSearchProvider.AdvancedSearchCountByManufacturer(String keyword, Int32 categoryId, Boolean searchName, Boolean searchDescription, Boolean searchSKU, Decimal lowPrice, Decimal highPrice, IList`1 choices) at CommerceBuilder.Products.ProductRepository.AdvancedSearchCountByManufacturer(String keyword, Int32 categoryId, Boolean searchName, Boolean searchDescription, Boolean searchSKU, Decimal lowPrice, Decimal highPrice, IList`1 choices) at CommerceBuilder.Products.ProductDataSource.AdvancedSearchCountByManufacturer(String keyword, Int32 categoryId, Boolean searchName, Boolean searchDescription, Boolean searchSKU, Decimal lowPrice, Decimal highPrice, IList`1 choices) at AbleCommerce.Search.Page_Load(Object sender, EventArgs e) at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) at System.EventHandler.Invoke(Object sender, EventArgs e) at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Thank you,

[mazhar]

- is special character which is neither indexed or can be searched by Full Text Search. The only way to search the - is to use SQL queries and LIKE clause. LIKE searches can be slow provided that you are searching on many fields with large data. We were doing the same in AC7 where we only looked into non description fields when search had the special characters with LIKE based queries.

First you can try to make sure if the issue indeed is the slow search. It was my guess since you the error in example had - in search keyword. The simplest way to confirm this from web browser could be to use AdvanceSearch.aspx which let you choose if you want to look into Description fields or not. Try the same search keyword with and without including description fields and see if it show in difference in performance.

[jonw2m]

Apart from the errors when special characters are in the search keyword, we noticed the site is crashing if an user is not logged. Search keyword is very generic without special characters. If user is logged, same search returns results in 5 seconds. Any ideas?

Thank you,

[Katie]

There is a work-around for the issue, and it will be posted soon.

[Katie]

To fix this issue, you can download a patch. The code change is in \ConLib\CategorySearchSidebar.ascx.cs

Patch for R9: https://www.ablecommerce.com/patches/R9 ... _Patch.zip
Patch for R10: https://www.ablecommerce.com/patches/R1 ... _Patch.zip

This is a TEMPORARY work-around to fix the issue. With our next release, we'll get it fixed properly in the CommerceBuilder.dll.

Please update to let us know if you are able to get a faster search.

Thanks,
Katie

[jmestep]

Thanks, Katie.
Jon will get the site patched.

[jonw2m]

Thank you, Katie.

[jmestep]

Katie, I applied this to an R9 site and noticed that I have extra code on the search page that I think came from an Able patch. I can't check thoroughly now because my hard drive is going out and I'm having to baby it until the new one gets here. It had to do with shop by and here were the changes:

Code: Select all

           _shopBy = StringHelper.StripHtml(Server.UrlDecode(Request.QueryString["shopby"]));
            if (!string.IsNullOrEmpty(_shopBy))
            {
                _shopBy = StringHelper.StripHtml(_shopBy).Trim();
            }

Code: Select all

               if(!string.IsNullOrEmpty(_shopBy))
                    baseurl += "shopby=" + _shopBy + "&";

[mazhar]

These updates are related to sorting and I guess they were made in R10. There are two patches please make sure you are using the correct one.

[jmestep]

I went searching to assure myself that I wasn't crazy. It was a patch for R9 having to do with paging and shopby filters.
viewtopic.php?f=65&t=18140&hilit=shopby