0

I have a query that populates table data from another table. Now it looks like this.

String query = "INSERT into TEMP_TABLE_ID (SELECT USERS_ID, rownum, US_NUMBER from USER_LOG_NUMBER where USERS_ID=(?))";

    int count = db.createNativeQuery(query).setParameter(1, usersId).executeUpdate();

On 10 000 records it execute about 10 seconds. But there will be cases when there will be 10,000,000 records and I'm afraid that it will be slow. Can i do it faster?

Egor Tverkov
  • 23
  • 4
  • `addBatch()`. `addBatch()`. `addBatch()`. `executeBatch()`. – Elliott Frisch Sep 04 '22 at 14:29
  • The link https://stackoverflow.com/questions/3784197/efficient-way-to-do-batch-inserts-with-jdbc has nothing to do with the question: optimizing a set of different INSERT instructions has nothing to do with the problematic of 1 INSERT of several million rows. Whatever the way you wrap the query - batch processing, PL/SQL, ... - at the end the DB has to do the actual job of moving the large set of rows. – p3consulting Sep 05 '22 at 05:23
  • The real problematic here is more what is the business requirement behind the INSERTing of millions of rows already existing the DB into what looks like a temporary table (is it really one?), especially if the source table looks like a logging one, so you may be also hit by a lot of restart events (when ORACLE decides to redo the query to maintain READ consistency of the TRANSACTION scope). To minimize the risk and optimize the INSERT speed, you should disable all triggers and indexes on TEMP_TABLE_ID. – p3consulting Sep 05 '22 at 05:36

1 Answers1

0

You can use Batch statement.

Batch Processing allows you to group related SQL statements into a batch and submit them with one call to the database.

First you have to create a statement using createStatement().

Statement statement = connection.createStatement();

Then add your SQL queries in statement using addBatch() method.

String firstQuery = "";
statement.addBatch(firstQuery);
String secondQuery = "";
statement.addBatch(secondQuery);
....
...
..
.

You can use any kind of loop for adding your queries.

Finally execute all the added queries by executeBatch().

statement.executeBatch();

Good luck.

Danial Eskandari
  • 154
  • 4