c# - 改善网站数据库上的搜索效果

有什么方法可以改进此查询，我对返回的结果感到很满意，但性能确实很差，在这种情况下联合会导致往返吗？通过使用存储过程来提高性能，这样的事情是否可能？是否可以对结果进行诸如自我连接之类的事情，这会提高性能吗？

        List<string> words = Util.Search.SplitByWhiteSpace(q);

        using (WebsitesDataContext context = new WebsitesDataContext())
        {
            IQueryable<WebsitesRanked> query = Enumerable.Empty<WebsitesRanked>().AsQueryable();
            query = query.Union(context.Websites.Where(x => x.Title.Contains(q)).Select(x => new WebsitesRanked { Webiste = x, Rank = 100 }));
            query = query.Union(context.Websites.Where(x => x.Description.Contains(q)).Select(x => new WebsitesRanked { Webiste = x, Rank = 100 }));
            query = query.Union(context.Websites.Where(x => x.Keywords.Contains(q)).Select(x => new WebsitesRanked { Webiste = x, Rank = 100 }));
            query = query.Union(context.Websites.Where(x => x.Url.Contains(q)).Select(x => new WebsitesRanked { Webiste = x, Rank = 100 }));

            foreach (var word in words)
            {
                string keyword = word;
                query = query.Union(context.Websites.Where(x => x.Title.Contains(word)).Select(x => new WebsitesRanked { Webiste = x, Rank = 20 }));
                query = query.Union(context.Websites.Where(x => x.Description.Contains(word)).Select(x => new WebsitesRanked { Webiste = x, Rank = 20 }));
                query = query.Union(context.Websites.Where(x => x.Keywords.Contains(word)).Select(x => new WebsitesRanked { Webiste = x, Rank = 20 }));
                query = query.Union(context.Websites.Where(x => x.Url.Contains(word)).Select(x => new WebsitesRanked { Webiste = x, Rank = 20 }));
            }

           var results = query.GroupBy(x => x.Webiste).Select(x => new WebsitesRanked { Webiste = x.First().Webiste, Rank =  x.Sum(s => s.Rank) }).OrderByDescending(x => x.Rank).Select(x => x.Webiste);
        }

最佳答案

您可以创建字典表：
一个表按字母分区（表分区以单词“ a”开头，另一个以“ b”开头...）
该表将包含以下列：

column 1:Word (each word in your table will be setted here using a nightly schedule task or Background schedule task)
column 2:Website
column 3:Rank (each time a word is found, increment this rank)

仅需查询一张表并使用分区即可执行，您将获得良好的性能，因为该工作将按计划任务完成

关于c# - 改善网站数据库上的搜索效果，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12130638/