Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Raja.

Asked: May 20, 2020 - 6:02 am UTC

Answered by: Chris Saxon - Last updated: May 20, 2020 - 9:50 am UTC

Category: PL/SQL - Version: 18C

Viewed 100+ times

You Asked

Hello Experts,

We have a package with object of 50 variables. Package will be invoked with 400 to 500 records with input object and We are looping through this 400 to 500 records and do some manipulation and insert into 4 tables which has sequence. This whole process takes much time and we are trying to do some optimization over it. Can you please let me know how can we improve the performance of the package.

and we said...

To provide effective help here, we really need to see your code! Along with performance metrics, breaking down how long it takes to execute each part. Otherwise we're just guessing.

I discussed ways to get the performance profile in yesterday's SQL Office Hours; the recording will be available for this "soon" at:

https://asktom.oracle.com/pls/apex/f?p=100:551::::RP,551:P551_CLASS_ID:7563

That said, you almost certainly want to address this:

We are looping through this 400 to 500 records and do some manipulation and insert into 4 tables which has sequence

SQL inside loops => SLOOOOOOOOOWWWWWWWW!

Either you want to insert the query directly:

insert into ....
  select ...


Or use bulk collection:

select ... bulk collect into ....

forall i in 1 .. recs.count 
  insert ....

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.