SQL Server全文搜索速度太慢 [英] SQL Server full text search too slow

问题描述

我正在与SQL Server的全文搜索速度太慢的问题。

I'm running into issues with SQL Server's full-text search running too slow.

它接管一分钟，运行全文搜索和连接查询（见下文）

It's taking over a minute to run a full-text search and JOIN query (see below)

select * from

(SELECT 
    (row_number() over ( order by b.BookKey)) as RowNumber,
    p.PublishCity,
    b.BookKey,
    b.BookTitle
from
    Books b (nolock)
    inner join PublishedRegions p (nolock)
        on b.BookKey = p.BookKey
where
    contains(p.PublishCity, 'France') or
    contains(b.BookTitle, 'France')
) as t1

where
    RowNumber between 200 and 210

• 作为参考，有在书籍表和PublishedRegions表
约50万行

• 我不能把PublishedCity在书籍表，因为SQL Server只允许你定义每个表中的全文索引。


• 注：200和210之间的最后一部分是ROWNUMBER刚刚插入任意。我可以选择任意一组行，结果仍需要很长时间


• 图书表上BOOKNAME NVARCHAR（255）
全文索引

• PublishedRegions对PublishedCity NVARCHAR全文索引（255）。


• For reference, there are about 500k rows in the Books table and PublishedRegions table
• I couldn't put PublishedCity in the Books table, because SQL Server only lets you define one full-text index per table.
• Note: The last part RowNumber between 200 and 210 is was just inserted arbitrarily. I could have chosen any set of rows and the results still take long
• Books table has a full-text index on BookName NVARCHAR(255)
• PublishedRegions has a full text index on PublishedCity NVARCHAR(255).

任何想法如何提高这种表现？

Any ideas how to improve the performance of this?

谢谢！

推荐答案

如果您使用的是 SQL Server 2012 + 因为当时页面而不是使用 ROW_NUMBER 使用 OFFSET..FETCH 。事情是这样的。

If you are using Sql Server 2012+ then for paging instead of using Row_Number use OFFSET..FETCH. Something like this.

SELECT
    p.PublishCity,
    b.BookKey,
    b.BookTitle
FROM
    Books b (nolock)
    INNER JOIN PublishedRegions p (nolock)
        ON b.BookKey = p.BookKey
WHERE
    CONTAINS(p.PublishCity, 'France') OR
    CONTAINS(b.BookTitle, 'France')
ORDER BY b.BookKey OFFSET 199 ROWS FETCH NEXT 11 ROWS ONLY

失调取办法会比 ROW_NUMBER 更好的性能。 <一href=\"http://www.mssqltips.com/sqlservertip/2362/overview-of-offset-and-fetch-feature-of-sql-server-2012/\"相对=nofollow> 这里检查的获得详细的对比

Offset-Fetch approach will have better performance than Row_number. Check here for detailed comparison

这篇关于SQL Server全文搜索速度太慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！