Mejorar el rendimiento de las consultas de texto completo en SQL Server

En este artículo se proporciona un método para mejorar el rendimiento de las consultas que usan predicados de texto completo en SQL Server.

Versión original del producto: 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 2012 Developer, SQL Server 2012 Standard, SQL Server 2012 Web, SQL Server 2012 Enterprise
Número de KB original: 2549443

Resumen

En este artículo se describe un método para mejorar el rendimiento de las consultas de Microsoft SQL Server que usan predicados de búsqueda de texto completo (como CONTAINS y CONTAINSTABLE) y que también filtran datos. Por ejemplo, este método mejora el rendimiento de la consulta siguiente:

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

Este método le permite diseñar la consulta, el esquema de tabla y el índice de texto completo de forma que el motor de búsqueda de texto completo filtre los resultados antes de que se envíen al motor relacional. Por lo tanto, el motor relacional no tiene que filtrar un conjunto de datos grande.

Más información

Al crear una consulta de búsqueda de texto completo, el factor principal que afecta al rendimiento de la consulta es la cantidad de datos que el motor de búsqueda de texto completo debe procesar antes de que los datos restantes se envíen al motor relacional. En SQL Server, puede mejorar el rendimiento de la consulta mediante el filtrado anticipado de filas para reducir el número de filas que se deben procesar más adelante.

En las versiones de SQL Server que se publicaron antes de SQL Server 2008, el motor de búsqueda de texto completo devuelve todas las filas que coinciden con un término de búsqueda y, a continuación, el motor relacional aplica los filtros. Las mejoras en este comportamiento se realizaron en SQL Server 2008, en SQL Server 2008 R2 y en SQL Server 2012. Sin embargo, es difícil usar estas mejoras porque los índices de búsqueda de texto completo se organizan de forma diferente a los índices de base de datos. Además, el motor de búsqueda de texto completo y el motor relacional funcionan de forma diferente. Por lo tanto, el método que se describe en este artículo usa la función Table-Valued (TVF) para filtrar las filas antes y reducir el número de filas que se deben procesar más adelante.

Por ejemplo, el siguiente plan de consulta devuelve 131051 filas que coinciden con una CONTAINS cadena de búsqueda. Además, un operador de combinación del plan realiza un filtrado adicional mediante una búsqueda 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

Sin embargo, si la consulta incluye la columna de clave de índice única de texto completo como predicado, el motor de búsqueda de texto completo puede usar el predicado para filtrar los resultados en el nivel de texto completo. En esta situación, la TVF devuelve una cantidad mucho menor de datos antes de que se deba aplicar un filtrado adicional. Por ejemplo, la consulta siguiente especifica cinco valores que deben coincidir con la condición c2 y la TVF devuelve solo los resultados que coinciden con los 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

La capacidad del motor de búsqueda de texto completo para insertar los valores que usa la clave de índice única es la base del método siguiente.

Si un predicado contiene una DateTime columna de tipo de datos, puede incluir información de fecha en la columna de clave de índice única para que solo se devuelvan las filas que coinciden con este predicado. Para ello, debe incorporar lógicamente la información de fecha en la columna de clave. Sin embargo, es posible que también tenga que cambiar el tipo de datos de columna de clave y las aplicaciones que usan la consulta.

Para implementar el método , cambie el tipo de datos de la clave ID única de texto completo a BIGINT. Los primeros 4 bytes del identificador de clave capturan los valores de año, mes y fecha de la columna de fecha y los últimos 4 bytes siguen siendo los mismos. Por ejemplo, el primer byte del identificador de clave podría hacer referencia al año, el siguiente byte podría hacer referencia al mes y los últimos 2 bytes podrían hacer referencia a la fecha. La aplicación debe adaptarse a este cambio de tipo de datos.

A continuación, convierta un predicado de intervalo en un predicado en el identificador de clave. Por ejemplo, el predicado de x < CDate < y intervalo se puede traducir al (x*2^32 < ID < y*2^32) predicado. Dado que el predicado traducido es un predicado en la clave de texto completo, el predicado se insertará en streaming de texto completo Table-Valued Functions (STVF). Este comportamiento realiza búsquedas de forma eficaz dentro del intervalo de fechas.