Hi all,

I'm new to Hibernate. I've read (almost) all the documentation and spent hours reading the forum.
My application is a Java standalone one, which will be the base for a batch process.
It runs on AIX and DB2 8.1.
Here's the code:

try {
sessionFactory = new Configuration().configure().buildSessionFactory();
session = sessionFactory.openSession();
tx = session.beginTransaction();
query2 = session.createQuery("from VbsSale as s where s.orderNo = '1234' and mach_type = ?");
query2.setCacheable(true);

for(int j=0; j < 1000; j++) {
System.out.println("#"+j);
query2.setParameter(0, ""+j);
query2.list();
}

query1 = session.createQuery("from BpSp2Fd as f where f.status='N'");
list = query1.list();
for( int i = 0; i<10; i++) {
sp2 = (BpSp2Fd) list.get(i);
ts = (Timestamp)sp2.getFeedKey();
System.out.println ("Processing FEED_KEY: " + ts.toString() + "," + total);
query2.setParameter(0, ""+total%1000);
query2.list();
}
tx.commit();
session.close();
}
catch (Exception e) {
.......
}



The problem is that query2 executes 500 times per second in the first "for" loop, but inside the other loop it executes 1 time per second.
It never returns a row since I hardcoded one of the values (order_no).
Without executing query2 in the second loop, the loop iterates 18,000 times in 50 seconds, if I add query2 it takes 3 hours.
Both query1 and query2 use database indexes. Query1 return 18,000 rows but in the example I only iterate through 10 of them.
Since there are no 1-many or anything like that, mapping files are very simple so I decided not to flood the forum with it.

Why does query2 executes in a different way inside the loop and outside it? Any suggestions please?

thanks in advance

I found the problem.

I needed to add Session.setFlushMode(FlushMode.COMMIT), since the AUTO value tries to synchronize the cache with the DB just too often.

Thanks to all the ones who tried to think about a solution!