Re: Performance Issue in batch insert query

Hi!

JDBC statement batching is done through Statement.addBatch()/executeBatch() methods. See for example this: https://stackoverflow.com/a/3786127 Concatenating insert statements into one string may or may not work, seems that with modern Oracle JDBC driver it does not.

On Sun, 11 Sept 2022 at 07:42, Pap <oracle.developer35_at_gmail.com> wrote:

> Hi All, We have a 19c migration that happened recently from 11.2.0.4. And
> post this we are seeing one of the insert query which used to happen in
> batch has been now happening in row by row basis. This we can confirm from
> the dba_hist_sqlstat by dividing execution
> sum(rows_processed_delta)/sum(executions_delta), the number used to be
> ~1000 before but now it's showing ~1 i.e. showing a row by row insert
> pattern. This insert used to come from java code and we have no changes
> made in the java code. So I wanted to understand what could be the reason
> for this?
>
> Along with the database move to 19C we also moved the websphere from
> ojdbc6 11.2.0.4 to ojdbc8-19.13.0.0.1. But can it cause this batch size
> difference even if we have not made changes done in the code?
>
> Below is the code how it look like in javs side
>
> java.sql.Connection connection
>
> PreparedStatement statementfront = null;
>
> StringBuilder stbuilder = new StringBuilder(1000);
>
> stbuilder.append(" INSERT INTO TAB1 (c1, c2, c3,.....");
>
> stbuilder.append(" ........");
>
> stbuilder.append(".....,");
>
> stbuilder.append(" ....");
>
> stbuilder.append("......,");
>
> stbuilder.append(" .......");
>
> stbuilder.append("......");
>
> stbuilder.append(".......");
>
> stbuilder.append(" ......");
>
> stbuilder.append(" ........");
>
> stbuilder.append(" ....)");
>
> stbuilder.append(" VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,");
>
> stbuilder.append(" ?, ?, ?, ? , ?, ?, ?, ?, ? , ?, ?, ? , ?, ?, ?,?,
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? , ?, ?, ?, ?, ?, ?,
> ?, ?, ?,");
>
> stbuilder.append(" ?, ?, ?, ?,?,");
>
> stbuilder.append(" ?)");
>
> statementfront = connection.prepareStatement(stbuilder.toString());
>
>

--
http://www.freelists.org/webpage/oracle-l