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:

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!