質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.04%

sqlserverでのフルテキストインデックスについて(降順検索の場合異常に遅い)

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 638

score 13

sqlserverを利用しています。

フルテキストインデックスを用意し、検索を行っています。
現在のレコード数は約1億レコードあります。

現在、テストを行っているのですが、対象レコードが多いキーワードにて検索すると、
10秒ほどかかっております。

対象レコードが少ないキーワードにて検索すると、即座にレスポンスがある状況です。

実行しているSQLは下記のような内容です。
select * from [TABLE_NAME] where CONTAINS([TITLE],'"*[KEYWORD]*"') ORDER BY [identity_column_name] DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

対象レコードで合致するものが、上記SQLであれば20件該当すれば抜けるとイメージしておりますが、
該当件数が多い場合に、レスポンスが遅くなる理由が解らず困っております。

環境
Windows Server 2016
SQL Server 2017    
CPU E5-2650*2
MEMORY 192GB
DBサイズ 約120GB

イメージ説明

イメージ説明

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 1

checkベストアンサー

+2

identity_column_nameに関するインデックスはありますか?
また、desc指定していますか?

追記

select で使用されるインデックスは一つだけですので、そのインデックスに、whereやorder byでの項目が含まれている必要があります。
インデックスを分けて使用されるようにネストしてみましょう。

[identity_column_name] DESC のインデックスが有る状態で、以下の問い合わせで改善されないでしょうか。

select * from [TABLE_NAME] 
where プライマリーキー in (
        select プライマリーキー from [TABLE_NAME]  where CONTAINS([TITLE],'"*[KEYWORD]*"')
      ) 
ORDER BY [identity_column_name] DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY


但し、上記は、そもそも以下のSQLが単独で早くないと意味はありません。

select * from [TABLE_NAME] 
ORDER BY [identity_column_name] DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY
select プライマリーキー from [TABLE_NAME]  where CONTAINS([TITLE],'"*[KEYWORD]*"')


2番目が遅いという事なので、検索結果が少なくなるように条件を工夫するようなアプローチを考えないと駄目かもしれませんね。

例えば、

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]*"')


などで(後者は遅いかもですが)、閾値を超える場合に、条件を追加するようにメッセージを出すとか。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/11/27 13:36

    identity_column_nameに関するINDEXは存在するのですが、
    インデックスが利用されていない状況だとわかりました。

    identity_column_nameを主キーとしており、自動的にPK_[table_name]_[identity_column_name](クラスター化)
    が自動的に生成されています。
    このインデックスのプロパティ内に並べ替え順序とあるのですが、昇順のまま変更出来ない状況です。

    試しに、非クラスター化インデックスで[identity_column_name]の降順インデックスを作ってみましたが、効果なく、
    フルテキストインデックスのプロパティー内 一意インデックスがPK_[table_name]_[identity_column_name]から変更出来ません。
    そのため昇順のインデックスが使われているのは?と思います。

    select TOP 100 * from [TABLE_NAME] where CONTAINS([TITLE],'"*[KEYWORD]*"')
    上記のようなSQLですと一瞬で返ってきますので、DESC部分だと思うのですが・・

    根本的に何か間違っているとは思うのですが、どの部分なのか把握できずにいます。

    キャンセル

  • 2019/11/28 02:57

    おかげさまで、
    select * from [TABLE_NAME]
    where プライマリーキー in (
    select プライマリーキー from [TABLE_NAME] where CONTAINS([TITLE],'"*[KEYWORD]*"')
    )
    ORDER BY [identity_column_name] DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY
    で劇的に早くなりました。


    ありがとうございます!

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.04%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る