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 em SQL Server.

Versão original do produto: 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, SQL Server Desenvolvedor 2012, SQL Server 2012 Standard, SQL Server Web 2012, SQL Server 2012 Enterprise
Número de KB original: 2549443

Resumo

Este artigo descreve um método para melhorar o desempenho das 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 projetar o índice de consulta, esquema de tabela e 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 conjunto de dados grande.

Mais informações

Quando você cria uma consulta de pesquisa de texto completo, o fator principal 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 para o mecanismo relacional. Em SQL Server, você pode melhorar o desempenho da consulta filtrando linhas mais cedo para reduzir o número de linhas que devem ser processadas posteriormente.

Em versões de SQL Server lançadas antes de SQL Server 2008, o mecanismo de pesquisa de texto completo retorna todas as linhas que correspondem a um termo de pesquisa e, em seguida, o mecanismo relacional aplica todos os filtros. Melhorias nesse comportamento foram feitas em SQL Server 2008, em SQL Server 2008 R2 e em SQL Server 2012. No entanto, é difícil usar essas melhorias porque os índices de pesquisa de texto completo são organizados de forma diferente dos índices de banco de dados. Além disso, o mecanismo de pesquisa de texto completo e o mecanismo relacional funcionam de forma diferente. Portanto, o método que este artigo descreve usa a TVF (função Table-Valued) para filtrar linhas mais cedo 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 cadeia de CONTAINS 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 exclusiva 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, a TVF retorna uma quantidade muito menor de dados antes que a filtragem adicional deva ser aplicada. Por exemplo, a consulta a seguir especifica cinco valores que devem corresponder à condição c2 e a 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 reduzir os valores usados pela chave de índice exclusiva é a base do método a seguir.

Se um predicado contiver uma DateTime coluna de tipo de dados, você poderá incluir informações de data na coluna de chave de índice exclusiva 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, você também pode ter que alterar o tipo de dados de coluna 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 de 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 poderia se referir ao ano, o próximo byte poderia se referir ao mês e os últimos dois bytes poderiam se referir à data. O aplicativo deve acomodar essa alteração de tipo de dados.

Em seguida, traduza um predicado de intervalo para um predicado na ID da chave. Por exemplo, o x < CDate < y predicado de intervalo pode ser traduzido para o (x*2^32 < ID < y*2^32) predicado. Como o predicado traduzido é um predicado na chave de texto completo, o predicado será empurrado para baixo no STVF (Streaming Table-Valued Functions) de texto completo. Esse comportamento efetivamente executa pesquisas no intervalo de datas.