I've got a rather simple HQL query. This query retrieves about 4000 entities from a database. The query takes
2656 ms. to execute with Hibernate, but only 110 ms. when i execute the translated SQL query through a MySQL
database tool.
1. I've tried to remove everything that has to do with subclassing in the mapping file. No performance improvement.
2. I've tried to change the ID from java.util.UUID to a regular String. No performance improvement.
3. I've tried to remove the many-to-one reference to society. No performance improvement.
4. I've tried to remove the order by clause in the HQL statement. No performance improvement.
5. I've disabled anything that has to do with cache, to avoid any overhead. No performance improvement.
I'd be very happy to receive any tips on how i might improve this query! There must be some way to get this query
to perform better than almost 3 seconds?
HQL query:
Code:
from Stakeholder s left join fetch s.society order by s.name
SQL query:
Code:
select stakeholde0_.id as id9_0_, society1_.id as id13_1_, stakeholde0_.updated as updated9_0_, stakeholde0_.name as name9_0_, stakeholde0_.cae as cae9_0_, stakeholde0_.status as status9_0_, stakeholde0_.flag as flag9_0_, stakeholde0_.society_id as society8_9_0_, stakeholde0_.type as type9_0_, society1_.updated as updated13_1_, society1_.name as name13_1_, society1_.status as status13_1_, society1_.flag as flag13_1_ from stakeholder stakeholde0_ left outer join society society1_ on stakeholde0_.society_id=society1_.id order by stakeholde0_.name
Mapping File
Code:
<class name="com.tracker.db.entities.Stakeholder" table="stakeholder">
<id column="id" length="36" name="id" type="com.tracker.db.usertypes.CustomUUID"/>
<discriminator column="type" type="java.lang.String"/>
<version column="updated" name="updated" type="java.util.Calendar" unsaved-value="null"/>
<property column="name" length="150" name="name" type="java.lang.String"/>
<property column="cae" length="15" name="cae" type="java.lang.Integer"/>
<property column="status" length="1" name="status" not-null="true" type="java.lang.Integer"/>
<property column="flag" length="1" name="flag" not-null="true" type="java.lang.Integer"/>
<many-to-one class="com.tracker.db.entities.Society" column="society_id" name="society"/>
<subclass discriminator-value="PA" name="com.tracker.db.entities.Patronym" />
<subclass discriminator-value="PS" name="com.tracker.db.entities.Pseudonym" />
<subclass discriminator-value="GR" name="com.tracker.db.entities.Group" />
</class>
Statistics:
Code:
** Query Statistics **
Qyery Execution Max Time: 2656
Qyery Execution Count : 1
Entity Load Count : 3866
Collection Fetch Count : 0
** Cache Statistics **
Qyery Cache Hit Count : 0
Qyery Cache Put Count : 0
Qyery Cache Miss Count : 0
Login
FAQ
Search
