Text Search Taking Too Long

I am currently on IDS 7.3. I have a table with approximately 600,000 records that contains several free text fields (~char(60)) that I have to search off of. I have to search using a filter such as matches "*searchstring*". My searches are taking alot longer that my users can tolerate and was wondering if I can get some performance tips from someone. I have created indexes on these fields which only help if searching with a wildcard after my search string (matches "searchstring*"). I have not fragmented my table which might help. Any other ideas? Thanks!

Dave Mascorro wrote: > I am currently on IDS 7.3. I have a table with approximately 600,000 > records that contains several free text fields (~char(60)) that I have to > search off of. I have to search using a filter such as matches > "*searchstring*". My searches are taking alot longer that my users can > tolerate and was wondering if I can get some performance tips from someone. > I have created indexes on these fields which only help if searching with a > wildcard after my search string (matches "searchstring*"). > > I have not fragmented my table which might help. Any other ideas? > > Thanks! If the rowsize of your table, less these columns, is large, you could consider splitting your table, removing all such text columns into a table of it own, structured with the primary key of the original table, a code to indicate the column type, and the data in a VARCHAR column. For example, PK1|This is column 1 to be searched|This is column 2 to be searched|Yup, this is the third|... would become Main table : PK1|...(rest of the columns) New table : PK1|1|This is column1 to be searched| PK1|2|This is column2 to be searched| PK1|3|Yup, this is the third| You can, more or less, extrapolate the performance improvement for this specific query, as follows : Old-NumRowsPerPage/New-NumRowsPerPage Needless to say, the performance improvement occurs because few pages need to be sequentially scanned. Unfortunately, significant application code changes are required, but it could be worth it. (I've actually used this method to improved performance of such a query 10 fold : Old-NumRowsPerPage was 3, while New-NumRowsPerPage increased to over 25) Rudy