NHibernate + SqlServer全文搜索

Question

我需要在NHibernate中进行全文搜索。

以前，我使用的是Lucene.Net。

我有一个名为candidate的表。

对于全文查询，Lucene会从lucene索引中返回所有候选人Id，并将这些id放入candidate查询中返回结果。

但问题是，有超过10万份候选人简历可用，因此Lucene非常慢，因为从10万行中筛选值并在候选人中放回值再次筛选候选人需要太长时间。

另外，我有一个分页标准，每页返回100个候选人。

现在，我添加了一个名为candidate_full_text的新表，在该表中我配置了sqlserver 2000中的全文索引，现在我想使用NHibernate DetachedCriteria进行查询，如下所示：

1) Select candidate with some filters

2) Execute the function ContainsTable for candidate_full_text table 
 (which returns candidate tables id as key and rank of occurrence of the search string)

3) join the result from 1 & 2

4) Apply paging criteria (ie return 1st 100,2nd 100,3rd 100.. etc) according to page no

5) return the result by order of rank column (which is return by ContainsTable)

我需要使用DetachedCriteria在单个查询中完成以下任务，候选人全文索引的关键列是候选人表的id列。下面是表模型：

1) 候选人（最少字段）

Id - 整数,

Name - 字符串,

Dob - 日期时间,

2) 候选人全文本

id - 整数,

candidate_resume_full_text - ntext（已配置全文本索引），

candidate_id - 整数

- Anand

---

我不会指望全文搜索，特别是在 Sql Server 2000 上，比 Lucene 更快。Lucene 通常非常快，即使对于大量数据也是如此。您是否考虑通过将所有所需数据放入 Lucene 中来消除 IN 查询，以便完全避免访问 Sql Server？ - Jonas Høgh

我选择使用SQL Server全文搜索而不是Lucene，原因更多是逻辑上的而不是技术上的。我无法将所有数据放入Lucene，因为我正在使用NHibernate作为ORM。我只是用Lucene来做全文搜索这一个目的。 - Anand

我同意Jonas H.的观点，总体而言，Lucene比SQL FTS更具性能。然而，如果SQL FTS的性能足够好，使用它可以使您受益于能够通过CONTAINSTABLE或FREETEXTTABLE函数将FTS结果与其他关系数据进行关联连接，从而使得非常复杂的过滤和分组变得更加容易。 - Joe Alfano

Answer 1

如果您能够使用SQL Server FTS代替Lucene并且性能可接受，您可以利用在SQL Server FTS结果和数据库中的其他关系数据之间进行关系连接的能力。为了执行这些连接，您应该使用CONTAINSTABLE函数，而不是CONTAINS谓词。

以您的示例为例，让我们在SQL Server中设置以下表：

create table Candidate
( 
Id int primary key,
Name varchar(50),
Dob  datetime
)

create table Candidate_Full_Text
(
id int primary key,
candidate_resume_full_text ntext, -- FTS column
candidate_id int foreign key references Candidate(Id)
)

您可以在nHibernate中创建一个参数化的命名查询，类似于以下内容：

<sql-query name="CandidateSearch">
   <![CDATA[
     SELECT TOP (:take) * 
        FROM
            (SELECT c.Id, c.Name, ft.[RANK], ROW_NUMBER() OVER(ORDER BY ft.[RANK] desc) as rownum          
            FROM ContainsTable(Candidate_full_text, candidate_resume_full_text , :phrase, LANGUAGE 1033) ft
                        INNER JOIN Candidate c on ft.[KEY] = c.Id
            WHERE c.Name = :name and c.Dob > :dob
             ) a
        WHERE a.rownum > :skip ORDER BY a.rownum 
  ]]>
</sql-query>

请注意，此查询通过关系连接CONTAINSTABLE函数的结果与您数据库中的其他表相连。通过使用SQL FTS，可以轻松地将FTS结果与其他数据的复杂关系查询相结合。这种能力是使用SQL Server FTS相对于Lucene的主要优势之一，并且可能是选择它而不是Lucene的原因，尽管其整体性能较差。

最后，您可以在C＃应用程序中填写参数，并使用nHibernate ISession对象执行查询：

        int take = 5;
        int skip = 10;
        string phrase = "(team NEAR player) OR (teamwork NEAR coopertive)";
        string name = "John Doe";
        DateTime dob = new DateTime(1963, 7, 1);

        var results = _session.GetNamedQuery("ExpandedSearchTerm")
                              .SetString("phrase", phrase)
                              .SetDateTime("dob", dob)
                              .SetString("phrase", phrase)
                              .SetInt32("take", take)
                              .SetInt32("skip", skip)
                              .List();

“ROWNUMBER()”函数在您使用的SQL Server 2000中不可用，但我认为还有其他方法来进行分页（例如，请参阅this article）。 （或者您可能想将SQL Server升级到2008版，该版本可以在进程中运行FTS，并具有更好的性能！）

我认为沿着这些方向的解决方案将满足您的需求。

- Joe Alfano

---

在最后一个片段中，查询名称“ExpandedSearchTerm”是否应该与中间的“CandidateSearch”匹配？ - Andy