SQL Server Query Some time take too much time with freetexttable - Looks Index Problem

Question

Following is my query, it takes some time around 1 minute and some times gives a result in a second, problem occurred specially i execute query after some time ago, or put some new keyword in query. It looks like some index problem, when i executed execution plan, RID Look up cost 60%. The source table have around 2-5 Lacs data and daily around 10,000-20,000 rows will be added. Please advise me. Thanks

SELECT *
FROM   (SELECT Row_number() OVER (ORDER BY rank DESC, jobid DESC) AS rnum,
               *
        FROM   (SELECT rank,
                       joblistview.*
                FROM   joblistview,
                       FREETEXTTABLE(joblistview, jobtitle, 'seo manager') f
                WHERE  joblistview.jobid = f.[key]
                       AND CONTAINS(joblistview.joblocation, 'mumbai')
                UNION
                SELECT rank,
                       joblistview.*
                FROM   joblistview,
                       FREETEXTTABLE(joblistview, jobdescription, 'seo manager')
                       f
                WHERE  joblistview.jobid = f.[key]
                       AND CONTAINS(joblistview.joblocation, 'mumbai')
                UNION
                SELECT rank,
                       joblistview.*
                FROM   joblistview,
                       FREETEXTTABLE(joblistview, company_name, 'seo manager') f
                WHERE  joblistview.jobid = f.[key]
                       AND CONTAINS(joblistview.joblocation, 'mumbai')) AS xx)AS
       tt
WHERE  rnum BETWEEN 11 AND 20

Execution Plan

SQL Execution Plan

They are not the same query, each query searches a different column (jobtitle, jobdescription, company_name) using the FREETEXTTABLE function. — AUSteve, Dec 03 '10 at 04:57

Joe Stefanelli · Answer 1 · 2010-12-03T14:50:59.707

0

You can specify multiple columns in a single FREETEXTTABLE search, which should eliminate the need for multiple queries with UNIONs.

SELECT *
FROM   (SELECT Row_number() OVER (ORDER BY rank DESC, jobid DESC) AS rnum,
               *
        FROM   (SELECT rank,
                       joblistview.*
                FROM   joblistview,
                       FREETEXTTABLE(joblistview, (jobtitle,jobdescription,company_name), 'seo manager') f
                WHERE  joblistview.jobid = f.[key]
                       AND CONTAINS(joblistview.joblocation, 'mumbai')
               ) AS xx
       ) AS tt
WHERE  rnum BETWEEN 11 AND 20

edited Dec 03 '10 at 14:50

answered Dec 03 '10 at 14:36

Joe Stefanelli

132,803
19
237
235

Hi thanks for answer.. I have already done that, but the result was coming far more better in terms of relevance with Union. Off course with multiple columns result will come fast. With Union also result performance is good But the problem is i think it is related to Index.. Whenever new terms come in to search it takes too much time. – Vkalal Dec 04 '10 at 03:02

score 0 · Answer 2 · answered Jun 08 '11 at 16:39

Did you try to add a computed column that contains data of all your search columns? Then configure this computed column to be persisted and let it be full text indexed. You could then try

SELECT *
FROM   (SELECT Row_number() OVER (ORDER BY rank DESC, jobid DESC) AS rnum,
               *
        FROM   (SELECT rank,
                       joblistview.*
                FROM   joblistview,
                       FREETEXTTABLE(joblistview, (<<<ComputedColumn>>>), 'seo manager') f
                WHERE  joblistview.jobid = f.[key]
                       AND CONTAINS(joblistview.joblocation, 'mumbai')
               ) AS xx
       ) AS tt
WHERE  rnum BETWEEN 11 AND 20

SQL Server Query Some time take too much time with freetexttable - Looks Index Problem

2 Answers2