Error trying to insert 20,000 records

[Rexxe]

Hello,

Currently I am trying to insert 20,000+ records into my database via a job that will run bi-nightly. I currently have a service method which takes an array of my object, and this service method calls the DAO save method x times. I am using Hibernate for O/R. I keep running into an Out of Memory error and my entire system comes crashing down. What am I doing wrong? Should I be using a different transaction strategy?

Thanks for any help

CategoryService code

for &#40;int i = 0; i < categories.length; i++&#41; &#123;
            categoryDAO.saveCategory&#40;categories&#91;i&#93;&#41;;
        &#125;

CategoryDAO code

getHibernateTemplate().save(category);

application-context.xml file:

<bean id="hibernateTransactionManager" class="org.springframework.orm.hibernate.HibernateTransactionManager">
		<property name="sessionFactory"><ref local="mySessionFactory"/></property>				
	</bean>

<bean id="categoryDAO" class="com.test.dao.impl.CategoryDAOImpl">
		<property name="sessionFactory"><ref local="mySessionFactory"/></property>		
	</bean>

<bean id="categoryService" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">	
		<property name="proxyInterfaces"><value>com.test.service.CategoryService</value></property>
		<property name="target">
	    	<ref local="categoryServiceTarget"/>
		</property>	
		<property name="transactionManager">
			<ref bean="hibernateTransactionManager"/>
		</property>	  	
		<property name="transactionAttributes">
	    	<props>
	      		<prop key="save*">PROPAGATION_REQUIRED</prop>
	      		<prop key="update*">PROPAGATION_REQUIRED</prop>
	      		<prop key="*">PROPAGATION_REQUIRED, readOnly</prop>
	    	</props>
	  	</property>
	</bean>
	
	<bean id="categoryServiceTarget" class="com.test.service.impl.CategoryServiceImpl">
    	<property name="categoryDAO"><ref local="categoryDAO"/></property>    	
  	</bean>

[irbouho]

If you call categoryDAO.saveCategory(categories) 20,000 times, all the calls will be wrapped in the same transaction since you configured categoryService methods to be transacted. This means Hibernate Session will hold all the data changes for your categories in memory and try to persist your objects when the transaction is to be commited.

Try to call categoryService.save* many times with subsets of your categories (if this makes sens as each call will be wrapped in a separate transaction).

:!:
Personnaly I would consider using direct batch JDBC calls (mainly if the inserted/updated objects are not connected to other objects).

[Ben Alex]

This reminds me of our postcode tables... Everything worked fine for Australia, New Zealand, Switzerland, the USA etc (about 50,000 postcodes) but when we hit Canada (I think it was about 700,000+) we had to change to stored procedures. Our application just writes a SQL script to the filesystem and it's handled using scripts from there. Maybe you could do something similar instead? We found performance improved massively too, of course.

Still, 20,000 is still pretty small. Have you tried adjusting your JVM startup settings? http://java.sun.com/docs/hotspot/VMOptions.html

[sethladd]

Hibernate is keeping all those objects in its 1st level cache. You can evict objects out of the cache to keep from seeing that OOM. Check out Session.evict(Object)

[irbouho]

Hibernate is keeping all those objects in its 1st level cache. You can evict objects out of the cache to keep from seeing that OOM. Check out Session.evict(Object)

If you evict objects out of cache, changes will not be persisted to the database: Rexxe is updating his 20,000 objects inside the same transaction.
From Hibernate javadoc

public void evict(Object object) throws HibernateException

Remove this instance from the session cache. Changes to the instance will not be synchronized with the database. This operation cascades to associated instances if the association is mapped with cascade="all" or cascade="all-delete-orphan".

[sethladd]

That is true, thanks for specifying that. I meant to say you can use Session.evict() after you've commited the transaction. This is assuming the 20,000 inserts are being split up into many different transactions.

[Keith Donald]

This does not sound like a job for a O/R mapping tool such as Hibernate!

You will get much better performance by using Spring's JDBC abstraction framework; specifically the batch update support, to perform large database load/manipulation operations. See JdbcTemplate and BatchSqlUpdate in the jdbc.core and jdbc.object packages, respectively.

[Rexxe]

Yeah, I'm thinking that I should use the JDBC template instead and just batch up the inserts. However, here's another question, if I do the inserts on the "backend" (meaning without Hibernate knowing by using straight JDBC) won't the Hibernate cache for the object be stale?

Thanks for the help everyone.....

--Rexxe

[irbouho]

won't the Hibernate cache for the object be stale?

Sure, it will be. You need then to evict the cache

  SessionFactory.evict(Category.class);