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
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.
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:
When not searching with the wild card the query is much faster.