Incremental search optimization #mssql

Adrian Wick

Hello all wizzards of Magic,

today i am turning to you about the old sin i have never fixed.

i have a table with 40k-50k records and incremental search is horrible. If i turn off index optimization, everything works, but
the records are not group up. So can someone give me a tip on how to build an index for the table. Here is some more info
about the table itself.

1. counter (A10)
2. description (A40)
3. date1
4. date2
5. date3
6. ...

1. counter (unique) (its the only unique key in this table)
2. descritpion (nonunique) (this field is not used in any other index!)
3. date1 + description (nonunique)
4. ...

The problem is incremental search on column "description". So i guess the problem is the 2nd index. Should it also have a counter as a 2nd segment of the index?
Like description + counter? Should 1st index be clustered index? I have made 11 differend tables with differend variations and the best combination is:
1. counter (unique) + clustered
2. description + counter

And this is only for the table itself to start working the way it should. I also have 8 link queries in that window and 2 subforms ... And, if i read correctly, it also matters the number of
records that are shown in the table, because the array on the table is 0, it means that it fetches the number of records * 5 ... So if i have 30 records online, does that mean that it
fetches 150 main recrods and since i have 8 link queries, this means 150 * 8 = 1200? And to put the cherry on the top :) i use descending order in task->range/locate->expressions for
range and locate

Any info is much appreciated!


Join to automatically receive all group messages.