Announcement Announcement Module
Collapse
No announcement yet.
Error trying to insert 20,000 records Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Error trying to insert 20,000 records

    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

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

    Code:
    getHibernateTemplate&#40;&#41;.save&#40;category&#41;;
    application-context.xml file:

    Code:
    <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>

  • #2
    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).

    Comment


    • #3
      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

      Comment


      • #4
        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)

        Comment


        • #5
          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
          Code:
          public void evict&#40;Object object&#41; 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".

          Comment


          • #6
            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.

            Comment


            • #7
              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.

              Comment


              • #8
                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

                Comment


                • #9
                  won't the Hibernate cache for the object be stale?
                  Sure, it will be. You need then to evict the cache
                  Code:
                    SessionFactory.evict&#40;Category.class&#41;;

                  Comment

                  Working...
                  X