Re: PL/SQL SP is slow - 1.2 Million Records - Help me

Hi,

thanks, i appreciate your help,
but i want to do some price calulations and comparisions, business rule. that is the reason i am using SP. other wise i could have used two insert statements.

one for new rows, other for comparision rows.

One more thing i want to know is why the stored procedure is so slow, when loading a cursor of 1.2 million records.

Please, help me , is there any thing that i have to set in the init.ora file. [ FYI : it is a CBO approach ]

Sreedhar v

In article <7ul8qo$n5n$1_at_nnrp1.deja.com>,   v_sreedhar_at_usa.net wrote:
> PL/SQL SP is slow - 1.2 Million Records - Help me
> Hi Guys,
>
> i have three tables,
> prod_old, prod_curr, prod_to_be_approved.
>
> both prod_old & prod_curr is having around 1.2 Million Records.
>
> System configuration ( LIVE SERVER ) : 4 GB RAM, Processors : 4
> Space : 100 GB
>
> i am loading the data in the prod_curr into a cursor
> and try to compare the prod_old,
> if the data is not in prod_old then
> Insert a row into the prod_to_be_approved
> Else
> compare around 12 columns of data
> of prod_old with data of prod_curr
> if any data changed then
> insert a row into the prod_to_be_approved
> else
> DONT PROCESS . continue the loop.
> end if
> end if
>
> if i select say around 100,000 rows,
> it is taking approximately 16 - 28 minutes. (max 35 minutes )when i
> tried to
> select * from prod_curr into a cursor
> that is it, it took two and half days
> and still it was running, so we killed the process.
> PLEASE HELP ME.
>
> Sreedhar V
> sreedharv_at_bigwords.com
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Oct 25 1999 - 13:30:54 CDT