Oscar Zarate |
Cuando tenemos la necesidad de manejar campos con grandes cantidades de texto empiezan nuestras complicaciones y esto se potencia si el usuario necesita buscar una palabra, por supuesto no la primera palabra de la frase, sino una que esta en cualquier parte de la frase. Aquí es donde hacen su aparición triunfal los productos que permite hacer índices sobre estos campos.
Usando Full Text Index con MS SQL Server
Un claro ejemplo donde encontramos esta necesidad es un sistema para bibliotecas, donde tenemos una tabla con los datos de los libros y esta tabla tiene un campo con un breve resumen (70 palabras) del contenido del libro y debe existir la posibilidad de buscar una palabra dentro de esta “breve” descripción.
Es cierto que existen instrucciones para hacerlo como el LIKE y los meta caracteres de búsqueda, pero ¿intentaron alguna vez hacer una búsqueda de una palabra dentro de una tabla de 20000 registros con un campo de 2100 caracteres con un LIKE?
Ahora bien, si Uds. quieren hacer una búsqueda similar a la anterior y obtener la respuesta en tiempos razonables van a necesitar un índice especifico, no de los clásicos sino uno especial, que en Microsoft SQL Server (SQL) lo administra Full Text Index (FTI).
Les cuento una prueba real, en una tabla de 2 millones de registros que tiene un campo de 75 caracteres, hacer una búsqueda con LIKE que retorna un registro con la cantidad correspondiente (SELECT COUNT) lleva 1’ 22” (un minuto y 22 segundos) y la misma consulta a través de un FTI tarda 5” la primera vez y 3” las siguientes. Nada mal.
Full Text Search es el servicio de indexación de texto libre del que dispone Windows y se puede usar para búsquedas en páginas HTML entre otras tantas cosas. Este servicio está integrado a Microsoft SQL Server 2000 (se instala junto con el servidor de base de datos) y con Microsoft SQL Server 7.0 (pero hay que solicitar la instalación, puesto que no está seleccionada por default). Este mismo servicio es el que resuelve nuestro problema.
Figura 1: Componentes de SQL Server (tomado del Book OnLine de Microsoft SQL Server)
Cuando instalen FTI verán que dentro del SQL Service Manager (icono que se ubica en el Tray Icon) un ítem (en el combo de servicios) correspondiente al Microsoft Search. Ahí podrán ver que este servicio funciona en un proceso separado a SQL y que puede estar parado o funcionando por separado. O sea, el FTI estará STAR o STOP mas a allá que el SQL esté START. Al igual que el SQL, podemos setear para que el servicio de FTI se inicie al arrancar la máquina.
Como he mencionado el servicio funciona por separado, como así también el manejo de índice (sí bien está muy integrado). Esto implica que cuando quieran usar un índice de FTI deban habilitarlo desde una opción especial.
Cada tabla de una base de datos podrá tener un archivo de FTI relacionado, pudiendo tener dentro de este índice, uno o más campos indexados. A este índice se lo conoce como CATALOGO.
A diferencia de un índice común de SQL, al finalizar la creación de un catálogo este no está listo para ser usado sino que debe ser populado. Este es un proceso que se dispara por separado y que hace que el índice se cargue con el contenido correspondiente.
Veamos un ejemplo simple: en este caso vamos crear un catálogo dentro de la base de datos PUBS que viene con SQL, para la tabla TITLES.
Figura 2: Creando un catálogo dentro de una base de datos
Desde el Enterprise Manager, hacemos click con el botón derecho sobre la tabla y elegimos Full-Text Index Table y dentro de esta la opción Define Full-Text Indexing on a Table... que es la única habilitada. Esto nos abrirá un Wizard para crear el catalogo.
Los pasos son:
- Seleccionar el índice de unicidad (Unique index).
- Seleccionar los campos a indexar. En este ejemplo el campo TITLE.
-
Seleccionar el nombre físico del archivo que contendrá el catálogo y su ruta. En el caso del ejemplo la ubicación será la default y el nombre del catalogo TITULOS.
-
Luego nos ofrece crear una tarea programada (Schedule Task) para popular el catálogo, que en nuestro ejemplo la omitiremos para realizar el llenado del catalogo a mano.
-
Finalmente confirmamos la creación del catálogo.
Bien, luego de estos pasos tenemos el catálogo creado pero vacío, como siguiente etapa popularemos el catálogo.
Desde el Enterprise Manager, hacemos click con el botón derecho sobre la tabla y elegimos Full-Text Index Table y dentro de esta la opción Start Full Population esto nos retornará una ventana indicando que el proceso se inició.
Figura 3: Poblando un catálogo
Como dije antes, esto se corre en un proceso separado por lo cual no recibiremos aviso sobre cuando termina el proceso. En este caso, al ser una tabla pequeña (por default trae 18 registros) el tiempo que tarde en popular será muy chico, pero en tablas grandes los tiempos aumentarán exponencialmente.
Para saber el estado de este proceso, podemos hacer click en la carpeta de catálogos de Full-Text (Full-Text Catalogs) de la base de datos PUBS. Ahí veremos que existe un registro del catálogo recién creado y si nos fijamos en la columna correspondiente a estado (Status) dirá “Idle” cuando halla terminado el proceso o “Population in Progress” durante el proceso.
Finalmente haremos una prueba sobre el Query Analizer donde escribiremos
select * from titles where contains(*, 'silicon')
veremos que retorna dos registros correspondientes a dos libros en cuyo título figura la palabra Silicon.
Aquí finaliza la introducción al uso de Full Text Index, vimos una explicación somera de los componentes y vimos como crear, popular y usar un índice de texto. A continuación explicaré como utilizar esto dentro del mundo real de los sistemas, administración avanzada de los índices de FTI y los pros y los contras de este sistema de trabajo.
NOTA: Durante mis años de trabajo con FoxPro tuve que resolver este tema con DBFs y lo hice con un producto llamado PHDBASE que lamentablemente se ha dejado de comercializar. Digo lamentablemente porque es un producto que trabaja muy bien con las DBF y el uso es casi transparente. Solo debemos agregar el manejo de esta librería y aprendernos unos simples predicado y podremos indexar campos MEMO. Actualmente existen otros productos que hacen este trabajo sobre DBFs, pero no puedo dar referencias puesto que no he trabajado con ellos.
Usando Full Text Index en la vida real
Si bien el ejemplo anterior es completamente real, no sería un caso en el cual es imprescindible el uso de FTI, puesto que una búsqueda usando LIKE nos hubiese respondido con eficacia a la consulta (no con eficiencia, pero si con eficacia), pero si tenemos tablas con muchos registros y campos mas variados el uso se hace imprescindible.
Antes de continuar profundizando en el uso de FTI, sería bueno comentar las recomendaciones que tiene Microsoft sobre el uso de este servicio.
Microsoft recomienda mantener los archivos físicos de catálogos en otro disco distinto al que tiene la base de datos. También recomienda que las tablas no superen el millón de registros. Además recomienda máquinas potentísimas con mucha RAM, muchos procesadores y mucho disco. Cumpliendo estas recomendaciones el FTI vuela, ahora bien no todas las instalaciones tienen equipos tan bien dimensionados, pero le aseguro que con máquinas normales (para lo que se necesita como servidor) los resultados son realmente excelentes.
Profundizando en el uso de FTI
El manejo de los catálogos se puede hacer desde el Enterprise Manager (ver el ejemplo anterior) como así también desde el Query Analizer.
Como vimos en el ejemplo el primer paso para generar un catálogo fue la creación del mismo. Lo hicimos desde Enterprise Manager, hacemos click con el botón derecho sobre la tabla y elegimos Full-Text Index Table y dentro de esta la opción Define Full-Text Indexing on a Table... y seguimos el Wizard. Para realizar la misma operación desde el Query Analizer en la base de datos que queramos indexar (PUBS para el ejemplo) haremos:
-- Habilitar la Base de datos para trabajar con FTI
sp_fulltext_database N'enable'
-- Con esta instrucción obtendremos el estado de la base de datos con respecto a FTI.
select DATABASEPROPERTY(DB_NAME(), N'IsFullTextEnabled')
-- 0 => deshabilitada y 1=> habilitada para FTI.
-- Con la invocación a este Store Procedure, crearemos el catálogo llamado TITULOS .
exec sp_fulltext_catalog N'TITULOS', N'create'
-- Con la invocación a este Store Procedure, seleccionamos la clave única
sp_fulltext_table N'[dbo].[titles]', N'create', N'TITULOS', N'UPKCL_titleidind'
-- Es imprescindible tener una clave única.
-- Con la invocación a este Store Procedure, elegimos el campo a indexar y el idioma.
sp_fulltext_column N'[dbo].[titles]', N'title', N'add', 3082
-- El valor 3082 => español moderno (0x0c0a), se puede usar otros como 0 para neutral o 1033
-- para ingles americano (0x0409) o 2057 para ingles de UK (0x0809), etc.
-- Finalmente con la invocación a este Store Procedure, activaremos el catálogo.
sp_fulltext_table N'[dbo].[titles]', N'activate'
El siguiente paso será popular el catálogo que se hace con
-- Con la invocación a este Store Procedure, iniciaremos el populado del catálogo.
sp_fulltext_catalog N'TITULOS', N’start_full’
Con esta instrucción podremos saber el estado en que se encuentra el catálogo.
Select FullTextCatalogProperty(N’TITULOS’, N 'PopulateStatus')
-- Que nos retorna 0 => si está Idle y 1=> si el proceso está corriendo, entre otros valores.
Poblando catálogos
En el ejemplo anterior vimos, tanto en el que realizamos por el Enterprise Manager como desde el Query Analizer, hicimos un Full Population e ignoramos la opción de Incremental Population, esto es porque la primera vez que populemos un catálogo es lo mismo pedir una proceso completo (Full Population) que el llenado incremental del mismo (Incremental Population). El fin real de la opción incremental es para ir agregando registros a la tabla y que estos se vean reflejados en el catálogo (recuerden que FTI trabaja por separado del motor de base de datos y el hecho de agregar registros a la tabla no implica que los mismos están en el catálogo de FTI).
El llenado incremental del un catálogo, solo funciona con los registros agregados a una tabla si la misma posee un campo Timestamp, este se usa internamente para que el motor sepa que registro debe agregar. Si este campo no está presente el llenado será total aún pidiendo un llenado incremental.
Instrucciones de T-SQL para FTI
Las principales instrucciones (en realidad son predicados, puesto que normalmente los usamos como parte de un SELECT) para realizar búsquedas en un catalogo de FTI son:
| Instrucción
| Uso |
| CONTAINS
| Se utiliza para buscar en columnas de con datos tipo texto que tengan coincidencias exactas o aproximadas, con palabras exactas o con cierta cercanía. Se puede buscar una palabra, una frase, el prefijo de una palabra o una frase, una palabra cerca de otra, etc. |
| FREETEXT
| Se utiliza para buscar en columnas de con datos tipo texto que coincidan con el significado y no literalmente con el contenido. Las consultas realizadas con FREETEXT tienen menos precisión que las realizadas con CONTAINS. |
| CONTAINSTABLE
| Devuelve una tabla con una o más filas para aquellas columnas que contengan datos de tipo carácter en las que haya coincidencias exactas o aproximadas con palabras simples o frases, palabras próximas a otra dada (dentro de una cierta distancia), etc. |
| FREETEXTTABLE
| Devuelve una tabla de cero, una o varias filas cuyas columnas contienen datos de tipo carácter cuyos valores coinciden con el significado, aunque no literalmente, del texto especificado. |
Lista de palabras comunes
En todo catálogo existen palabras comunes que son deseables que no sean indexadas. SQL trae un archivo por idioma (NOISE.ESN, NOISE.ENG, NOISE.ENU, etc.) con una lista de palabras comunes que esta en el directorio \MSSQL\FTData\SQLServer\Config. Estos archivos pueden ser modificados y será tenido en cuenta al momento de la populación del catálogo. Si realizamos una búsqueda incluyendo solo una palabra incluida en dicho archivo SQL nos responderá con un mensaje de “solo palabras ignoradas” (A clause of the query contained only ignored words).
Recomendaciones de uso
Microsoft recomienda no superar el millón de registros por tabla, mi experiencia es que con dos millones funciona muy bien, incluso con 4 millones también, pero hay que tener en cuenta que los tiempos de populado son altos.
Ahora bien, ¿qué pasa cuando tenemos tablas con más cantidad de registros? En mi caso les cuento que tuve que realizar sistemas con manejos de 20 millones de registros y conozco casos que había que manejar 50 millones de registros. La solución pasó por dividir la tabla horizontalmente en varias tablas y hacer búsquedas paralelas.
Esto sobre todo es fundamental si se trabaja sobre un SQL 7.0 puesto que si mandamos a popular un catálogo con mayor número de columnas el proceso nunca finaliza. Esto en la versión 2000 está solucionado, pero los tiempos que tarda en popular un catalogo de 2 millones de registros es de unas 6 horas en una máquina con 2 procesadores, 1024 Mb de RAM y discos SCSI y de unas 14 horas en una máquina con 1 procesador, 512 RAM y discos IDE. He realizado pruebas para popular una tabla de 15 millones de registros (con SQL 2000) y tardó 54 horas.
Conclusión
Si alguna vez necesita trabajar con indexación de grandes volúmenes texto sobre tablas de SQL Server, dispondrá de una herramienta realmente poderosa. Full Text Index responde de manera espectacular a las consultas complicadas y es lo suficientemente versátil como para trabajar en máquinas no tan potentes. La única precaución a tener es cuando disponemos de gran cantidad de registros. Deberá disponer de un tiempo inicial alto y tal vez deba pensar en partir sus tablas horizontalmente para obtener excelentes resultados.
Oscar Zárate es Licenciado en Administración de Empresas y Analista de Sistemas. Trabaja como desarrollador desde 1987, pasando desde las primeras versiones del FoxBase a través de Clipper hasta las actuales versiones de Visual FoxPro, como así también ha trabajado con Visual Basic, ASP, XML, SQL Server entre otras herramientas. Actualmente está trabajado en Syncromind con .NET en desarrollos Web e implementación de Metodologías (RUP, MSF, etc.). Se lo puede ubicar en oscar.zarate@mug.org.ar.