Convert PLSQL function to process a jdbc statement that utilizes an Oracle ARRAY

Question

I have the following PL/SQL block that creates a procedure to insert a new cat:

CREATE OR REPLACE PROCEDURE INSERT_NEW_CAT(AGE NUMBER, WEIGHT NUMBER, NAME VARCHAR2)  
AS  
BEGIN    
     INSERT INTO CATS(cat_id,age,weight,name)  
     VALUES(cat_id_sequence.nextval,age,weight,name);  
END  INSERT_NEW_CAT;

And the following Java block to batch insert these cats:

CallableStatement statement = conn.prepareCall("{CALL insert_new_cat(?,?,?)}");  
for(int i = 0; i < 50000;i++)  
{  
    statement.setInt(1, i);  
    statement.setInt(2,i);  
    statement.setString(3,"Test");  
    statement.addBatch();  
    if(i % 16382== 0)  //max value my batch can support on my hardware
    {  
         statement.executeBatch();  
    }  
}  

statement.executeBatch();

So the issue with this is I can only persist 700 records a second, which is quite slow. I believe the issue is that I invoke the above PL/SQL function once per row (or once per batch entry). How can I re-write the above function to take an array of these statements and do a bulk insert to reduce the number of calls to the function to N modulo 16382 + 1? The database is Oracle 10g.

Just a question. Do you think it might be more performant if you executed batch every 100 records or so instead of doing it after 16000 odd records? — sethu
– sethu, Commented Aug 17, 2012 at 15:16
@sethu I believe the more calls to the database the more the performance would degrade so no. Also as an FYI performant is not a word :) — Woot4Moo
– Woot4Moo, Commented Aug 17, 2012 at 15:27
Performant most certainly is a word. And also, I was coming more from the memory angle. Memory will be held until you execute batch. This might slow you system down. You might have to experiment and get the correct batch size. — sethu
– sethu, Commented Aug 18, 2012 at 8:37
Unrelated to the question, but ... english.stackexchange.com/questions/38945/… — Burhan Ali
– Burhan Ali, Commented Aug 20, 2012 at 15:15

davidmontoyago · Accepted Answer · 2012-08-17 14:14:19Z

1

Create a DB object type:

CREATE TYPE CAT AS OBJECT(
  AGE NUMBER, WEIGHT NUMBER, NAME VARCHAR2
);

Then create a collection type of the same:

CREATE TYPE CATS IS TABLE OF CAT;

In your PL/SQL procedure receive the list of cats and use FORALL -> This is important for performance.

From java build your CAT objects and CATS collection using StructDescriptor and ArrayDescriptor.

edited Aug 17, 2012 at 14:14

answered Aug 17, 2012 at 14:06

davidmontoyago

1,83514 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Woot4Moo · Accepted Answer · 2012-08-17 16:19:40Z

I was able to solve the problem using the following steps:

Creation of Oracle objects to reference my Cat attributes:

Create Type age is table of number  
Create Type weight is table of number  
Create Type name is table of varchar(50)

Then in my Java code I did the following:

ArrayDescriptor ageCollection = ArrayDescriptor.createDescriptor("AGE", conn);   
ArrayDescriptor weightCollection = ArrayDescriptor.createDescriptor("WEIGHT", conn); 
ArrayDescriptor nameCollection = ArrayDescriptor.createDescriptor("NAME", conn);   
int[] ageArray = new int[50000];  
int[] weightArray = new int[50000];  
String[] nameArray = new String[50000];  
for(int i = 0; i <50000;i++)  
{  
    ageArray[i]=i;  
    weightArray[i]=i;
    nameArray[i] = "Test";
}  
ARRAY oraAge = new ARRAY(ageCollection,conn,ageArray);  
ARRAY oraWeight = new ARRAY(weightCollection,conn,weightArray);  
ARRAY oraName = new ARRAY(nameCollection,conn,nameArray);    

CallableStatement statement = conn.prepareCall("{CALL insert_new_cat(?,?,?)}";   

statement.setObject(1,oraAge);
statement.setObject(2,oraWeight);  
statement.setObject(3,oraName);  
statement.execute();  
conn.commit();

SQL procedure:

CREATE OR REPLACE PROCEDURE  INSERT_NEW_CAT (age age, weight weight, name name)  
AS
BEGIN  
    forall i in 1..age.last  
        insert into cats(id,age,weight,name)  
        vales(cat_sequence.nextval,age(i),weight(i),name(i);  
END INSERT_NEW_CAT;

Of importance to note the line (age age) refers to the data type of age that I created in the oracle database. By implementing the above I was able to improve inserts on a fully indexed table to ~45000 a second. On a non-indexed table this value becomes ~70000 per second.

Collectives™ on Stack Overflow

Convert PLSQL function to process a jdbc statement that utilizes an Oracle ARRAY

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related