7.0.4 Service Release 1 and Search
Posted: Tue Jun 22, 2010 12:22 pm
We've installed Service Release 1 and the search is working much better but there seems to be an inconsistency problem.
I ran a simple search using two words - booth* filter* - (asterisks included).
Using the SQL Server Profiler I captured the four queries that were run. The first query correctly returns the total number of products, the second query correctly returns the total number products per manufacturer, the four query correctly returns the total number of products for the current category. The third query returns no results. The first, second and fourth queries use the fulltext CONTAINS feature while the third query uses LIKE.
If I change the simple search to just - booth filter - then the third query uses CONTAINS like the counts and returns the sames results. The (booth* filter*) returns the 8 results I'm looking for versus the 6 results from the (booth filter) search because two results contain "booth filters". I can manually run the Revised Query 3 to include the wild cards and it returns the 8 results.
I've double checked my files against the SR1 files and everything seems to be up to date. Did I miss something or is this how the search was designed to work or is there a problem?
Thanks for your help,
Query 1
Query 2
Query 3 (booth* filter*)
Query 4
Revised Query 3 (booth filter)
I ran a simple search using two words - booth* filter* - (asterisks included).
Using the SQL Server Profiler I captured the four queries that were run. The first query correctly returns the total number of products, the second query correctly returns the total number products per manufacturer, the four query correctly returns the total number of products for the current category. The third query returns no results. The first, second and fourth queries use the fulltext CONTAINS feature while the third query uses LIKE.
If I change the simple search to just - booth filter - then the third query uses CONTAINS like the counts and returns the sames results. The (booth* filter*) returns the 8 results I'm looking for versus the 6 results from the (booth filter) search because two results contain "booth filters". I can manually run the Revised Query 3 to include the wild cards and it returns the 8 results.
I've double checked my files against the SR1 files and everything seems to be up to date. Did I miss something or is this how the search was designed to work or is there a problem?
Thanks for your help,
Query 1
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 CONTAINS(P.*, @keyword) AND P.VisibilityId = 0)',N'@storeId int,@keyword nvarchar(22)',@storeId=1,@keyword=N'"booth*" and "filter*"'Code: Select all
exec sp_executesql N'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 = @storeId AND CONTAINS(P.*, @keyword) AND P.VisibilityId = 0 GROUP BY M.ManufacturerId, M.Name ORDER BY M.Name',N'@storeId int,@keyword nvarchar(22)',@storeId=1,@keyword=N'"booth*" and "filter*"'Code: Select all
exec sp_executesql N'SELECT TOP 12 P.ProductId, P.Name, P.Sku, P.Price, P.MSRP, P.CostOfGoods, P.Weight, P.Length, P.Width, P.Height, P.IsFeatured, FTS.RANK, MIN(PC.OrderBy) AS OrderBy 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, @keyword)
UNION ALL
SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, Sku, @keyword)
UNION ALL
SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, ModelNumber, @keyword)
UNION ALL
SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, SearchKeywords, @keyword)
UNION ALL
SELECT [KEY], (RANK * 1.5) AS weightRank FROM CONTAINSTABLE(ac_Products, Summary, @keyword)
UNION ALL
SELECT [KEY], (RANK * 1.0) AS weightRank FROM CONTAINSTABLE(ac_Products, [Description], @keyword)
UNION ALL
SELECT [KEY], (RANK * 1.0) AS weightRank FROM CONTAINSTABLE(ac_Products, ExtendedDescription, @keyword)
) 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 = @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) 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 Name ASC',N'@storeId int,@keyword nvarchar(22)',@storeId=1,@keyword=N'"booth%" and "filter%"'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 INNER JOIN ac_CatalogNodes PC ON P.ProductId = PC.CatalogNodeId) WHERE P.StoreId = @storeId AND CONTAINS(P.*, @keyword) AND PC.CatalogNodeTypeId = 1 AND PC.CategoryId IN (SELECT CategoryId FROM ac_CategoryParents WHERE ParentId = @categoryId) AND P.VisibilityId = 0)',N'@storeId int,@keyword nvarchar(22),@categoryId int',@storeId=1,@keyword=N'"booth*" and "filter*"',@categoryId=14Code: Select all
exec sp_executesql N'SELECT TOP 12 P.ProductId, P.Name, P.Sku, P.Price, P.MSRP, P.CostOfGoods, P.Weight, P.Length, P.Width, P.Height, P.IsFeatured, FTS.RANK, MIN(PC.OrderBy) AS OrderBy 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, @keyword)
UNION ALL
SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, Sku, @keyword)
UNION ALL
SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, ModelNumber, @keyword)
UNION ALL
SELECT [KEY], (RANK * 2.0) AS weightRank FROM CONTAINSTABLE(ac_Products, SearchKeywords, @keyword)
UNION ALL
SELECT [KEY], (RANK * 1.5) AS weightRank FROM CONTAINSTABLE(ac_Products, Summary, @keyword)
UNION ALL
SELECT [KEY], (RANK * 1.0) AS weightRank FROM CONTAINSTABLE(ac_Products, [Description], @keyword)
UNION ALL
SELECT [KEY], (RANK * 1.0) AS weightRank FROM CONTAINSTABLE(ac_Products, ExtendedDescription, @keyword)
) 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 = @storeId AND CONTAINS(P.*, @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, FTS.RANK ORDER BY Name ASC',N'@storeId int,@keyword nvarchar(20)',@storeId=1,@keyword=N'"booth" and "filter"'