Hello,
The scenario here is that I would have to query the database to select a row based on three conditions (conjunctions). And there are thousands (10,000 nearly) of such transactions. It takes a long time to execute these transactions.
When doing a batch select(broader select with just one where condition) it takes less that a second to return, though a huge list is returned.
Also, execution of thousands of select statment using mysql preparedStatement doesnt take too much time at all.
So I fear there should be a serious problem with the way we approach hibernate.
Please clarify.
The details are below:

Hibernate version:
3
Mapping documents:
<class name="AdComponent" table="AdComponent">
<id name="id" column="ID" type="long">
<generator class="identity"/>
</id>
<property name="ComponentTypeID" column = "ComponentTypeID" type="integer"/>
<property name="ComponentID" column = "ComponentID" type="long"/>
<property name="AdID" column = "AdID" type="long"/>
....
<query name="getAdComponentByCriteria"><![CDATA[
from AdComponent where AdID = :AdID and ComponentID = :ComponentID and ComponentTypeID = :ComponentTypeID
]]></query>
...
Code between sessionFactory.openSession() and session.close():
Transaction txn = relDBSession.beginTransaction();
Query hq = relDBSession.getNamedQuery("getAdComponentByCriteria");
hq.setLong("AdID", AdID);
hq.setInteger("ComponentTypeID", ComponentTypeID);
hq.setLong("ComponentID", ComponentID);
AdComponent ac = (AdComponent) hq.uniqueResult();
if(ac != null)
ac.getPriority();
txn.commit();
Name and version of the database you are using:
mysql 5
The generated SQL (show_sql=true):
select adcomponen0_.ID as ID13_, adcomponen0_.ComponentTypeID as Componen2_13_, adcomponen0_.ComponentID as Componen3_13_, adcomponen0_.AdID as AdID13_, adcomponen0_.CheckSum as CheckSum13_, adcomponen0_.RelevancyScore as Relevanc6_13_, adcomponen0_.Priority as Priority13_, adcomponen0_.StatusID as StatusID13_, adcomponen0_.ReqTimeStamp as ReqTimeS9_13_ from AdComponent adcomponen0_ where adcomponen0_.AdID=? and adcomponen0_.ComponentID=? and adcomponen0_.ComponentTypeID=?