Melhorar o desempenho de consultas de texto completo no SQL Server

Este artigo fornece um método para melhorar o desempenho de consultas que usam predicados de texto completo no SQL Server.

Versão original do produto: SQL Server
Número original do KB: 2549443

Resumo

Este artigo descreve um método para melhorar o desempenho de consultas do Microsoft SQL Server que usam predicados de pesquisa de texto completo (como CONTAINS e CONTAINSTABLE) e que também filtram dados. Por exemplo, esse método melhora o desempenho da seguinte consulta:

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

Esse método permite que você crie a consulta, o esquema de tabela e o índice de texto completo de forma que o mecanismo de pesquisa de texto completo filtre os resultados antes de serem enviados para o mecanismo relacional. Portanto, o mecanismo relacional não precisa filtrar um grande conjunto de dados.

Mais informações

Quando você cria uma consulta de pesquisa de texto completo, o principal fator que afeta o desempenho da consulta é a quantidade de dados que o mecanismo de pesquisa de texto completo deve processar antes que os dados restantes sejam enviados ao mecanismo relacional. No SQL Server, você pode melhorar o desempenho da consulta filtrando as linhas antecipadamente para reduzir o número de linhas que devem ser processadas posteriormente.

O método descrito neste artigo usa a TVF (Função Table-Valued) para filtrar linhas antecipadamente e reduzir o número de linhas que devem ser processadas posteriormente.

Por exemplo, o plano de consulta a seguir retorna 131051 linhas que correspondem a uma CONTAINS cadeia de caracteres de pesquisa. Além disso, um operador de junção no plano executa filtragem adicional usando uma busca de índice.

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

No entanto, se a consulta incluir a coluna de chave de índice exclusivo de texto completo como um predicado, o mecanismo de pesquisa de texto completo poderá usar o predicado para filtrar os resultados no nível de texto completo. Nessa situação, o TVF retorna uma quantidade muito menor de dados antes que a filtragem adicional seja aplicada. Por exemplo, a consulta a seguir especifica cinco valores que devem corresponder à condição c2 e o TVF retorna apenas os resultados que correspondem aos cinco valores:

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

A capacidade do mecanismo de pesquisa de texto completo de enviar os valores usados pela chave de índice exclusiva é a base do método a seguir.

Se um predicado contiver uma coluna de tipo de DateTime dados, você poderá incluir informações de data na coluna de chave de índice exclusivo para que apenas as linhas que correspondem a esse predicado sejam retornadas. Para fazer isso, você deve incorporar logicamente as informações de data na coluna de chave. No entanto, talvez você também precise alterar o tipo de dados da coluna de chave e os aplicativos que usam a consulta.

Para implementar o método, altere o tipo de dados da chave ID exclusiva de texto completo para BIGINT. Os primeiros 4 bytes da ID da chave capturam os valores de ano, mês e data da coluna de data e os últimos 4 bytes permanecem os mesmos. Por exemplo, o primeiro byte da ID da chave pode se referir ao ano, o próximo byte pode se referir ao mês e os últimos 2 bytes podem se referir à data. O aplicativo deve acomodar essa alteração de tipo de dados.

Em seguida, converta um predicado de intervalo em um predicado na ID da chave. Por exemplo, o predicado x < CDate < y de intervalo pode ser convertido no (x*2^32 < ID < y*2^32) predicado. Como o predicado traduzido é um predicado na chave de texto completo, o predicado será enviado para baixo nas STVF (Funções com Valores de Tabela de Streaming) de texto completo. Esse comportamento executa pesquisas com eficiência dentro do intervalo de datas.