SQL Server 查询有时使用 freetexttable 花费太多时间 - 看起来索引问题

发布于 2024-10-05 18:31:24 字数 1404 浏览 4 评论 0原文

以下是我的查询，需要大约 1 分钟的时间，有时会在一秒钟内给出结果，特别是我在一段时间前执行查询后出现问题，或者在查询中添加一些新关键字。看起来像是一些索引问题，当我执行执行计划时，RID 查找成本为 60%。源表大约有 2-5 个 Lacs 数据，每天将添加大约 10,000-20,000 行。请告诉我。谢谢

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

执行计划

SQL 执行计划

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

郁金香雨 2024-10-12 18:31:24

您可以在单个 FREETEXTTABLE 搜索中指定多个列，这应该消除需要使用 UNION 进行多个查询。

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

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

回复收藏 0 原文

冷夜 2024-10-12 18:31:24

您是否尝试添加包含所有搜索列数据的计算列？然后将此计算列配置为持久保存并对其进行全文索引。然后你可以尝试

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

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

回复收藏 0 原文

~没有更多了~