>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.
Erik,
Make an index tag on jmembnum + activity on the join_cancel table, then;
SELECT Join_cancel
SEEK memb.membnum + "EXPIRE"
REPLACE jDate WITH current_paid_thru
of course if membnum is numeric then the idnex needs to be STR(jmembNum,
) + Activity and the SEEK would be STR(memb.membnum,>Length>) = "EXPIRE"
