For the GURUS - Load Process slowing down

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 Tue Jul 06 1999 - 12:39:32 CDT