7.0.6 Upgrade Search Count Inconsistency

dc8johnson · Post by **dc8johnson** » Thu Feb 10, 2011 10:39 am

I'm updating a site from 7.0.4 to 7.0.6 and I noticed an inconsistency in the search.

When I do a quick search for "tire changers" while running the profiler on SQL Server I get this query from the NarrowSearchCount call:

Code: Select all

exec sp_executesql N'SELECT COUNT(DISTINCT P.ProductId) As TotalProducts 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 = @storeId AND (P.Name LIKE @keyword OR P.Sku LIKE @keyword OR P.ModelNumber LIKE @keyword OR P.Summary LIKE @keyword OR P.Description LIKE @keyword OR P.ExtendedDescription LIKE @keyword OR P.SearchKeywords LIKE @keyword) AND P.VisibilityId = 0)',N'@storeId int,@keyword nvarchar(17)',@storeId=1,@keyword=N'%tire% %changers%'

and this query from the NarrowSearch call:

Code: Select all

exec sp_executesql N'SELECT  TOP 15 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 = @storeId AND (P.Name LIKE @keyword OR P.SearchKeywords LIKE @keyword) 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 OrderBy',N'@storeId int,@keyword nvarchar(17)',@storeId=1,@keyword=N'%tire% %changers%'

The where clauses don't match. Because of this the NarrowSearchCount returns more results. In one case the NarrowSearch returns exactly 15 results (my page size). But the NarrowSearchCount returns 17 results so the second page of the search results is blank.

I went back to 7.0.4 code and found that the same queries were being run. Do I have a problem with my update or is this a problem? Here is the version number info from the help page.

AbleCommerce for ASP.NET
VERSION: 7.0.6.14312
MSSQL v2008
AC SCHEMA v2005
.NET CLR v2.0.50727.4206
ASP.NET TRUST: Unrestricted

AjaxControlToolkit: 3.0.20229.20843
CommerceBuilder: 7.6.14302.0
CommerceBuilder.AbleCommerceTax: 7.4.13043.0
CommerceBuilder.AcTestProvider: 1.0.0.0
CommerceBuilder.AustraliaPost: 7.1.0.0
CommerceBuilder.AuthorizeNet: 7.3.12625.0
CommerceBuilder.AvaTax: 7.6.14309.0
CommerceBuilder.BankOfCanada: 7.3.0.0
CommerceBuilder.CanadaPost: 7.0.0.0
CommerceBuilder.Configuration: 7.6.14302.0
CommerceBuilder.CyberSource: 7.5.13718.0
CommerceBuilder.Data: 7.6.14302.0
CommerceBuilder.DataClient.Api: 7.6.14302.0
CommerceBuilder.DataClient.Csv: 1.0.0.0
CommerceBuilder.DHLInternational: 7.2.11307.0
CommerceBuilder.ECB: 7.0.0.0
CommerceBuilder.FedEx: 7.0.9725.0
CommerceBuilder.GoogleCheckout: 7.6.14238.0
CommerceBuilder.InternetSecure: 7.0.9764.0
CommerceBuilder.LinkPoint: 7.0.9764.0
CommerceBuilder.Paradata: 7.0.9764.0
CommerceBuilder.PayFlowPro: 7.0.10061.0
CommerceBuilder.PayJunction: 7.3.12625.0
CommerceBuilder.PaymentechOrbital: 7.5.14063.0
CommerceBuilder.PayPal: 7.6.14103.0
CommerceBuilder.Protx: 7.3.12327.0
CommerceBuilder.Services: 7.6.14302.0
CommerceBuilder.SkipJack: 7.0.9764.0
CommerceBuilder.UPS: 7.0.10041.0
CommerceBuilder.USPS: 7.4.13441.0
CommerceBuilder.WATax: 7.5.13942.0
CommerceBuilder.Web: 7.6.14302.0
ComponentArt.Web.UI: 2010.1.2637.35
CyberSource.Base: 2.0.0.0
CyberSource.Clients: 5.0.2.0
CyberSource.Clients.XmlSerializers: 5.0.2.0
CyberSource.WSSecurity: 2.0.0.0
CybsWSSecurityIOP: 1.0.0.0
DataPort.Framework: 7.4.221.0
FredCK.FCKeditorV2: 2.5.2912.21007
ICSharpCode.SharpZipLib: 0.85.5.452
LinkPointTransaction: 1.0.1797.29766
log4net: 1.2.10.0
NVelocity: 7.2.0.0
PaygatewayNET: 3.0.0.0
paypal_base: 4.2.1.0
PFProCOMLib: 1.0.0.0
PFProdotNET: 0.0.0.0
RSS.NET: 0.86.2977.28464
Validators: 1.0.0.0
WebChart: 1.1.1.6
wwHoverPanel: 1.85.0.0

Thanks,

David