1

I need to process 4Millions records in java. Need to copy from AS400 to Oracle. What i am doing is i am commiting records batch by batch. Batch size is user defined. Lets suppose its 100.

I am creating a connection object and creating a batch of 100 records and commiting it. Once i complete commiting 2000 I am creating a new Connection Object by closing the existing one.

So, Now I have the below doubts.

1.once i commit the batch, the data I committed 100 records will be in the memory? In Connection Object? Because i have not closed it?

  1. What If I create a new connection object once i commit 100 records. So in this case i will endup creating connection object like more than 100, and closing it properly. Will it be a performance impact?
Abdul
  • 1,130
  • 4
  • 29
  • 65

2 Answers2

1

Creating JDBC connections is most of the times expensive. Therefore the DataSource concept was made which normaly uses a connection pool to keep connections for reusing.

Here are some ideas about that: How to establish a connection pool in JDBC?

The connection does not hold any data of your queries that is part of the statements you execute. So basically creating and releasing the connection has no memory impact. But you are right for the performance it is relevant.

The statements are also closable so they release the resources after that as well.

Sorontur
  • 500
  • 4
  • 15
0

1) Yes, once you commit the records they belong to the memory. Always commit before close any Connection object.

2) Maybe. You should log your process in order to measure the time you need for open-close Connections. Generally speaking this process is time consuming but in your case the extra time for the open-close may not have a significant impact your performance. You should measure first and decide for the best solution.

Anastasios Vlasopoulos
  • 1,792
  • 22
  • 34