Re: The best update strategy ? ;)
Should have sent my first response to both - oh well.
I'm not sure where the cutoff is in terms of size of the update set. I
would suspect that with an update set of 60% of the original you should see
serious benefits. I'll bet it's a good deal lower - one of the reasons I
need to benchmark it. As for RI - I don't hold with RI in a DW environment
- which is where I've been relegated lately. If you have RI requirements,
resolve them before the data is loaded or during. Since the data is static
in a DW environment you don't have to worry about keeping it up to date -
except during loads and updates.
cheers
j.
---------------------- Forwarded by Jack Parker/Boston50/Epsilon on
03/31/98 09:02 AM ---------------------------
"Alexander V.Didytch" <unknown.thegreat@usa.net> on 03/31/98 06:03:00 AM
To: Jack Parker/Boston50/Epsilon
cc: "informix-list@iiug.org" <informix-list@iiug.org>
Subject: Re: The best update strategy ? ;)
jparker@epsilon.com wrote:
> 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.
Jake its nice method when
a. No referential integrity is used
b. The amount of "updated" values is much bigger then amount of "not for
update" values.
Anyway, thanks you for the answer !. Sincerely, Alexander
--
"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