Améliorer les performances des requêtes de texte intégral dans SQL Server

Cet article fournit une méthode pour améliorer les performances des requêtes qui utilisent des prédicats de texte intégral dans SQL Server.

Version du produit d’origine : SQL Server 2008 Developer, SQL Server 2008 Enterprise, SQL Server 2008 R2 Datacenter, SQL Server 2008 R2 Developer, SQL Server 2008 R2 Enterprise, SQL Server 2008 R2 Standard, Développeur SQL Server 2012, SQL Server 2012 Standard, SQL Server 2012 Web, SQL Server 2012 Entreprise
Numéro de la base de connaissances d’origine : 2549443

Résumé

Cet article décrit une méthode pour améliorer les performances des requêtes Microsoft SQL Server qui utilisent des prédicats de recherche en texte intégral (tels que CONTAINS et CONTAINSTABLE) et qui filtrent également les données. Par exemple, cette méthode améliore les performances de la requête suivante :

SELECT * FROM dbo.ftTest WHERE CONTAINS(TextData, '"keyword"') AND CDate > @date

Cette méthode vous permet de concevoir la requête, le schéma de table et l’index de recherche en texte intégral de telle sorte que le moteur de recherche en texte intégral filtre les résultats avant qu’ils ne soient envoyés au moteur relationnel. Par conséquent, le moteur relationnel n’a pas besoin de filtrer un jeu de données volumineux.

Plus d’informations

Lorsque vous créez une requête de recherche en texte intégral, le principal facteur qui affecte les performances de la requête est la quantité de données que le moteur de recherche en texte intégral doit traiter avant que les données restantes ne soient envoyées au moteur relationnel. Dans SQL Server, vous pouvez améliorer les performances de la requête en filtrant les lignes tôt afin de réduire le nombre de lignes qui doivent être traitées ultérieurement.

Dans les versions de SQL Server publiées avant SQL Server 2008, le moteur de recherche en texte intégral retourne toutes les lignes qui correspondent à un terme de recherche, puis le moteur relationnel applique les filtres. Des améliorations ont été apportées à ce comportement dans SQL Server 2008, dans SQL Server 2008 R2 et dans SQL Server 2012. Toutefois, il est difficile d’utiliser ces améliorations, car les index de recherche en texte intégral sont organisés différemment des index de base de données. En outre, le moteur de recherche en texte intégral et le moteur relationnel fonctionnent différemment. Par conséquent, la méthode décrite dans cet article utilise la fonction Table-Valued (TVF) pour filtrer les lignes en amont et réduire le nombre de lignes qui doivent être traitées ultérieurement.

Par exemple, le plan de requête suivant retourne 131051 lignes qui correspondent à une CONTAINS chaîne de recherche. En outre, un opérateur de jointure dans le plan effectue un filtrage supplémentaire à l’aide d’une recherche d’index.

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

Toutefois, si la requête inclut la colonne clé d’index unique de texte intégral en tant que prédicat, le moteur de recherche de texte intégral peut utiliser le prédicat pour filtrer les résultats au niveau du texte intégral. Dans ce cas, le TVF retourne une quantité de données beaucoup plus petite avant qu’un filtrage supplémentaire ne soit appliqué. Par exemple, la requête suivante spécifie cinq valeurs qui doivent correspondre à la condition c2, et la fonction TVF retourne uniquement les résultats qui correspondent aux cinq valeurs :

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

La capacité du moteur de recherche en texte intégral à pousser vers le bas les valeurs utilisées par la clé d’index unique est la base de la méthode suivante.

Si un prédicat contient une DateTime colonne de type de données, vous pouvez inclure des informations de date dans la colonne clé d’index unique afin que seules les lignes correspondant à ce prédicat soient retournées. Pour ce faire, vous devez incorporer logiquement les informations de date dans la colonne clé. Toutefois, vous devrez peut-être également modifier le type de données de colonne clé et les applications qui utilisent la requête.

Pour implémenter la méthode , remplacez le type de données de la clé ID unique de recherche en texte intégral par BIGINT. Les 4 premiers octets de l’ID de clé capturent les valeurs d’année, de mois et de date de la colonne de date, et les 4 derniers octets restent les mêmes. Par exemple, le premier octet de l’ID de clé peut faire référence à l’année, l’octet suivant peut faire référence au mois et les 2 derniers octets peuvent faire référence à la date. L’application doit prendre en charge ce changement de type de données.

Ensuite, traduisez un prédicat de plage en prédicat sur l’ID de clé. Par exemple, le x < CDate < y prédicat de plage peut être traduit en (x*2^32 < ID < y*2^32) prédicat. Étant donné que le prédicat traduit est un prédicat sur la clé de texte intégral, le prédicat est poussé vers le bas dans les fonctions de Table-Valued de streaming de texte intégral (STVF). Ce comportement effectue efficacement des recherches dans la plage de dates.