>>>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

Don't worry, the percentage is small. Using your SCAN suggetstion we would go to the beginning of this thread, check it, please!