Hi All,
Hibernate looks like it is generating unnecessary queries that are significantly lowering our application's performance and seem to be causing deadlocks. Of course, since we are Hibernate beginners it is probably our fault. At any rate, we are using Hibernate 2.0.3 against MS SQL Server 2000.
The problem is:
We have the following table in our database:
IF_TASKS
--------
PK | ID
FK1| JOB_ID (table IF_JOBS)
FK2| PARENT (table IF_TASKS)
FK3| TYPE_ID (table IF_TASK_TYPES)
.../other column names removed for readability/
This is HQL that we are using to query data from the IF_TASKS table:
Code:
"select t from TaskDTO as t "
+ "where t.job.id = ? and t.type.id = ? and "
+ "((t.taskInfo.state & ?) > 0)",
Of course, t.job.id = JOB_ID and t.type.id = TYPE_ID
Hibernate generates the following queries:
1) select taskdto0_.ID as ID, .../other column names removed for readability/
from IF_TASKS taskdto0_
where (taskdto0_.JOB_ID=? )and(((taskdto0_.STATE&?)>0 ))and(taskdto0_.TYPE_ID=? )
This is cool, because it finds what we are querying for.
2) select tasktype0_.ID as ID, .../other column names removed for readability/
from IF_TASK_TYPES tasktype0_
where tasktype0_.ID=?
As far as we can see, this is unnecessary, because we only need task_type id which is in the same table.
And in case of job id there is no query! Here is an extract form hbm.xml:
Code:
<many-to-one name="job" column="JOB_ID" not-null="true"/>
<many-to-one name="type" column="TYPE_ID" not-null="true"/>
So why is job not queried while type is queried? They are configured the same way in our mapping file.
3) select if_tasks0_.ID as ID__, ..../other column names removed for readability/
from IF_TASKS if_tasks0_
where if_tasks0_.PARENT=?
This is also not need at this stage, this db query sets subtasks,
but subtasks shouldn't be set immediately as it is set to load lazy:
Code:
<set name="subtasks" lazy="true" inverse="true" cascade="all">
<key column="PARENT"/>
<one-to-many class="com.abb.sf.iff.data.orders.TaskDTO"/>
</set>
We have other Sets defined this way which are not queried, for example:
Code:
<set name="materials" lazy="true" inverse="true" cascade="all">
<key column="TASK_ID"/>
<one-to-many class="com.abb.sf.iff.data.orders.TaskMaterialDTO"/>
</set>
So 'subtasks' Set is unneccesarily initialized but other Sets are not initialized (this is correct). All sets are configured the same way in our mapping file.
Here is our mapping file:Code:
<hibernate-mapping>
<class name="com.abb.sf.iff.data.orders.TaskDTO" table="IF_TASKS" proxy="com.abb.sf.iff.data.orders.TaskDTO">
<id name="id" column="ID" unsaved-value="null">
<generator class="identity"/>
</id>
<many-to-one name="job" column="JOB_ID" not-null="true"/>
<many-to-one name="type" column="TYPE_ID" not-null="true"/>
<many-to-one name="parentTask" column="PARENT" outer-join="false"/>
<component name="taskInfo" class="com.abb.sf.iff.data.orders.TaskInfoDTO" insert="true" update="true">
<property name="erpId" column="ERP_ID" type="java.lang.String" update="true" insert="true"/>
<property name="description" column="DESCRIPTION" type="java.lang.String" update="true" insert="true"/>
<property name="plannedStartDate" column="PLANNED_START_DATE" type="java.util.Date" update="true" insert="true"/>
<property name="plannedFinishDate" column="PLANNED_FINISH_DATE" type="java.util.Date" update="true" insert="true"/>
<property name="actualStartDate" column="ACTUAL_START_DATE" type="java.util.Date" update="true" insert="true"/>
<property name="actualFinishDate" column="ACTUAL_FINISH_DATE" type="java.util.Date" update="true" insert="true"/>
<property name="batchQuantity" column="BATCH_QUANTITY" type="java.lang.Integer" update="true" insert="true"/>
<property name="state" column="STATE" type="java.lang.Integer" update="true" insert="true"/>
<property name="mesMilestone" column="MES_MILESTONE" type="java.lang.Boolean" update="true" insert="true"/>
<property name="mspMilestone" column="MSP_MILESTONE" type="java.lang.Boolean" update="true" insert="true"/>
<property name="erpMilestone" column="ERP_MILESTONE" type="java.lang.Boolean" update="true" insert="true"/>
<property name="summary" column="SUMMARY" type="java.lang.Boolean" update="true" insert="true"/>
</component>
<property name="ifActions" column="IF_ACTIONS" type="java.lang.Integer" update="true" insert="true"/>
<property name="lastUpdateTS" column="LAST_UPDATE_TS" type="java.util.Date" update="true" insert="true"/>
<property name="materialsUpdateTS" column="MATERIALS_UPDATE_TS" type="java.util.Date" update="true" insert="true"/>
<set name="subtasks" lazy="true" inverse="true" cascade="all">
<key column="PARENT"/>
<one-to-many class="com.abb.sf.iff.data.orders.TaskDTO"/>
</set>
<set name="materials" lazy="true" inverse="true" cascade="all">
<key column="TASK_ID"/>
<one-to-many class="com.abb.sf.iff.data.orders.TaskMaterialDTO"/>
</set>
<set name="assignments" lazy="true" inverse="true" cascade="all">
<key column="TASK_ID"/>
<one-to-many class="com.abb.sf.iff.data.orders.AssignmentDTO"/>
</set>
<set name="extensions" lazy="true" inverse="false" cascade="all">
<key column="TASK_ID"/>
<one-to-many class="com.abb.sf.iff.data.orders.TaskPropertyDTO"/>
</set>
<set name="components" table="IF_TASK_COMPONENT" lazy="true" cascade="all">
<key column="TASK_ID"/>
<many-to-many class="com.abb.sf.iff.data.components.ComponentDTO" column="COMPONENT_ID"/>
</set>
<set name="confirmations" table="IF_ERP_CONFIRMATIONS" lazy="true" inverse="true" cascade="all">
<key column="TASK_ID"/>
<one-to-many class="com.abb.sf.iff.data.orders.ConfirmationDTO"/>
</set>
</class>
</hibernate-mapping>
Login
FAQ
Search
