Hi I have written a function which fetch one city of a particular state the hbm mapping of files are given below:

city.hbm

<id name="id" type="integer">
<generator class="sequence">
<param name="sequence">city_sequence</param>
</generator>
</id>

<property name="name">
<column name="name"/>
</property>

<many-to-one name="State" class="com.abc.State">
<column name="site" sql-type="number"/>
</many-to-one>

State.hbm

<id name="id" type="integer">
<generator class="sequence">
<param name="sequence">state_sequence</param>
</generator>
</id>

<set name="city" inverse="true" cascade="all-delete-orphan">
<key column="site"/>
<one-to-many class="com.abc.City"/>
</set>

From my action class I am calling the method

City city = cityService.getCity(String cityName, State state);

IMPL class Method:

public City getCity(String cityName,State state) {

City city = null;
Session session = getSession();
try {
Criteria criteria = session.createCriteria(City.class);
criteria.add(Restrictions.eq("name", cityName))
.add(Restrictions.eq("state.id", state.getId()))

city = (City) criteria.uniqueResult();
if (city == null) {

throw new ObjectRetrievalFailureException(City.class, cityName);
}
}
catch( Exception e ) {
if (log.isDebugEnabled())
log.debug("[getCity and State] "+e);
log.error("[getCity and State ] "+e.getMessage());
}
return city;
}



The whole application invokes when I upload some users into database, before uploading users I also checked the city related to that user for that I invoke above method. The method works fine with few records but it takes lot of time when we upload say 5000 users.
I also put debug statements and found that to execute the
city = (City) criteria.uniqueResult();
it takes 4ms for first record then it keeps on increasing and reached to 150 ms for 8000 users.

I had also tried to put second level caching, as a result the SQL query is not executing every time but it still takes same time .

Please provide some solution. Thanks

You're most likely getting hit with auto flushes. As more objects are kicking around in your session the flush checks are going to take longer and longer. Do a search for batch processing as sounds like the code you're working with is some sort of import?