この記事では、SQL Server でフルテキスト述語を使用するクエリのパフォーマンスを向上させる方法について説明します。
元の製品バージョン: SQL Server
元の KB 番号: 2549443
まとめ
この記事では、フルテキスト検索述語 ( CONTAINS や CONTAINSTABLEなど) を使用し、データをフィルター処理する Microsoft SQL Server クエリのパフォーマンスを向上させる方法について説明します。 たとえば、このメソッドを使用すると、次のクエリのパフォーマンスが向上します。
SELECT * FROM dbo.ftTest WHERE CONTAINS(TextData, '"keyword"') AND CDate > @date
このメソッドを使用すると、フルテキスト検索エンジンが結果をリレーショナル エンジンに送信する前にフィルター処理するように、クエリ、テーブル スキーマ、フルテキスト インデックスを設計できます。 そのため、リレーショナル エンジンは大規模なデータセットをフィルター処理する必要はありません。
詳細
フルテキスト検索クエリを作成する場合、クエリのパフォーマンスに影響する原則要因は、残りのデータがリレーショナル エンジンに送信される前にフルテキスト検索エンジンが処理する必要があるデータの量です。 SQL Server では、後で処理する必要がある行の数を減らすために行を早期にフィルター処理することで、クエリのパフォーマンスを向上させることができます。
この記事で説明する方法では、Table-Valued 関数 (TVF) を使用して、行を早期にフィルターで除外し、後で処理する必要がある行の数を減らします。
たとえば、次のクエリ プランでは、 CONTAINS 検索文字列と一致する131051行が返されます。 さらに、プランの結合演算子は、インデックス シークを使用して追加のフィルター処理を実行します。
Rows StmtText
-------------------- ----------------------------------------------------------------------------------------
1167 SELECT CDate, ID FROM dbo.fttest WHERE contains (c2, '"create"') AND CDate> '08/05/2019'
1167 |--Merge Join(Left Semi Join, MERGE:([FTSdb].[dbo].[fttest].[ID])=(FulltextMatch.[docid]), RESIDUA
5858 |--Sort(ORDER BY:([FTSdb].[dbo].[fttest].[ID] ASC))
5858 | |--Clustered Index Seek(OBJECT:([FTSdb].[dbo].[fttest].[clidx1]), SEEK:([FTSdb].[
131051 |--Table-valued function
ただし、クエリにフルテキスト一意のインデックス キー列が述語として含まれている場合、フルテキスト検索エンジンは述語を使用してフルテキスト レベルで結果をフィルター処理できます。 この状況では、追加のフィルター処理を適用する必要がある前に、TVF から返されるデータ量がはるかに少なくなります。 たとえば、次のクエリでは、c2 条件に一致する必要がある 5 つの値を指定し、TVF は 5 つの値に一致する結果のみを返します。
Rows StmtText
-------------------------------------------------------------------------------------------------------------------------------------------
5 SELECT CDate, ID FROM dbo.fttest WHERE contains (c2, '"create"') AND CDate > '08/05/2019' AND ID IN ( 654051, 644051, 649106, 465, 105)
5 |--Nested Loops(Left Semi Join, OUTER REFERENCES:([FTSdb].[dbo].[fttest].[ID]))
5 |--Index Seek(OBJECT:([FTSdb].[dbo].[fttest].[idx1]), SEEK:([FTSdb].[dbo].[fttest].[ID]=(105) OR ...
5 |--Table-valued function
一意のインデックス キーで使用される値をプッシュダウンするフルテキスト検索エンジンの機能は、次のメソッドの基礎です。
述語に DateTime データ型の列が含まれている場合は、一意のインデックス キー列に日付情報を含めて、この述語に一致する行のみが返されるようにすることができます。 これを行うには、キー列に日付情報を論理的に組み込む必要があります。 ただし、クエリを使用するキー列のデータ型とアプリケーションを変更する必要がある場合もあります。
メソッドを実装するには、フルテキスト一意キー ID のデータ型を BIGINTに変更します。 キー ID の最初の 4 バイトは、日付列の年、月、日付の値をキャプチャし、最後の 4 バイトは同じままです。 たとえば、キー ID の最初のバイトは年を参照でき、次のバイトは月を参照でき、最後の 2 バイトは日付を参照できます。 アプリケーションは、このデータ型の変更に対応する必要があります。
次に、範囲述語をキー ID の述語に変換します。 たとえば、 x < CDate < y 範囲述語は、 (x*2^32 < ID < y*2^32) 述語に変換できます。 変換された述語はフルテキスト キーの述語であるため、述語はフルテキスト ストリーミング テーブル値関数 (STVF) にプッシュダウンされます。 この動作により、日付範囲内の検索が効果的に実行されます。