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

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

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  

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