NHibernate的+ SqlServer的全文本搜索 [英] NHibernate + SqlServer full text search

问题描述

我必须做全文搜索NHibernate的

I have to do Full text search in NHibernate

有关以前我使用Lucene.Net

For following operation previously I am using Lucene.Net

我有一个表名为候选

有关全文查询Lucene的将Lucene索引返回所有候选编号，并形成该ID我把查询候选并返回结果

For full text query Lucene will return all candidate Id from lucene index and form that id I put in query in candidate and return the result

但问题是，有超过10个缺少候选人的简历可用，因此Lucene是非常缓慢的，因为过滤器值从10路行，并把返回值在打击候选查询并再次筛选候选人花费过多时间

But the problem is there is more than 10 Lack of candidate resume available so Lucene is very slow because filter value from 10 Lk row and put return value for in query against candidate and again filter candidate is taking too much time

还我一个传呼标准，并为每个页面我回100名候选人

Also i have a paging criteria and for each page i return 100 candidates

现在我添加了新的表candidate_full_text
在该表中我配置在SQLServer 2000的
全文索引，现在我想使用NHibernate的DetachedCriteria如下查询

now i added new table candidate_full_text in that table i configured full text index in sqlserver 2000 now i want to query using NHibernate DetachedCriteria as follows

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 $ b单一的查询做$ b和用于candidate_full_text索引键列候选表ID ..
在这里，我
给出表款的 1）候选人（最小领域）

Following things i have to do in single query with DetachedCriteria And the key column for candidate_full_text index is candidate tables id.. Here i given table models of 1)candidate (Min fields)

ID - INT，

Id - int ,

名称 - VARCHAR，

Name - varchar,

多波 - 日期时间，

Dob - datetime,

2）candidate_full_text

ID - INT，

id - int,

candidate_resume_full_text -ntext（配置全文索引）

candidate_resume_full_text -ntext,(configured fulltext index)

candidate_id - INT

candidate_id - int

推荐答案

如果你能够使用，而不是Lucene的SQL服务器FTS和性能是可以接受的，你可以采取做关系的能力优势，在SQL Server FTS结果与其他关系之间的联接在你的数据库中的数据。要做到这些连接，您应该使用CONTAINSTABLE功能，而不是CONTAINS谓词

If you are able to use SQL Server FTS instead of Lucene and the performance is acceptable, you can take advantage of the ability to do relational joins between the SQL Server FTS results and the other relational data in your database. To do these joins, you should use the CONTAINSTABLE function, instead of the CONTAINS predicate.

使用你的榜样，让我们​​设置在SQL Server下表：

Using your example, let's set up the following tables in 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的东西沿着这些路线：

You can then create a parameterized named query in nHibernate something along these lines:

<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结果与以上在你的数据库中的其他数据的复杂关系查询。这种能力是在Lucene的使用SQL Server FTS的主要优势之一，可以成为一个理由去选择它了Lucene的，尽管其较差的整体性能。

Note how this query relationally joins the results of the CONTAINSTABLE function to other table in your database. By using SQL FTS, it is easy to join FTS results with complex relational queries over the other data in your DB. This capability is one of the key benefits of using SQL Server FTS over Lucene and can be a reason to choose it over Lucene in spite of its poorer overall performance.

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

Finally, you can fill in your parameters in your C# app and execute the query using the nHibernate ISession object:

        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中，您使用的是可用的，但我认为还有其他变通做分页（例如见这篇文章 ）。 （你也可以要升级的SQL Server 2008，运行FTS过程，具有更好的性能！）

The ROWNUMBER() function is not available in SQL Server 2000, which you are using, but I think there are other work-arounds for doing paging (see for instance this article). (Or you may want to upgrade your SQL Server to 2008, which runs FTS in process and has much better performance!)

我想沿着这些线路的解决方案将满足您的的需求。

I think a solution along these lines will meet your needs.

这篇关于NHibernate的+ SqlServer的全文本搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！