在SQL Server 2008中加入的分层记录上进行全文搜索

问题描述

为了举例，我有一个Person表，一个Tag表和一个ContactMethod表。一个人将有多个标记记录和多个与他们相关的ContactMethod记录。

我想要一个宽容的搜索，它将在每个表格的几个字段中进行搜索。所以我可以通过他们的电子邮件（通过ContactMethod），他们的名字（通过Person）或分配给他们的标签找到一个人。

作为FTS的完整noob，想到这些方法：

1. 构建一些复杂的查询，分别为每个字段寻址


2. 查找表，它连接我想要索引的字段，并在该派生表上只做一个全文查询。
   

（免费如果你的sql server支持它，你可以通过以下方式来编辑它：

解决方案

创建一个索引视图和全文搜索;您可以使用containstable（*，'chris'）来读取所有列。

如果它不支持它，因为字段全部来自不同的表我认为可扩展性;如果您可以轻松地将字段填充到独立表中的每条记录的单个行中，我会进行全文搜索而不是单个记录。您最终将得到一个不太复杂的FTS目录，并且您的查询不需要一次执行4个全文搜索。在同一时间在不同的表上运行大量单独的FTS查询是查询我的经验中的性能问题的凭单。这样做的缺点是你失去了自己搜索姓氏的能力;如果这是你需要的东西，你可能需要看一个替代方案。

在我们的应用程序中，我们发现单个表更快（我们不能依赖于拥有企业sql的客户）;所以我们通过更新sp将数据填充到FTS表中，然后我们的主要联系人查找在列表中运行搜索。我们有两个单独的搜索来处理精确搜索（即名称或电话号码）或仅用于自由文本。关于表格的另一个好处是，向查询添加更多列相对容易且成本低（例如，我们已经要求我们提供社会安全号码;为此，我们只是将该列添加到更新SP中，而我们已经离开了几乎没有影响。

Probably a noob question, but I'll go for it nevertheless.

For sake of example, I have a Person table, a Tag table and a ContactMethod table. A Person will have multiple Tag records and multiple ContactMethod records associated with them.

I'd like to have a forgiving search which will search among several fields from each table. So I can find a person by their email (via ContactMethod), their name (via Person) or a tag assigned to them.

As a complete noob to FTS, two approaches come to mind:

1. Build some complex query which addresses each field individually
2. Build some sort of lookup table which concatenates the fields I want to index and just do a full-text query on that derived table.

(Feel free to edit for clarity; I'm not in it for the rep points.)

解决方案

If your sql server supports it you can create an indexed view and full text search that; you can use containstable(*,'"chris"') to read all the columns.

If it doesn't support it as the fields are all coming from different tables I think for scalability; if you can easily populate the fields into a single row per record in a separate table I would full text search that rather than the individual records. You will end up with a less complex FTS catalog and your queries will not need to do 4 full text searches at a time. Running lots of separate FTS queries over different tables at the same time is a ticket to query performance issues in my experience. The downside with doing this is you lose the ability to search for Surname on its own; if that is something you need you might need to look at an alternative.

In our app we found that the single table was quicker (we can't rely on customers having enterprise sql at hand); so we populate the data with spaces into an FTS table through an update sp then our main contact lookup runs a search over the list. We have two separate searches to handle finding things with precision (i.e. names or phone numbers) or just for free text. The other nice thing about the table is it is relatively easy and low cost to add further columns to the lookup (we have been asked for social security number for example; to do it we just added the column to the update SP and we were away with little or no impact.

这篇关于在SQL Server 2008中加入的分层记录上进行全文搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！