SQL Full text query too slow

64 views Asked by At

I have some Full Text Catalogs and since some time ago, the query time really slow down, and the only solution I found until now is to rebuild the catalogs, sometimes more than once a day with command:

ALTER FULLTEXT CATALOG TABLE1 REBUILD

Why is necessary to rebuild the catalogs? There are other solutions for this problem?

After run the Rebuild command the query is instantaneous and when slow down could take several seconds or a minute.

My Catalogs have been created by this code:

DECLARE @catalog VARCHAR(255)
DECLARE @pkCatalog VARCHAR(255)
SET @catalog = 'TABLE1' 
SET @pkCatalog = 'PK_' + @catalog

EXEC sp_fulltext_database 'enable'

EXEC sp_fulltext_catalog @catalog, 'create'

EXEC sp_fulltext_table @catalog, 'create', @catalog, @pkCatalog

----------------------------------- INICIO CURSOR: cursorColumn
DECLARE cursorColumn CURSOR 
FOR
select name from syscolumns where id=object_id(@catalog) and xtype = 167
FOR READ ONLY
 
OPEN cursorColumn
DECLARE @colName SYSNAME
FETCH NEXT FROM cursorColumn INTO @colName
WHILE (@@fetch_status <> -1)
 BEGIN  

   EXEC sp_fulltext_column @catalog,@colName,'add'

   FETCH NEXT FROM cursorColumn INTO @colName
 
 END  
CLOSE cursorColumn 
DEALLOCATE cursorColumn 
------------------------------------- FIM CURSOR: cursorColumn 

EXEC sp_fulltext_table @catalog,'activate'

EXEC sp_fulltext_table @catalog, 'Start_change_tracking'
EXEC sp_fulltext_table @catalog, 'Start_background_updateindex'
GO
0

There are 0 answers