NHibernate + SqlServer full text search

Question

I have to do Full text search in NHibernate

For following operation previously I am using Lucene.Net

I have a table called candidate

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

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

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

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)

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 ,

Name - varchar,

Dob - datetime,

2)candidate_full_text

id - int,

candidate_resume_full_text -ntext,(configured fulltext index)

candidate_id - int

Answer 1

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.

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)
)

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>

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.

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();

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.