Re: Insert / update in big tables

guillauminb_at_hotmail.com wrote:
> Batch is coded using proprietary app (PeopleSoft Application Engine ).
> For all concerns it can be considered as PL/SQL(mostly).

I don't know how that particular product works but a possible source of slowdown can be too much communication back and forth (i.e. if the tool does individual checks / inserts vs. set oriented operations).

> I don't understand the word 'SP' please explain. Checks are made this
> way (for now) :
> UPDATE TEMP_TABLE SET error_flag = 'Y' / ID = (SELECT ID FROM TABLE)
> WHERE (NOT) EXISTS (CHECK QUERY )
>
> The 10 lines correspond to the 10 possible values for usage. They are
> hard coded for now. I propose to join the translation table containing
> all the values for usage instead, using case to decide which field to
> use. The file is not loaded in direct path but the trace file I've seen
> indicates I wouldn't win that much time(and I would have to generate
> the internal ids another way they are now)
>
> The temp table look like this
> EXT_ID1,ext_id2,UPD_FLG,product_line_id(generated),fields..,
> field_usage_1,field_usage_2...field_usage_10.
>
> The indexes are all here there is no problem. IO throughput doesn't
> seem to be the problem either(disks are reported 5% busy).

What about CPU?

> Since we are in init phase there aren't much updates nearly only
> inserts and they last too long(45 min for a 10 000 lines file/total
> time :48 minutes).

I still do not have a clear picture of what is happening in your DB, maybe it helps to post concrete DDL and the code that PeopleSoft generates.

Kind regards

        robert Received on Wed Mar 22 2006 - 07:48:41 CST