I made an small application which will copy data (180.000 rows) from AS400 DB2 to MySQL database.

The application is built using hibernate and spring.

However, the commit operation take about 30 - 60 minutes (1 hour).

For those 180.000 rows, is this time normal?

Is there any way to improve the commit operation?

Thanks in advance

Hi,

do You execute entire operation in transaction?
In 1 transaction or multiple transactions?

I had same story few years ago.
At first each INSERT/UPDATE statement were done in 1 transaction.
Such process was very slow.

Then I have put that in many transactions with 5-50 statements in single transaction. Entire proccess improved.
Deal with that and check.

Hi,

Would you mind to post the sample please? so, it can increase my knowledge. Thanks

Yes, it is with only 1 transaction.

As SIau_Tie said, Can you share your code?

Thanks in advance,

Solution is here http://www.hibernate.org/hib_docs/refer ... batch.html

Thanks, I have already known this way.

Hibernate is really cool. Hibernate is fast enough.

It can insert 10.000 rows in 6 seconds.

So, I was debugging to see what was the problem.

The problem is with jasypt encription, so there is nothing related with hibernate. The encription process happen when commiting data.

The solution to this problem is use hibernate paging + batch processing with native sql.

Why native sql:
> manually I will encript using concurrence framework, to resolve slow of encryption process
> insert chunk of 1000 rows using session.createSQLQuery(queryString).executeUpdate();