>Has something changed in VFP8 that makes using the SQL UPDATE with optimistic table buffering slow unless a Tableupdate is performed after each update?
>
>I run this code in VFP7
>
>SET MULTILOCKS ON
>CURSORSETPROP("Buffering",5,'contdb')
>UPDATE contdb SET agent_lock = 0 WHERE idnum = 705999
>UPDATE contdb SET agent_lock = 0 WHERE idnum = 706000
>
>Both execute in fractions of a second, as they should. "idnum" is a fully indexed integer type primary key.
>
>Same code and data in VFP8 the first update runs quickly, not as fast as VFP7 but still less than a second. The second Update takes 60-80 seconds.
>
>In VFP8, if I put a tableupdate in after the first update the second one runs in less than a second.
>
>Anyone have any idea why this is?

Hi Patrick,

VFP8 doesn't Rushmore optimize SQL-UPDATE or SQL-DELETE if target table uses table buffering and the buffer is not empty. This change was made because previous VFP versions don't look into buffer if query is Rushmore optimized and do look into buffer if the query is not Rushmore optimized. This means that the same query potentially affects different amount of records depending on whether there is an index it can use. This issue was discovered very late in the product cycle and the safest way to fix it was to disable Rushmore optimization if there is a possibility of inconsistent result.

Thanks,
Aleksey.