I would check to transaction settings, as the DB gets slower and slower when doing long running transactions (due to undo logs getting bigger and bigger)
As far as I experienced, it's not the best idea to import a lot of data in 1 transaction. Try to devide the import in smaller steps (e.g. 1-1000, 1001-2000) and wrap the transaction around the smaller import. Dont't forget to flush the hibernate session, if you trigger the import via webinterface with an active lazyloadingfilter. This should work. If you need to import new data on regular basis, i recommend to take a closer look at spring-batch (http://static.springframework.org/spring-batch/ ) Bye, Peter 2008/9/18 Juan Heyns <[EMAIL PROTECTED]> > Hello Guys > > I have an Appfuse Struts modular project. > > I am trying to insert 80,000 objects while importing a file. At the moment > I have a test in the web project, to try and guage the speed at which > records are inserted with the connection pool. > > Unfortunately something goes wrong when inserting these objects. 99 inserts > are performed and then I get the following error. > > Any ideas what the problem could be? > > Thanks > Juan > > > > > > [imms] WARN [main] JDBCExceptionReporter.logExceptions(77) | SQL Error: 0, > SQLState: null > [imms] ERROR [main] JDBCExceptionReporter.logExceptions(78) | Cannot get a > connection, pool exhausted > [imms] WARN [main] JDBCExceptionReporter.logExceptions(77) | SQL Error: 0, > SQLState: null > [imms] ERROR [main] JDBCExceptionReporter.logExceptions(78) | Cannot get a > connection, pool exhausted > [imms] ERROR [main] AbstractFlushingEventListener.performExecutions(301) | > Could not synchronize database state with session > org.hibernate.exception.GenericJDBCException: Cannot open connection > at > org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103) > at > org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91) > at > org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) > at > org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29) > at > org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:426) > at > org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144) > at > org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:94) > > > ------------------------------------------------------------------------------- > Test set: com.epiuse.ims.performance.AccountImportPerformanceTest > > ------------------------------------------------------------------------------- > Tests run: 1, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 53.563 sec > <<< FAILURE! > testPerformance(com.epiuse.ims.performance.AccountImportPerformanceTest) > Time elapsed: 53.5 sec <<< ERROR! > org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: > Cannot open connection; uncategorized SQLException for SQL [???]; SQL state > [null]; error code [0]; Cannot get a connection, pool exhausted; nested > exception is org.apache.commons.dbcp.SQLNestedException: Cannot get a > connection, pool exhausted > at > org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:124) > at > org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322) > at > org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:424) > at > org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:410) > at > org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424) > at > org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374) > at > org.springframework.orm.hibernate3.HibernateTemplate.merge(HibernateTemplate.java:820) > at > org.appfuse.dao.hibernate.GenericDaoHibernate.save(GenericDaoHibernate.java:95) > at > org.appfuse.service.impl.GenericManagerImpl.save(GenericManagerImpl.java:89) > at > com.epiuse.ims.io.impl.AccountImportImpl.createAccountImportFailure(AccountImportImpl.java:45) > at > com.epiuse.ims.io.impl.AccountImportImpl.importFile(AccountImportImpl.java:97) > at > com.epiuse.ims.io.impl.FileImportManagerImpl.importFile(FileImportManagerImpl.java:247) > at > com.epiuse.ims.io.impl.FileImportManagerImpl.importFiles(FileImportManagerImpl.java:123) > at > com.epiuse.ims.performance.AccountImportPerformanceTest.testPerformance(AccountImportPerformanceTest.java:40) > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) > at java.lang.reflect.Method.invoke(Method.java:585) > at junit.framework.TestCase.runTest(TestCase.java:168) > at junit.framework.TestCase.runBare(TestCase.java:134) > at junit.framework.TestResult$1.protect(TestResult.java:110) > at junit.framework.TestResult.runProtected(TestResult.java:128) > at junit.framework.TestResult.run(TestResult.java:113) > at junit.framework.TestCase.run(TestCase.java:124) > at junit.framework.TestSuite.runTest(TestSuite.java:232) > at junit.framework.TestSuite.run(TestSuite.java:227) > at > org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:81) > at > org.apache.maven.surefire.junit4.JUnit4TestSet.execute(JUnit4TestSet.java:62) > at > org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.executeTestSet(AbstractDirectoryTestSuite.java:140) > at > org.apache.maven.surefire.suite.AbstractDirectoryTestSuite.execute(AbstractDirectoryTestSuite.java:127) > at org.apache.maven.surefire.Surefire.run(Surefire.java:177) > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) > at java.lang.reflect.Method.invoke(Method.java:585) > at > org.apache.maven.surefire.booter.SurefireBooter.runSuitesInProcess(SurefireBooter.java:338) > at > org.apache.maven.surefire.booter.SurefireBooter.main(SurefireBooter.java:997) > Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot get a > connection, pool exhausted > at > org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:103) > at > org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540) > at > org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:82) > at > org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:423) > at > org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144) > at > org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:94) > at > org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.prepare(IdentityGenerator.java:69) > at > org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:30) > at > org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2163) > at > org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2643) > at > org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:51) > at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279) > at > org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263) > at > org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167) > at > org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298) > at > org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27) > at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000) > at > org.springframework.orm.hibernate3.HibernateAccessor.flushIfNecessary(HibernateAccessor.java:390) > at > org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:420) > ... 32 more > Caused by: java.util.NoSuchElementException: Timeout waiting for idle > object > at > org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:825) > at > org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:95) > ... 50 more > > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] >
