Skip to Main Content
  • Questions
  • Execute a procedure up to 5 milion times

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Lucian.

Asked: March 24, 2017 - 3:00 pm UTC

Last updated: March 28, 2017 - 12:52 am UTC

Version: 12g

Viewed 1000+ times

You Asked

Hello,

I represent in my schema the following tables :

Container : contains a column ContainerName (unique) and represents a container
Container_Item : contains a column ContainerItemName (unique) and represents a container item
Container_Candidates: This table contains the link between the "Container" and the "Container_Item" table . Each "Container" can contain any number of "ContainerItems" (even "ContainerItems" that are located in another "Container" )
Elements: contains a column ElementName (unique) that represents an element.
Compounds: This table contains the link between the "ContainerItem" and the "Elements" table . Each "ContainerItem" can contain any number of "Elements" (even "Elements" that are located in another ContainerItem"

When i add a new container i perform the following operations :
1) create an insert in the "Container" table
2) use a procedure to insert a container_item :
* check if the container_item name exists . if exists, obtain it's id and use it in the "Container_Candidates" table
* in case the container_item name does not exist, create it and use the generated id in the "Container_Candidates" table
3) use a procedure to insert an element
* check if the element name exists . if exists, obtain it's id and use it in the "Compounds" table
* in case the element name does not exists, create it and use the generated id in the "Compounds" table

I Execute the upper three operations in a transaction.

This works for smaller structures, however in case i have a container that contains 5.000 items and each item can contain 1.000 unique elements i end up with a transaction of around 5.000.000 operations (1-3) described upper . This is really time consuming, non memory friendly and not user friendly.

Also when i delete the entire structure i have to check which of the inner elements (elements, container_items) are not used by another container/container_item . I perform this using some cursors. Again my code works but it is not very efficient.

Can you give me so ideas how can i achieve an add/update/delete operation of a large structure using the described table structure from above. I would like to have also the benefits of using a transaction.

Thank you.


and Connor said...

As long as you are using array operations, then you can get similar performance levels for many rows as you would for 1 row.

For example, for inserts, in this example, we insert 1 row, 100 rows, and then 1000 rows:


SQL> create table t1 ( x1 int );

Table created.

SQL>
SQL> create or replace
  2  type numlist is table of number;
  3  /

Type created.

SQL>
SQL> set serverout on
SQL> declare
  2    n numlist := numlist();
  3    s timestamp;
  4  begin
  5    n.extend(1);
  6    n(1) := 10;
  7
  8    s := localtimestamp;
  9    insert into t1
 10    select column_value
 11    from table(n);
 12    dbms_output.put_line(localtimestamp-s);
 13
 14    for i in 2 .. 100 loop
 15      n.extend;
 16      n(i) := i;
 17    end loop;
 18
 19    s := localtimestamp;
 20    insert into t1
 21    select column_value
 22    from table(n);
 23    dbms_output.put_line(localtimestamp-s);
 24
 25    for i in 101 .. 1000 loop
 26      n.extend;
 27      n(i) := i;
 28    end loop;
 29
 30    s := localtimestamp;
 31    insert into t1
 32    select column_value
 33    from table(n);
 34    dbms_output.put_line(localtimestamp-s);
 35
 36  end;
 37  /
+000000000 00:00:00.003000000
+000000000 00:00:00.001000000
+000000000 00:00:00.000000000

PL/SQL procedure successfully completed.

SQL> select count(*) from t1;

  COUNT(*)
----------
      1101

1 row selected.

SQL>


You can see the elapsed times are so fast even for 1000 rows.

So use array operations for these things. For updates and deletes, you can also use SQL operations, or you can also consider FORALL operations from PL/SQL. Plenty of examples of FORALL on AskTom.

So dont execute a proc 5000 times...execute it *once* for 5000 rows.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Very helpfull

Lucian Florea, March 27, 2017 - 11:07 am UTC

Thanks for your reply. This is really helpfull idea.
I am thinking of changing my procedure in order to accept two concatenated strings and add the folowing logic :
- create a typed record array
- split using the regexp_substr function the received strings and insert the data into the typed array. ***
- iterate the array and perform the inserts.

Could this help me ?
Regarding (***) can you provide me with a code snippet how can i achieve this.

This is my code :
TYPE RecordType IS RECORD(
   itemId VARCHAR2(100 CHAR),
   itemStatusId NUMBER(38));
 TYPE WorkListType IS TABLE OF   RecordType INDEX BY BINARY_INTEGER;


If i receive two strings like :
str1 = 'item-id01,item-id02,item-id03'
str2 = '4,5,6'

How can i split both strings and insert them into WorkListType ?
so that i have a match between the elements from the two strings.

Thanks.



Connor McDonald
March 28, 2017 - 12:52 am UTC

Here's a video I made on converting strings to rows


More to Explore

PL/SQL demos

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

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library