Hi, John.
BEWARE of variable lenght indexes!
An index on alltrim(something) would yield different lenghts for keys. Most probably this would make the index unoptimizable, but even worst, thats sort of indexes are highly prone to corruption.
If you index on "car_no" alone (don't know how long is the field), performance could be higly improved.
Take a look at that and tell me.
>>>This seems quite bad. How large is the table (MB) ? Which indexes did you use ? When retrieving the 5000 records which indexes are used to optimize ? Did you try setting optimization off ?
>
>The table size is 2MB ++. I am using index ALLTRIM(card_no) and i use it in my SQL statement too.
>I will tried to set optimization off later. Why need to set it off?
>
>>>4 seconds, for 5000 records is not that bad, however 1-2 minutes on a 10BASET is bad, very bad. How is network trafic ?
>The network is totally free.