How to improve performance of bulk inserts in Hibernate and Spring

Hello Experts,

I have an import that needs to import a colon delimited file into a database and then cache calculations into the database. At first the imports of the text files were extremely slow. I then found an the hibernate article on setting batch sizes.
I have set my JDBC batch size to 50 after a lot of benchmarking with this number.
<prop key="hibernate.jdbc.batch_size">50</prop>
I then only flush and clear every 2000 records, which results in a multi-second per minute increase in performance.

The performance issue I am running into still is that I have to query tables using a multiple join query to get count information and then cache that in a table for real time access. These cached values add up to millions of records and requires a query and then an insert for each record created. I've tweaked my performance to 100,000 every 5 minutes.

My question is, are there any other options to increase speed. Would splitting my process into multiple threads make it faster somehow? I really need to be about 10x faster.

Thanks,

Jared

//Query for this by looking for monitor/countType/component AND severity/foundIn is null
                sql = new StringBuilder();
                sql.append("from WeeklyBugChange change where change.weekDate = :date");
                sql.append(" and change.countType = :countType");
                sql.append(" and change.component = :entity");
                sql.append(" and change.bugHistory.bug IN " +
                               "(select bug from Bug bug JOIN bug.bugMonitorHistory monHist where monHist.monitor = :monitor" +
                                   " and monHist.startDate <= :date and (monHist.endDate >= :date OR monHist.endDate IS NULL))");
 
                int size = hibernateTemplate.findByNamedParam(sql.toString(),
                                                     new String[] {"date", "monitor",
                                                                    "countType", "entity"},
                                                     new Object[] {weekDate, monitor, countType,
                                                                    entity}).size();
 
 
                rollup = new WeeklyRollup(weekDate, monitor, countType, size);
                rollup.setComponent(entity);
                getHibernateTemplate().save(rollup);
                recordsCached++;
                if(recordsCached % 2000 == 0){
                    getHibernateTemplate().flush();
                    getHibernateTemplate().clear();
                }

Select allOpen in new window