Re: For the GURUS - Load Process slowing down

From: Mark G <someone_at_hot>
Date: Wed, 7 Jul 1999 09:05:46 +0100
Message-ID: <378307f3.0_at_145.227.194.253>


I have read all the threads so far and notices that you said that there are no indexes on your temp table, only on the live table.

I won't go into direct path on sql*load for speed since you probably knw about that already.

However, you say that you do some processing, if the record exists, you do an update else you do an insert.
In the code where you do this processing, which is your driving table? ie If you have a cursor loop, do you take all values from the live table and compare it with the temp table or vice-versa? Also you cannot disable the indexes on your live table during the load since it will slow down any updates you will be doing. If you wee only doing inserts, you could have got away with it.

The ideal solution (ok.. my opinion!) would be to load all the data in the temp table and after the load, index the primary key or the field you search on in your temp table. If you are using some kind of loop and cursors in your code, use the temp table as your driving table since it has less rows. Also, try committing after every 100 transactions.

After a while, regardless on how many indexes you have on your live table, the load will slow down due to the fact that you have a growing table and for each insert/update you do on it, it has to update the indexes as well.

HTH Mark

Ozzy wrote in message <7ltett$62g$1_at_hermes.is.co.za>...
>Hi
>
>I have written a load process (using SPs and Packages) that loads and/or
>updates live 24hr member data. The problem is that as the destination
>member table grows, the load gets progressively slower.
>
>Basically what the process is doing, is loading data, via SQL Loader, into
a
>member load table. It then compares the data in the load table with data
in
>the live member table. If the member exists and the data differs, it will
>update, if there is no match, a row is added. As the table grows, it takes
>progressively longer to load the same amount of rows.
>
>I have spent a long time debugging and running tests and have established
>that the problem does not lie in the searches or the insertion of data
rows,
>but in the growing indexes. Eg. I have removed all the indexes (except
for
>the PK/Unique indexes) and the load becomes constant. As soon as I put the
>necessary indexes on, the load begins to slow. I have also given the
>indexes 500K and 1M extents and no difference is noticed.
>
>Is this a common problem? Will it reach a plateau and become constant
>eventually?
>
>I currently only working with about 25 000 rows.
>
>Any help in this regard will be greatly appreciated. (Maybe my problem will
>be solved when I go onto real serious hardware?)
>
>Thanks
>Ozzy
>
>
Received on Wed Jul 07 1999 - 10:05:46 CEST

Original text of this message