Oracle: drastically reduced performance when using batched procedure instead of batch inserts

Question

I am to bulk insert data into an Oracle 11g database using a stored procedure in a batch. During my tests with the upload I sensed that the time taken to process the data is quite high. So I bypassed the stored procedure and bulk inserted the same data using the insert statement used by the stored procedure. The result: this is way faster.

I know that the easiest solution would be to get rid of the stored procedure but this is what the database guys want me to use. So I want to be sure that the way I coded is not the reason for this performance penalty.

This is the code I use to call the stored procedure:

try {
  jdbcTemplate.batchUpdate( "call foo(?,?,?,?,?)",
   new BatchPreparedStatementSetter() {
  @Override
  public void setValues(PreparedStatement ps, int i) throws SQLException {
      LookupEntry le = cachedEntries.get(i);
     ps.setTimestamp(1, new Timestamp( le....
     ps.setString(2, le....
     ps.setString(3, le....
     ps.setString(4, le....
     ps.setString(5, le...
    }

    @Override
  public int getBatchSize() {
   int size = cachedEntries.size();
   return size;
  }
    });
  } catch (DuplicateKeyException e) {
   log.error( "bummer ..., e);
  }

The stored procedure is defined this way:

CREATE OR REPLACE PROCEDURE "X"."FOO"
(SomeDate in Timestamp, String1 in VARCHAR2, String2 in VARCHAR2, String3 in VARCHAR2, String4 in VARCHAR2)
AS
    begin
    INSERT
INTO
    X.BAR
    (
        SOMEDATE,
        STRING1,
        STRING2,
        STRING3,
        STRING4
    )
    VALUES
        (SomeDate,String1,String2,String3,String4);
end;

When I test this with 7500 entries the upload takes 70 seconds.

But when I copy the insert statement directly into my code (the rest remains unchanged) the same data is stored in 4 seconds.

Do you see a reason for this ? Is there an inefficiency in my code ? Or is there a good reason why calling the stored procedure makes Oracle so slow ?

Calling a procedure will always incur some overhead. When and where are you COMMIT-ing? — Mr. Llama
– Mr. Llama, Commented Jan 7, 2015 at 21:44
That's a really good question which shows me I forgot to take care of something. Obviously Spring does issue an autocommit because I did not commit explicitly but the data is there anyway. — Marged
– Marged, Commented Jan 7, 2015 at 21:56
Out of curiosity, is there a reason you need to be doing it in a procedure instead of a straight insert? Are you needing to possibly insert the data into different tables each time (and a synonym won't work)? — Mr. Llama
– Mr. Llama, Commented Jan 7, 2015 at 22:30
It is entirely possible that a batched execution of a stored procedure doesn't have the optimizations (eg real batching) as available to normal DML; but I haven't worked with Oracle for a while, so I am not sure about this. — Mark Rotteveel
– Mark Rotteveel, Commented Jan 8, 2015 at 7:05
@Marged - Maybe you should consider inserting into a view instead of a procedure. The view can be updated at any time, but as long as it represents as one-to-one mapping of a table, Oracle is more than happy to insert/update/delete it. — Mr. Llama
– Mr. Llama, Commented Jan 8, 2015 at 14:51

Marged · Accepted Answer · 2015-01-09 12:47:21Z

1

I will stick to the direct INSERT batching and no longer use the procedure. Perhaps I will give inserting into a view a try, since this decouples my program from the database structure in a similar way as the procedure does.

answered Jan 9, 2015 at 12:47

Marged

11.1k13 gold badges65 silver badges107 bronze badges

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

Collectives™ on Stack Overflow

Oracle: drastically reduced performance when using batched procedure instead of batch inserts

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related