Low performance calling stored procedures

[mooki]

Hi,
I have written stored procedure going over table and doing some inserts.
I know the performace should be better cause what took Oracle (for same functionality) 10 seconds, took 2.5 minutes.

description :
Cursor
from (about 200,000 records)
gets (1-50) records
where (uniqe indexed 2 decimal columns)

Cursor LOOP doing :
-Specific SELECT for the record
-check if record not exists (by unique index)
- Insert into table #1
-check if record not exists (by unique index)
- insert into table #2
-check if record not exists (by unique index)
- insert into table #3

***** TOTAL for each table, 150 records inserted

i tried doing simple loop in query analyzer :
loop of 5000
insert
insert
insert
AND IT TOOK ONLY 13 SECONDS (15000 records) so.....

WHAT IS WRONG ?? WHY IS IT TAKING HIM SO LONG WITH THE STORED PROCEDURE ???

 

[foxdev]

Do you know which portion of the SP is taking the longest (creating cursor vs. loops)? Robert Bradley

http://www.foxdev.com/

Do you have too much money? Visit

http://www.agrainofhope.org

 

[mooki]

When using the profiler it seems like the opening of the cursor taking the longest time....