问题描述
我试图在我的ASP.NET MVC实现完整的搜索功能(C#,LINQ到SQL)的网站。
该网站由大约3-4表有大约1-2列,我想搜索。
这是我迄今为止:
公开名单<&SearchResult中GT;搜索(字符串关键字)
{
字符串[] =拆分Keywords.Split(新的char [] {''},StringSplitOptions.RemoveEmptyEntries);
清单<&SearchResult中GT; RET =新的List<&SearchResult中GT;();
的foreach(在分割字符串s)
{
IEnumerable的<&博文GT;结果= db.BlogPosts.Where(X => x.Text.Contains(多个)|| x.Title.Contains(S)); 的foreach(在结果的博文P)
{
如果(ret.Exists(X => x.PostID == p.PostID))
继续; ret.Add(新信息搜索结果
{
PostTitle = p.Title,
BlogPostID = p.BlogPostID,
文字= p.Text
}); }
}
返回RET;
}
正如你所看到的,我对关键字和在一个表(我想再说一遍对每个表)。
运行一个内部的foreach一个foreach这似乎inefficent,我想知道,如果那里有一个更好的方式来创建数据库的搜索方法。
另外,我能做些什么,以列在数据库中,使他们能够被搜索得更快?我读了一些有关他们的索引,是只是全文索引真/假现场我在SQL Management Studio中看到了什么?
Yes, enabling full-text indexing will normally go a long way towards improving performance for this scenario. But unfortunately it doesn't work automatically with the LIKE operator (and that's what your LINQ query is generating). So you'll have to use one of the built-in full-text searching functions like FREETEXT, FREETEXTTABLE, CONTAINS, or CONTAINSTABLE.
Just to explain, your original code will be substantially slower than full-text searching as it will typically result in a table scan. For example, if you're searching a varchar field named title with LIKE '%ABC%' then there's no choice but for SQL to scan every single record to see if it contains those characters.
However, the built-in full-text searching will actually index the text of every column you specify to include in the full-text index. And it's that index that drastically speeds up your queries.
Not only that, but full-text searching provides some cool features that the LIKE operator can't give you. It's not as sophisticated as Google, but it has the ability to search for alternate versions of a root word. But one of my favorite features is the ranking functionality where it can return an extra value to indicate relevance which you can then use to sort your results. To use that look into the FREETEXTTABLE or CONTAINSTABLE functions.
Some more resources:
这篇关于搜索数据库 - ASP.NET MVC C#的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!