Inserting %RowType objects "faster" [message #675846] |
Tue, 23 April 2019 13:55 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/2461d0e588dac21911fad6760eedd39a?s=64&d=mm&r=g) |
andrew-mack
Messages: 1 Registered: April 2019
|
Junior Member |
|
|
I've inherited some fun stuff.
There's a stored-procedure that generates, in memory, a collection of %RowType objects. ~200,000 of them. This stored-procedure is run fairly often during an application's lifecycle.
Generating these objects only takes about 8 seconds. However, replacing the persisted records (delete xxx where xxx .... forall xxx insert into xxx ...) takes several minutes --- ~5 minutes.
I'm tasked with making this persistence portion faster.
I've checked out FK's and Indexes and such ... and nothing appears to be out-of-sorts. I messed around with the few FK's and Indexes that are there anyways, just to see what might happen, and didn't get anything close to the results we're looking for.
Would anyone have any ideas? Any pointers or places/things to look for or try to make this persist faster?
|
|
|
Re: Inserting %RowType objects "faster" [message #675847 is a reply to message #675846] |
Tue, 23 April 2019 14:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Welcome to the forum.
1/ Are you sure you need to generate the in-memory collection and not use a simple INSERT-SELECT?
2/ Of course, FORALL loop is slower than a pure SQL solution
3/ Of course, writes (INSERT) are slower than reads (SELECT) (above all with indexes, check also triggers)
Post your code.
Before, Please read How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
You can also where the time is spent using SQL trace and PL/SQL profiler.
|
|
|