Search crashing when user is not logged

[jonw2m]

We have a client on Able Gold R9 / FTS search. When searching for a generic keyword, if the user is not logged, it crashes. If user is logged, the search returns just under 10 000 records. It works fine if the search returns less records (around 4000) or if the search is for a specific product.
Below is the error.

Any suggestions or ideas would help.

Thank you,

An error has occured at http://www.xxxxxxxxxxxx.aspx?k=wheels
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.ntwsearch5_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, FTS.RANK FROM (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) 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 PC.CatalogNodeTypeId = 1 AND P.ProductId IN (SELECT DISTINCT P.ProductId FROM ac_Products P WHERE P.StoreId = @p8 AND P.EnableGroups = 0 AND CONTAINS(P.*, @p1) 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 , FTS.RANK ORDER BY FTS.RANK DESC ] Name:storeId - Value:1 Name:keyword - Value:"wheels" [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, FTS.RANK FROM (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) 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 PC.CatalogNodeTypeId = 1 AND P.ProductId IN (SELECT DISTINCT P.ProductId FROM ac_Products P WHERE P.StoreId = @p8 AND P.EnableGroups = 0 AND CONTAINS(P.*, @p1) 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 , FTS.RANK 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(NativeSQLQuerySpecification spec, QueryParameters queryParameters) at NHibernate.Impl.SqlQueryImpl.List() 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, FTS.RANK FROM (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) 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 PC.CatalogNodeTypeId = 1 AND P.ProductId IN (SELECT DISTINCT P.ProductId FROM ac_Products P WHERE P.StoreId = @p8 AND P.EnableGroups = 0 AND CONTAINS(P.*, @p1) 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 , FTS.RANK ORDER BY FTS.RANK DESC ] Name:storeId - Value:1 Name:keyword - Value:"wheels" [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, FTS.RANK FROM (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) 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 PC.CatalogNodeTypeId = 1 AND P.ProductId IN (SELECT DISTINCT P.ProductId FROM ac_Products P WHERE P.StoreId = @p8 AND P.EnableGroups = 0 AND CONTAINS(P.*, @p1) 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 , FTS.RANK 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(NativeSQLQuerySpecification spec, QueryParameters queryParameters) at NHibernate.Impl.SqlQueryImpl.List() 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, FTS.RANK FROM (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) 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 PC.CatalogNodeTypeId = 1 AND P.ProductId IN (SELECT DISTINCT P.ProductId FROM ac_Products P WHERE P.StoreId = @p8 AND P.EnableGroups = 0 AND CONTAINS(P.*, @p1) 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 , FTS.RANK ORDER BY FTS.RANK DESC ] Name:storeId - Value:1 Name:keyword - Value:"wheels" [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, FTS.RANK FROM (ac_Products P INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) 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 PC.CatalogNodeTypeId = 1 AND P.ProductId IN (SELECT DISTINCT P.ProductId FROM ac_Products P WHERE P.StoreId = @p8 AND P.EnableGroups = 0 AND CONTAINS(P.*, @p1) 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 , FTS.RANK 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(NativeSQLQuerySpecification spec, QueryParameters queryParameters) at NHibernate.Impl.SqlQueryImpl.List() 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)

[Katie]

What happens if you try using the same search term on the advancedsearch.aspx page?

[jonw2m]

Same keyword returns results in few seconds and no errors.

Thank you,

[Katie]

That doesn't make a lot of sense. Is the regular search form customized in any way? Can you compare it's code to the advancedsearch.aspx page?

Katie

[jmestep]

Katie- some thoughts from when I had done some search troubleshooting.
I can't find the code now, but I thought that I had found a place in the source code that created a user and other user-related objects if the customer was anonymous.
Also, the AdvancedSearch doesn't have the sidebar so it doesn't do all the filtering and loading. It also doesn't have the shop by filtering.

[mazhar]

I wonder if product group restrictions are making any difference? Are there any products available only to specific user group in case result set is different for anonymous and logged in user?

[jonw2m]

There are no products marked as "EnableGroups = True" in this database.

[Katie]

Hello Jon,

Can you review this other forum discussion and let me know if this install has been patched?

viewtopic.php?f=65&t=18478

Thanks
Katie

[jonw2m]

I can't download the patches anymore (asking for credentials). Can you please post them here or make them available in a place where can be downloaded without credentials?
Thank you,

[Katie]

Sorry about that. Please try again.

To comply with PA-DSS requirements, we had to abandon the FTP site and all patch links needs to be changed from ftp://ftp.ablecommerce.com to https://www.ablecommerce.com

[jonw2m]

Thank you Katie,

The patch is applied to this site, but as far as I can see is fixing a different issue - special characters in the search keywords. This post is about keywords without any special characters, just generic searches as "tires".

Thank you,

[Katie]

Yes, I thought as much but I always like to know that all hot patches are installed.

So, can you check a few things? From the Configure > Store > Maintenance page, click the CHECK DATABASE button. Can you post the information for Anonymous User Count?

Also, how many total user records are there? You can go to the People > Users page to see the record count.

Thanks
Katie

[jonw2m]

Hi Katie,

There are 187544 anonymous users, oldest one is three days.
Total non anonymous users is 3933.

Thank you for your help,

[Katie]

Wow. That is a lot of records for only 3 days. Is this site on a dedicated web server and separate SQL server? I sure hope so.

Do you have Activity logging enabled? We recommend this is turned off for high traffic sites.

I'm just trying to cover the basics here. These are the most common issues that can cause problems.

[jonw2m]

This site is on a dedicated webserver but a shared sql server. IIS logging is on - we do that for all our clients for traffic report.
Thank you,

[Katie]

There are 187544 anonymous users, oldest one is three days.

One thing you can try is to manually remove the anonymous users and see if that helps. I would remove all but a few records and then try using the search again.

If you are using the Shop By (product finder) feature with 10,000+ products, you may be running into an SQL overload issue. The only way to know for sure is to see if the search term "tires" works after cleaning up the anonymous users and also trying during a non-peak time of day.