My issue
Hello there I've been working with hibernate for nearly 2 months now. I am pretty convinced about the many assets it can provide over straight JDBC coding. But I'v come lately into more and more issues concerning performance.
As you might see later on, I've a list of services, each service maps to 0...n keywords and mmsAbos. In this case only the abos matter.
In one use case I fetch all abos, so I also just fetch the service and do not make a join between the abos and the service, which results in a subquery for each abo collection.
If I query for abos starting/ending at a given time a join is done as you might see later in the named query. But if do it like that, I can have a cup of coffee.
I've been reading a lot sofar, like the online ref and the book, but I am not sure if I missed one point.
If someone could give me a hint, I'd be very glad.
The question boils down to, am I doing something wrong which fundamentally has such a huge impact on the performance? Could I do things better?
I must say that each collection cann easily have some hundreds items.
Thanks for any help
Tarik
Hibernate version:
2
Mapping documents:
Relevant mappings:
Parent:
Code:
<hibernate-mapping>
<class name="com.someCompany.MMSCAdmin.beans.Service" table="SERVICE" lazy="true" batch-size="20">
<cache usage="read-write"/>
<id name="serviceID" type="long" column="SERVICE_ID">
<generator class="assigned"/>
</id>
<property name="name" type="java.lang.String" column="NAME" not-null="true" length="100"/>
<property name="serviceTable" type="java.lang.String" column="SERVICETABLE" not-null="true" length="100"/>
<!-- Associations -->
<map name="keywords" lazy="true" sort="natural" order-by="KEYWORD">
<key column="SERVICE_ID"/>
<index column="KEYWORD" type="string"/>
<one-to-many class="com.someCompany.MMSCAdmin.beans.Keyword"/>
</map>
<map name="abos" lazy="true" sort="natural" order-by="SENDDATESTART">
<key column="SERVICE_ID"/>
<index column="MMSABO_ID" type="int"/>
<one-to-many class="com.someCompany.MMSCAdmin.beans.MMSAbo"/>
</map>
</class>
<query name="com.someCompany.MMSCAdmin.beans.Service.getAllServicesByAboDates"><![CDATA[
from com.someCompany.MMSCAdmin.beans.Service as service
join service.abos as abo
where abo.senddateStart >= :senddateStart
and abo.senddateEnd <= :senddateEnd
ORDER BY service.serviceID ASC
]]></query>
</hibernate-mapping>
Code:
<hibernate-mapping>
<class name="com.someCompany.MMSCAdmin.beans.MMSAbo" table="MMSABO" lazy="true" batch-size="20" >
<cache usage="read-write"/>
<id name="mmsaboID" type="long" column="MMSABO_ID">
<generator class="assigned"/>
</id>
<property name="senddateStart" type="java.util.Date" column="SENDDATESTART" length="19"/>
<property name="senddateEnd" type="java.util.Date" column="SENDDATEEND" length="19"/>
<property name="serviceID" type="long" column="SERVICE_ID" not-null="true" length="20"/>
<property name="composedID" type="long" column="COMPOSED_ID" not-null="true" length="20"/>
<!-- Associations -->
</class>
</hibernate-mapping>
My DAO for this issue:
Code:
public class ServiceDAO {
/**
* @param abo
* @return @throws
* HibernateException
*/
public static List getAllServicesByAboDates(MMSAbo abo)
throws HibernateException {
Session session = HibernateUtil.currentSession();
Query query = session
.getNamedQuery("com.someCompany.MMSCAdmin.beans.Service.getAllServicesByAboDates");
query.setString("senddateEnd", abo.getSenddateEndAsString());
query.setString("senddateStart", abo.getSenddateStartAsString());
query.setCacheable(true);
return query.list();
}
}
Full stack trace of any exception that occurs:
No exception, just poor performance.
Name and version of the database you are using:
MySQL, 4.0.18
The generated SQL (show_sql=true):
select service0_.SERVICE_ID as SERVICE_ID0_, abos1_.MMSABO_ID as MMSABO_ID1_, service0_.NAME as NAME0_, service0_.SERVICETABLE as SERVICET3_0_, abos1_.SENDDATESTART as SENDDATE2_1_, abos1_.SENDDATEEND as SENDDATE3_1_, abos1_.SERVICE_ID as SERVICE_ID1_, abos1_.COMPOSED_ID as COMPOSED5_1_ from SERVICE service0_ inner join MMSABO abos1_ on service0_.SERVICE_ID=abos1_.SERVICE_ID where (abos1_.SENDDATESTART>=? )and(abos1_.SENDDATEEND<=? ) order by service0_.SERVICE_ID ASC
Login
FAQ
Search
