1

I have a full text catalog with about 300K records in it. We use full text search over 3 columns to search this catalog of articles.

Searching single words like 'Shower head' are almost instantly. But almost all the products also have a code in there name, like 62.13.1 or 300B.10.

People in the organization know a lot of these codes and search with these codes. But using a . in my search query slows my query down to almost 1 minute.

I think it has something to do with word breakers but I'm not sure and my knowledge of database isn't that great. The results I'm getting back are correct thou.

This is on Windows Azure SQL btw. Locally isn't slow as well.

Part of the execution path.

Query:

SELECT TOP 100
a.Id as Id,
CASE WHEN ((a.Code IS NULL) OR (( CAST(LEN(a.Code) AS int)) = 0)) THEN 
a.Name ELSE CASE WHEN (a.Code IS NULL) THEN N'' ELSE a.Code END + N' - ' + 
a.Name END AS Value,
a.Name as Name,
a.Code as Code,     
a.[ImageUrl] AS [ImageUrl], 
a.[Description] AS [Description],
... Other small properties
case when (a.TimesUsedInArticleList is null) then 0 else 
a.TimesUsedInArticleList end as [Frequency]
FROM Articles a with (nolock)
LEFT JOIN MeasureUnitTypes mut on a.MeasureUnitTypeId = mut.id
LEFT JOIN UnitTypes ut on a.UnitTypeId = ut.id
LEFT JOIN PostGroups pg on a.PostGroupId = pg.Id
WHERE
a.IsDeleted = 0 AND
a.ArticleType != 2 AND
CONTAINS((a.Name,a.Code,a.Description),@Query) 
ORDER BY TimesUsedInArticleList desc, IsCommon desc

Note: The query also contains a wild card (for example: 61.25*).

When not searching with the wild card the query is much faster.

  • btw. Locally isn't slow as well. do you mean btw. Locally is slow as well.? – SqlWorldWide Aug 18 '17 at 19:12
  • one thing for sure,wrtie "a.ArticleType not in( 2) ".you can use inner join is possible. check performance.you can comment order by,again check performance. – KumarHarsh Aug 21 '17 at 3:32
  • Do you get the same execution plans on SQL Server vs. Azure SQL Database? What is your DTU utilization at the time of degradation? – Joe Sack Aug 24 '17 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.