Re: The best update strategy ? ;)

I have a personal favourite method that takes advantage of the engines ability to read/write quickly and avoids the slow update altogether: Old_table = table to be updated upd_table = table to update from 1) select a.*, b.some_non_null_column from old_table a, outer upd_table b into temp t1 with no log {we've now built a dataset with a flag (some_non_null_column) which says whether the row is to be 'updated' or not. } 2) create new_table (same as old_table) 3) insert into new_table select * from t1 where some_non_null_column IS NULL {i.e. where not updated - all the old data} 4) insert into new_table select * from upd_table {all of the update rows - this particular case assumes that you have an exact image of the old_table record in upd_table - whatever you need to do...} 5) drop old_table 6) rename new_table to old_table With me so far? Now replace steps 3 and 4 with HPL jobs (unless you're using XPS). This method can scream. I actually plan to benchmark this in the near future. (What? Jack do work? Naaaah) cheers j. "Alexander V.Didytch" <unknown.thegreat@usa.net> on 03/27/98 09:37:12 AM To: "informix-list@iiug.org" <informix-list@iiug.org> cc: (bcc: Jack Parker/Boston50/Epsilon) Subject: The best update strategy ? ;) Hi ! Hopes that most of us has the following problem: Let's say we have two table: CustomerOrder -- where all customer order is stored and it has field Status that states where the order is paid or not. The PK (primary key) for that table is Order_ID. PayedOrder -- a temporary table that stores all orders that was paid during last day.It has just one field Order_ID. And the quest is quite simple -- reflect the payment on the order in table CustomerOrder. Restrictions: 1. No ESQL is allowed, all thing must be done in Stored Procedure Language. 2. The size of CustomerOrder is much bigger than PayedOrder 3. The size of PayedOrder is big enough, that's why this quest could not be performed in one transaction. Any suggestions ? :) Sincerely, Alexander P.S. I had seen only 2 ways: 1.start the loop on order_id; begin work; Update CustomerOrder order set Status=1 where Status=0 and order_id between LoopStep and LoopStep+LoopAdjust and exists (select * from PayedOrder a where a.order_id=CustomerOrder.order_id); end work; end loop; OR 2. start the loop on order_id; begin work; FOREACH select Order_id into v_order_id from PayedOrder where order_id between LoopStep and LoopStep+LoopAdjust update CustomerOrder set Status=1 where order_id=vorder_id; END FOREACH; commit; end loop; Each of this ways works badly. May be there is any other solutions ? -- "People will work eight hours a day for pay, 10 hours a day for a good boss, and 24 hours a day for a good cause!" - John C. Maxwell