Hello,

I'm using b]Hibernate 3.0[/b], Tomcat 5.0.28, JDK 1.4.2_07 and Jtds 1.0.2
and MS SQL sever

I have a big flat table containing about 200 fields. This table has few millions rows.
For a given list of fields I want to get one value (any) and also the list of all the distinct value for that field.

Let say I need to get one value for 40 fields out of the 200.
One thing I thought about was get that value for each field. So I generated 40 selects in a while loop that were getting each value.

I noticed that it was really fast the first time I was getting the first 40 values (each select was taking few millisec) but if I was running this again some queries would take up to 10 sec in the second batch!

I invertigated a little, changed hibernate setup, used a different connection pool (c3p0, DBCP), used Jdts, microsoft jdbc driver. But I could not get the second call as fast as the first one.
Some select just takes forever for some reason the second time where they are really fast the first time.
Memory is fine (I just load 40 small object in the memory). There is no garbage collection happening that would explain why the select would take so long.

Does anyone has a clue about what's going on?

Thanks,

Richard