CODEWITHSUNDEEP
sqlspringoracle-databasejdbcoracle11g
Marged
Marged

Reputation: 10953

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

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 ?

Upvotes: 2

Views: 493

Answers (1)

Marged
Marged

Reputation: 10953

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.

Upvotes: 1

Related Questions