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