>>I have a very high volume form that, upon saving, must update a record in a table with about 280.000 records. All of the relevant fields in the table are indexed. I have tried the two methods below, both with about the same performance results.
>>
>>UPDATE temptest!join_cancel; && EXPIRE record to reflect new paid thru date
>>SET jdate = current_paid_thru;
>>WHERE join_cancel.jmembnum = memb.membnum;
>>AND join_cancel.activity = "EXPIRE"
>>
>>and
>>
>>replace jdate with current_paid_thru for join_cancel.jmembnum = memb.membnum .and. join_cancel.activity = EXPIRE"
>>
>>They each take about 5 seconds to execute. This is unacceptable. Is there something I am overlooking? Is there anything else I need to index or a different way I should be updating this record? I have several other tables with records in the millions that only take a split second to query. Please help, I am at wits end. I am in big trouble if this is the performance I have to live with.
>
>I believe you will improve performance if:
>Select join_cancel
>Set order to tag memact && indexed on jmembnum+activity, use Str() if different types
>Seek memb.membnum+"EXPIRE" && use Str if different types
>do while join_cancel.jmembnum=memb.membnum and join_cancel.activity=EXPIRE"
> Select join_cancel
> Replace jdate with current_paid_thru
> Skip
> if eof()
> go bottom
> exit
> endif
>enddo

Use SCAN...ENDSCAN instead of DO WHILE...SKIP...ENDDO. It's much faster.

Don't SELECT inside of a loop if it's not necessary. SELECT is a slow command.
BTW, SCAN...ENDSCAN automatically reselects the original area at every loop. So, even if you change the area inside of a loop, don't reselect before ENDSCAN.

This solution is faster if the percentage of scaned records is small. If the percentage is high, this method is much slower (because the table is scanned ordered).

Vlad