SQL Server

Llevamos los dos últimos artículos hablando de los índices de texto completo (FullText) en SQL Server. Hemos aprendido lo qué son y cómo se administran, sin embargo hay un tema, quizá lo más importante, que habíamos dejado para el final. Y no es otro que su uso y el impacto en el rendimiento. ¿Realmente merece la pena este tipo de índices? Vamos a descubrirlo juntos.

Cláusulas específicas para buscar en índices FullText

Normalmente cuando tenemos que buscar texto dentro de una columna en SQL usamos la cláusula LIKE y nos apoyamos en el carácter % que hace de comodín. Sin embargo, cuando hemos añadido una columna a un índice FullText se nos habilitan otras opciones de búsqueda como son CONTAINS, FREETEXT, CONTAINSTABLE y FREETEXTTABLE. Aunque todas son cláusulas para filtrar por texto tienen algunas diferencias que debemos conocer.

CONTAINS: La Precisión

CONTAINS es una cláusula que permite realizar búsquedas de texto completo en columnas de tipo char, varchar o text dentro de SQL Server. Esta cláusula es especialmente poderosa por su capacidad de buscar palabras o frases específicas, e incluso formas de una palabra, dentro de un texto. Por ejemplo, si queremos encontrar todos los registros que contengan la palabra «optimización», CONTAINS nos facilitará esta tarea con gran precisión.

FREETEXT: La Relevancia 

Por otro lado, FREETEXT es menos estricta que CONTAINS y se utiliza para buscar términos que sean «similares» o relacionados con el texto de la consulta, sin necesidad de que sean exactos. Es decir, FREETEXT no solo nos va a devolver los campos que contengan esa palabra sino también los que contengan algún sinonimo.  Esto es ideal para cuando la intención es más importante que la coincidencia literal, permitiendo una búsqueda más «libre» y basada en la relevancia.

CONTAINSTABLE: Resultados Ordenados

CONTAINSTABLE es una función que extiende las capacidades de CONTAINS, devolviendo una tabla de resultados con las filas que coinciden con la búsqueda, junto con un rango de relevancia, conocido como «rank». Esto permite no solo encontrar las coincidencias, sino también ordenarlas por su relevancia, facilitando la identificación de los resultados más pertinentes.

FREETEXTTABLE: Mayor Flexibilidad en la Búsqueda

Similar a CONTAINSTABLE, FREETEXTTABLE aplica los principios de FREETEXT a una tabla de resultados clasificables. Esta función es crucial cuando buscamos entender el contexto general de los términos de una búsqueda dentro de un conjunto de datos, proporcionándonos una clasificación que nos ayuda a discernir la importancia relativa de cada registro encontrado.

Sintaxis y ejemplos de la búsqueda de texto

Ahora que ya sabemos las diferencias entre las distintas búsquedas vamos a ver como se usan en un caso práctico. Para esto vamos a usar la base de datos de ejemplo de Microsoft AdventureWorks. Como ya sabréis (o no, no importa) esta base de datos tiene datos de ejemplo de una tienda de bicicletas. Vamos a buscar, entonces, por la palabra rendimiento en la tabla de descripciones de los productos. Lamentablemente no tenemos una base de datos AdventureWorks con datos en español así que tendremos que buscar en inglés pero es solo por esto. Vosotros tendreis que buscar en el idioma que tengáis vuestros datos y, muy importante, tendréis que haber creado directamente el índice en ese idioma (si no sabéis como se hace os lo conté aquí). 

Vamos a hacer la misma búsqueda con todas las posibles opciones que hemos visto antes:

select *
from Production.ProductDescription
where Description like '%Performance%'

select *
from Production.ProductDescription
where  CONTAINS (Description,'"Performance*"')

select *
from Production.ProductDescription
where  FREETEXT (Description, '"Performance"')

SELECT *
FROM Production.ProductDescription AS FT_TBL
INNER JOIN  CONTAINSTABLE (Production.ProductDescription, Description,   '"Performance*"' ) AS KEY_TBL
   ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]

SELECT *
FROM Production.ProductDescription AS FT_TBL
INNER JOIN  FREETEXTTABLE(Production.ProductDescription, Description, '"Performance"' ) AS KEY_TBL
   ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY]  

Podríamos entrar aquí y ahora en todas las opciones avanzadas de filtrado que incluyen cada una de estas opciones frente al tradicional y simple LIKE pero, convertiría este artículo en eterno y tampoco es el objetivo del post. Si queréis profundizar más tenéis la documentación oficial y si queréis que veamos aquí algunos ejemplos podeis pedirmelo y lo haremos. 

Rendimiento en las búsquedas de texto

Vamos ahora a comparar el rendimiento de las consultas anteriores. Para que el ejemplo tenga más relevancia he creado aproximadamente unos 200.000 registros en la tabla en lugar de los menos de 800 que trae cuando restauramos la base de datos. No sufráis por eso que después de eso también he actualizado los índices y estadísticas de la tabla. Esto nos va a permitir verificar mejor las diferencias.

Estos han sido los resultados ejecutando las anteriores consultas:

Cláusula	Tiempo de CPU	Lecturas lógicas	Porcentaje del plan
LIKE	781 milisegundos	4365 páginas	77%
CONTAINS	< 1 milisegundo	278 páginas	5%
FREETEXT	< 1 milisegundo	278 páginas	7%
CONTAINSTABLE	< 1 milisegundo	278 páginas	5%
FREETEXTTABLE	< 1 milisegundo	278 páginas	7%

Las conclusiones son obvias, el mayor consumo de recursos es con Like, cuando no se usa el índice FullText tardando 780 veces más en completar la consulta y leyendo 34 Mb de información (4365 páginas * 8Kb por página / 1024 = 34 Mb) frente a los solo 2 Mb de información que se leen las consultas que sí hacen uso del índice de texto completo. 

Rendimiento en escrituras

Ya hemos hablado en varias ocasiones de esto pero no está de más recordarlo, los índices en SQL “no son gratis” y por no son gratis me refiero a que hay un precio a pagar por toda esta mejora de rendimiento en las lecturas. En concreto el precio a pagar es un gran empeoramiento de las escrituras. Para verificar hasta dónde es negativo este impacto en las escrituras he creado una tabla en mi SQL Server con la misma estructura que con la que estamos haciendo la prueba y con los mismos datos pero sin el índice de texto completo. Si que tiene el resto de los índices que tiene la tabla original (PK + Nonclustered). A continuación he insertado en las dos tablas los mismos 100.000 registros para medir los tiempos. 

Como podéis ver en la imagen, el resultado de la inserción en la tabla sín índice FullText ha tardado 225 milisegundos frente a 1079 milisegundos en la tabla que tiene índice de texto completo. En cuanto a la información que se ha necesitado leer para hacer la inserción encontramos que para insertar en la tabla con FullText hemos tenido que leer 12,6 Gb de información mientras que para la que no tiene ese índice solo hemos tenido que leer 259,2 Mb.

Conclusión

Los índices de texto completo o FullText pueden ser un gran aliado en las consultas de lecturas, no tanto en las escrituras, donde nos pueden llegar a dar verdaderos problemas de rendimiento. Además, al contrario que en los índices normales donde su creación es transparente, para aprovechar la mejora de un índice FullText tendremos que adaptar nuestro código y usar las cláusulas específicas que usarán el índice. 

Espero que esta serie de artículos te haya ayudado a comprender mejor los índices de texto completo y a poder analizar si son o no beneficiosos para tus escenarios de uso actuales y futuros. Como siempre, te animo a experimentar con estas técnicas y a explorar todas las posibilidades que ofrecen. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de LinkedIn y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

Continuamos con el tema de ayer sobre los índices de texto completo y hoy vamos a centrarnos en aspectos más prácticos. Si aún no has leído el artículo introductorio de ayer sobre los índices FullText te recomiendo que lo hagas antes de seguir con este para saber de lo que estamos hablando. Si ya leíste vamos a hacer un pequeño resumen para reforzar los conceptos clave. Los índices de texto completo o FullText son unos índices especiales que nos ayudarán en nuestras búsquedas sobre columnas con gran cantidad de texto. Además, tienen la particularidad de que se organizan en catálogos, aspecto clave para su creación y futuro mantenimiento.

Requisitos para crear un índice FullText

Para crear los índices FullText lo primero que necesitaremos será tener instalada la característica de SQL con la que trabajan, no tiene pérdida se llama FullText o Texto Completo dependiendo del idioma de nuestro instalador. Una vez instalada tenemos que asegurarnos de tener corriendo el servicio para la extracción de texto completo, tampoco tiene pérdida y lo localizaremos por su nombre rápidamente junto con el resto de servicios en el administrador de configuración de SQL Server. 

Una vez estemos seguros de que el servicio está instalado y en ejecución podremos proceder con la creación del catálogo que, recordad que es requisito imprescindible para este tipo de índices. Para crear el catálogo usaremos la sintaxis siguiente sintaxis: 

CREATE FULLTEXT CATALOG NombreCatalogo;

Para ver los catálogos existentes podemos hacerlo en SSMS, desplegando la carpeta almacenamiento dentro de la base de datos o con la siguiente consulta:

select * from sys.fulltext_catalogs

Creación de índices FullText

Una vez que cumplamos con los requisitos anteriores estaremos ya preparados para crear nuestro índice FullText. Tenemos que saber que podemos crear índices de texto completo tanto en tablas como en vistas indexadas pero solo uno por tabla (o vista). El índice de cada tabla podrá contener hasta 1024 columnas. La sintaxis de creación es la siguiente:

CREATE FULLTEXT INDEX ON table_name
   [ ( { column_name
             [ TYPE COLUMN type_column_name ]
             [ LANGUAGE language_term ]
             [ STATISTICAL_SEMANTICS ]
        } [ , ...n ]
      ) ]
    KEY INDEX index_name
    [ ON <catalog_filegroup_option> ]
    [ WITH ( <with_option> [ , ...n ] ) ]
[;]
<catalog_filegroup_option>::=
 {
    fulltext_catalog_name
 | ( fulltext_catalog_name , FILEGROUP filegroup_name )
 | ( FILEGROUP filegroup_name , fulltext_catalog_name )
 | ( FILEGROUP filegroup_name )
 }
<with_option>::=
 {
   CHANGE_TRACKING [ = ] { MANUAL | AUTO | OFF [ , NO POPULATION ] }
 | STOPLIST [ = ] { OFF | SYSTEM | stoplist_name }
 | SEARCH PROPERTY LIST [ = ] property_list_name
 }

No voy a entrar en todas las opciones de creación de un índice FullText, para eso podéis consultar la documentación oficial aquí. Nos vamos a centrar en las más importantes. Como veis es parecido a la sintaxis de creación de índices que todos conocemos. Especificaremos la tabla sobre la que crearlo y las columnas que incluirá. Como opción podemos definir el idioma del texto de esas columnas para las búsquedas, si no lo especificamos se usará el idioma por defecto de la instancia. Esto es importante si tenéis el servidor en inglés pero los datos en español, por ejemplo. Esta opción de language admite tanto el alias como el lcid de los idiomas de la vista del sistema sys.syslanguages.

Otro de los aspectos clave y que es obligatorio es definir un índice de referencia con la clave para nuestro nuevo índice FullText. Debe ser una clave única que no admita valores nulos. Para un mejor rendimiento se recomienda que sea un identificador único numérico. También debemos definir el catálogo sobre el que se creará el índice. Si no definimos el catálogo se usará el por defecto y si no hay uno por defecto veremos un bonito error, así que, aseguraos de definirlo bien.

Para terminar con esta sección de creación e índices FullText es importante destacar la opción de Change_Tracking que definirá la propagación de nuestro índice. Esto significa que define el comportamiento cuando hay una modificación de la tabla (Insert, Update o Delete). Por defecto está en modo AUTO y los cambios se propagan de la tabla al índice FullText en tiempo real pero podemos definirlo en modo manual y que solo se propaguen con una sentencia SQL que ejecutaremos a voluntad o programaremos en un job. La sentencia para propagar los cambios de la tabla al índice es:

 ALTER FULLTEXT INDEX ON table_name START UPDATE POPULATION

Palabras irrelevantes

Como podéis imaginar, indexar todo un texto puede suponer unos requisitos de espacio tremendos, para evitar esto en la medida de lo posible SQL Server implementa lo que se llaman las palabras irrelevantes y las listas de palabras irrelevantes (stopword y stoplist). Si os habéis fijado antes, a la hora de crear un índice FullText podíamos definir una de estas stoplist.

Palabras irrelevantes

Una palabra irrelevante puede ser por ejemplo un código que guardamos pero que no se usa o palabras que sí tienen significado lingüístico pero que no son relevantes para las búsquedas como podrían ser preposiciones y otras. Por ejemplo en español no puede que no queramos indexar las palabras “un”, “y”, “el”, «de «, «hasta «, etc.

Listas de palabras irrelevantes

Para poder gestionar las palabras irrelevantes SQL Server usa unos objetos que se llaman lista de palabras irrelevantes o stoplists. Podemos encontrarlos desplegando la carpeta almacenamiento dentro de la base de datos (mirad en la imagen de antes). Para crear nuestras propias stoplists podemos hacerlo desde este apartado de SSMS o con la sintaxis CREATE FULLTEXT STOPLIST.

Mantenimiento de índices FullText 

Como ya adelantamos en el artículo de ayer, el mantenimiento de los índices FullText se realiza sobre los catálogos. Para ello podemos hacerlo desde el SSMS haciendo click derecho sobre el catálogo y dando a Rebuild o por T-SQL. Si lo hacemos por T-SQL tendremos la posibilidad de reorganizar los índices sin necesidad de reconstruirlos. Si optamos por un REBUILD se borrarán todos los índices del catálogo y se volverán a crear eliminando así la fragmentación. Puede ser que solo queramos hacer el mantenimiento a un índice, en este caso podemos borrarlo y crearlo de nuevo manualmente. La sintaxis para hacer el mantenimiento de un catálogo es:

ALTER FULLTEXT CATALOG catalog_name   
{ REBUILD [ WITH ACCENT_SENSITIVITY = { ON | OFF } ]  
| REORGANIZE  
| AS DEFAULT   
}  

Si os fijáis, con esta instrucción podríamos también definir este catálogo como el por defecto.

Conclusión

Hoy hemos aprendido cómo podremos lidiar con los FullText índices como DBAs, esto sumado a lo que vimos ayer y a lo que veremos en el próximo artículo sobre su uso es todo lo que necesitamos para controlar este tema. Si queréis profundizar más en detalle os recomiendo bucear por la documentación oficial que os he compartido y por todas las demás páginas de Microsoft sobre los índices de texto completo. A mi me parece un mundo apasionante y del que poca gente conoce todos los detalles. ¿Quién sabe? Igual algún día tener estos conocimientos puede marcar la diferencia para el trabajo de vuestros sueños.

Espero que este artículo te haya proporcionado una visión profunda de los índices FullText o de texto completo en SQL Server. Como siempre, te animo a experimentar con estas técnicas y a explorar todas las posibilidades que ofrecen. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de LinkedIn y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

Volvemos a la carga con un artículo sobre índices, uno de los temas más importantes para el rendimiento de nuestras bases de datos y por ello sobre lo que más contenido tenemos en el blog. Si no has visto los artículos anteriores los tienes todos aquí. A estas alturas no es una sorpresa para nadie que diga que los índices son nuestros mejores aliados para optimizar las consultas y mejorar el rendimiento. Hoy, vamos a sumergirnos en el fascinante mundo de los índices de texto completo o Full-Text index en SQL Server. Este tipo de índice especial y no tan conocido en SQL Server nos va a permitir buscar en grandes cadenas de texto que, sin estos índices siempre es peor que un dolor de muelas.

¿Qué son los Índices de Texto Completo?

Los índices de texto completo son un tipo especial de índice de SQL Server que nos proporciona una solución eficaz para buscar palabras y frases dentro de una gran cantidad de datos de texto en SQL Server. A diferencia de los índices tradicionales, que se basan en la comparación de valores, los índices de texto completo nos permiten realizar búsquedas complejas en textos largos y no estructurados.

Creación de Índices de Texto Completo

Crear un índice de texto completo en SQL Server es un proceso sencillo. Primero, necesitamos una columna con un tipo de datos de texto. Luego, antes de poder crear en índice necesitaremos un catálogo de texto completo. Una vez creado el catálogo podremos crear nuestros índices de texto completo en sí. 

La creación de catálogos es un aspecto fundamental para los índices de texto completo y de cómo los estructuremos ya que nos va a permitir organizarlos y va a afectar al rendimiento futuro de nuestras tareas de mantenimiento.

Uso de Índices de Texto Completo

Una vez creado el índice de texto completo, podemos utilizar la cláusula CONTAINS en nuestras consultas para buscar palabras o frases específicas. También podemos usar la cláusula FREETEXT para realizar búsquedas más generales.

Desventajas de los Índices de Texto Completo

Los índices de texto completo en SQL Server son una herramienta poderosa, pero como cualquier tecnología, tienen sus limitaciones y desventajas. Aquí vamos a nombrar algunas de las más importantes:

Espacio Adicional

Los índices de texto completo ocupan espacio adicional en el disco. Esto puede ser significativo en bases de datos con muchas tablas grandes y varios índices creados. Por lo tanto, es importante considerar el equilibrio entre el rendimiento de la consulta y el uso del espacio en disco.

Rellenado de Índices

El proceso para crear y mantener un índice de texto completo se denomina rellenado (o rastreo). Hay tres tipos de rellenado de índice de texto completo: rellenado completo, rellenado basado en el seguimiento de cambios, y rellenado incremental basado en una marca de tiempo. Este proceso puede ser costoso en términos de recursos y tiempo, especialmente para bases de datos grandes.

Mantenimiento de Índices de Texto Completo

Como cualquier otro índice, los índices de texto completo requieren un mantenimiento regular para garantizar su eficiencia. SQL Server proporciona varias herramientas y técnicas para ayudarnos en esta tarea. Al igual que en el resto de tipos de índices estas son REORGANIZE y REBUILD. Sin embargo, en el caso particular de estos índices las tareas de mantenimiento se realizan sobre el catálogo afectando a todos los índices incluidos y no se puede hacer sobre un único índice a petición. Siempre nos queda la opción de eliminar el índice y volverlo a crear pero no parece una opción aceptable. Por si esto fuera poco es un proceso que no está incluido en las principales soluciones de mantenimiento como OLA Hallengren.

Recomendaciones de rendimiento

Como acabamos de mencionar, optimizar el rendimiento de los índices de texto completo en SQL Server puede ser un desafío. Vamos a ver las mejores recomendaciones que pueden ayudarnos:

Arquitectura y Mantenimiento

Es una buena idea limitar la selección de columnas de clave de texto completo a una columna pequeña. Aunque se admite una columna de 900 bytes, se recomienda usar una columna de clave menor en un índice de texto completo, int y bigint ofrecen el mejor rendimiento.

Podremos reorganizar el catálogo de texto completo utilizando ALTER FULLTEXT CATALOG REORGANIZE. Esto debe hacerse antes de realizar pruebas de rendimiento, ya que la ejecución de esta instrucción produce una mezcla maestra de los índices de texto completo del catálogo.

Uso y consultas

Siempre es mejor combinar varios predicados CONTAINS en un predicado CONTAINS. En SQL Server puedes especificar una lista de columnas en la consulta CONTAINS.

Si solo necesitas información de clave de texto completo o de clasificación, usa CONTAINSTABLE o FREETEXTTABLE en lugar de CONTAINS o FREETEXT, respectivamente.

Para limitar los resultados y aumentar el rendimiento, usaremos el parámetro top_n_by_rank de las funciones CONTAINSTABLE y FREETEXTTABLE top_n_by_rank permite volver a recuperar solo las coincidencias más pertinentes.

Comprobar el Plan de Consultas

Y como siempre, deberemos comprobar constantemente el plan de consultas de texto completo para asegurarnos de que se selecciona el plan de combinaciones adecuado. Usaremos una sugerencia de consulta o forzaremos un plan de ejecución en Query Store si es necesario.

Conclusión

Los índices de texto completo son una herramienta potentísima en SQL Server que nos permite realizar búsquedas eficientes en grandes volúmenes de datos de texto. Aunque su creación y mantenimiento pueden requerir un poco de esfuerzo adicional, los beneficios que aportan en términos de rendimiento y flexibilidad de las consultas valen la pena.

Esperamos que este artículo te haya proporcionado una visión profunda de los índices de texto completo en SQL Server. Como siempre, te animamos a experimentar con estas técnicas y a explorar todas las posibilidades que ofrecen. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de LinkedIn y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!