Home>

sql server - about full-text index with sqlserver (abnormally slow for descending search)

I use sqlserver.

A full text index is prepared and searched.
There are currently about 100 million records.

I'm currently testing, but if I search for keywords with many records,
It takes about 10 seconds.

If i search with keywords with few records, there is an immediate response.

The SQL being executed is as follows.
select * from [TABLE_NAME] where CONTAINS ([TITLE], '"* [KEYWORD] *"') ORDER BY [identity_column_name] DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

If the target record matches the above SQL, 20 images are considered to be missing.
If there are a large number of cases, we have no idea why the response is slow.

Environment
Windows Server 2016
SQL Server 2017
CPU E5-2650 * 2
MEMORY 192GB
DB size 120GB

sqlserver
  • Answer # 1

    Is there an index for

    identity_column_name?
    Is desc specified?

    Added

    select uses only one index, so that index must contain an item with where or order by.
    Let's nest the indexes to be used separately.

    [identity_column_name] If there is a DESC index, isn't it improved by the following query?

    select * from [TABLE_NAME]
where primary key in (
        select primary key from [TABLE_NAME] where CONTAINS ([TITLE], '"* [KEYWORD] *"')
      )
ORDER BY [identity_column_name] DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY


    However, the above does not make sense if the following SQL is not fast in the first place.

    select * from [TABLE_NAME]
ORDER BY [identity_column_name] DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY
    select primary key from [TABLE_NAME] where CONTAINS ([TITLE], '"* [KEYWORD] *"')


    Since the second is slow, it may be useless if you do not think of an approach that devise conditions so that search results are reduced.

    For example,

    select * from [TABLE_NAME] where CONTAINS ([TITLE], '"* [KEYWORD] *"')
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY
    select count (*) from [TABLE_NAME] where CONTAINS ([TITLE], '"* [KEYWORD] *"')


    Etc. (the latter may be slow), but when the threshold is exceeded, a message is issued to add a condition.

Related articles