Specifics below. Here's the summary.
I have two objects -- "StuScheduleRoot" and "StuSchedule". StuScheduleRoot is the parent of a one-to-many relationsip with StuSchedule (i.e. each StuScheduleRoot may have one or more StuSchedule's associated with it). It's very common for there to be only one StuSchedule (child) per StuScheduleRoot (parent).
For the most part, everything works in hibernate, except delete. When we delete a StuScheduleRoot (the parent), it seems to take along time to complete (2-5 seconds). Turning on debugging shows hibernate issuing several SQL UPDATE's, then the DELETE for the parent and children.
There are no triggers (there ar constraints, but they don't seem to be causing a problem) and no interceptors on the session.
What I can't figure out is why Hibernate is trying to update anything? Why not just issue, in the example case, the two deletes? I suspect these extra updates are causing some or all of the delay in the transaction (other simpler DAO deletes are pretty speedy and hibernate speed with the DB in general is excellent).
I'm really hoping it's just some idiocy on my behalf regarding the mappings. We do periodically have to delete several hundred records this way and right now, that can takes hours.
Any advice or pointers would be very much appreciated!
Gerry
Hibernate version: 3.05
Mapping documents:
Excerpt from StuScheduleRoot
Code:
<list
name="snapshotList"
table="student_schedule"
lazy="true"
inverse="true"
cascade="all">
<key column="root_key"></key>
<index column="snapshot_order"/>
<one-to-many class="SOpen.spm.dao.StuSchedule"/>
</list>
Excerpt from StuSchedule
Code:
<many-to-one
name="snapshotRoot"
class="SOpen.spm.dao.StuScheduleRoot"
cascade="all"
outer-join="auto"
update="true"
insert="true"
column="root_key"
not-null="true"
/>
Code between sessionFactory.openSession() and session.close():Code:
SchoolYearI year = (SchoolYearI) DAOUtils.lookupFirst("From SchoolYear AS year WHERE year.name='2005/2006'", null);
LocationI baseLocation = (LocationI) DAOUtils.lookupFirst("FROM Location AS l WHERE l.code='01'", null);
SchoolYearLocationI location = (SchoolYearLocationI) DAOUtils.lookupFirst("FROM SchoolYearLocation AS syl WHERE syl.schoolYear.id=:yearid AND syl.location.id = :bldgid",
new Object[][] { { "yearid", year.getPrimaryKey() },
{ "bldgid", baseLocation.getPrimaryKey() } });
// get list of StuScheduleRoots to delete
Iterator theSchds = DAOUtils.lookupAll(unschdSession, "FROM StuScheduleRoot AS sr WHERE sr.schoolYear.id=:yearid AND sr.location.id=:bldgid",
new Object[][] { { "yearid", year.getPrimaryKey() },
{ "bldgid", baseLocation.getPrimaryKey() } });
StuScheduleRootI schdRoot;
Transaction theTransaction;
while(theSchds.hasNext()) {
schdRoot = (StuScheduleRootI) theSchds.next();
theTransaction = unschdSession.beginTransaction();
unschdSession.delete(schdRoot);
theTransaction.commit();
}
Name and version of the database you are using:Postgresql 8.1
The generated SQL (show_sql=true):Code:
Hibernate: select stuschedul0_.primary_key as col_0_0_ from stu_schedule_root stuschedul0_ where stuschedul0_.school_year_id=? and stuschedul0_.location_id=?
Hibernate: select stuschedul0_.primary_key as primary1_106_0_, stuschedul0_.record_version as record2_106_0_, stuschedul0_.student_id as student3_106_0_, stuschedul0_.school_year_id as school4_106_0_, stuschedul0_.course_id as course5_106_0_, stuschedul0_.section_id as section6_106_0_, stuschedul0_.schedule_version_id as schedule7_106_0_, stuschedul0_.location_id as location8_106_0_, stuschedul0_.GUID as GUID106_0_, stuschedul0_.changed_by_user as changed10_106_0_, stuschedul0_.changed_at as changed11_106_0_, stuschedul0_.created_at as created12_106_0_, stuschedul0_.created_by_user as created13_106_0_ from stu_schedule_root stuschedul0_ where stuschedul0_.primary_key in (?, ?)
Hibernate: select snapshotli0_.root_key as root3_1_, snapshotli0_.primary_key as primary1_1_, snapshotli0_.snapshot_order as snapshot29_1_, snapshotli0_.primary_key as primary1_107_0_, snapshotli0_.record_version as record2_107_0_, snapshotli0_.root_key as root3_107_0_, snapshotli0_.override_credits as override4_107_0_, snapshotli0_.override_gpa_multiplier as override5_107_0_, snapshotli0_.override_study_area as override6_107_0_, snapshotli0_.earned_credits as earned7_107_0_, snapshotli0_.credits as credits107_0_, snapshotli0_.gpa_multiplier as gpa9_107_0_, snapshotli0_.school_year_id as school10_107_0_, snapshotli0_.location_id as location11_107_0_, snapshotli0_.schedule_version_id as schedule12_107_0_, snapshotli0_.student_id as student13_107_0_, snapshotli0_.course_id as course14_107_0_, snapshotli0_.section_id as section15_107_0_, snapshotli0_.request_id as request16_107_0_, snapshotli0_.schedule_status_id as schedule17_107_0_, snapshotli0_.start_schedule_period_id as start18_107_0_, snapshotli0_.end_schedule_period_id as end19_107_0_, snapshotli0_.study_area_id as study20_107_0_, snapshotli0_.schedule_locked as schedule21_107_0_, snapshotli0_.added_from_id as added22_107_0_, snapshotli0_.dropped_from_id as dropped23_107_0_, snapshotli0_.scheduled_as as scheduled24_107_0_, snapshotli0_.change_reason_id as change25_107_0_, snapshotli0_.change_comments as change26_107_0_, snapshotli0_.change_effective_date as change27_107_0_, snapshotli0_.change_effective_through as change28_107_0_, snapshotli0_.snapshot_order as snapshot29_107_0_, snapshotli0_.GUID as GUID107_0_, snapshotli0_.changed_by_user as changed31_107_0_, snapshotli0_.changed_at as changed32_107_0_, snapshotli0_.created_at as created33_107_0_, snapshotli0_.created_by_user as created34_107_0_ from stu_schedule snapshotli0_ where snapshotli0_.root_key in (?, ?)
Hibernate: update stu_schedule_root set record_version=?, student_id=?, school_year_id=?, course_id=?, section_id=?, schedule_version_id=?, location_id=?, GUID=?, changed_by_user=?, changed_at=?, created_at=?, created_by_user=? where primary_key=? and record_version=?
Hibernate: update stu_schedule_root set record_version=?, student_id=?, school_year_id=?, course_id=?, section_id=?, schedule_version_id=?, location_id=?, GUID=?, changed_by_user=?, changed_at=?, created_at=?, created_by_user=? where primary_key=? and record_version=?
Hibernate: update stu_schedule set record_version=?, root_key=?, override_credits=?, override_gpa_multiplier=?, override_study_area=?, earned_credits=?, credits=?, gpa_multiplier=?, school_year_id=?, location_id=?, schedule_version_id=?, student_id=?, course_id=?, section_id=?, request_id=?, schedule_status_id=?, start_schedule_period_id=?, end_schedule_period_id=?, study_area_id=?, schedule_locked=?, added_from_id=?, dropped_from_id=?, scheduled_as=?, change_reason_id=?, change_comments=?, change_effective_date=?, change_effective_through=?, snapshot_order=?, GUID=?, changed_by_user=?, changed_at=?, created_at=?, created_by_user=? where primary_key=? and record_version=?
Hibernate: delete from stu_schedule where primary_key=? and record_version=?
Hibernate: delete from stu_schedule_root where primary_key=? and record_version=?
Login
FAQ
Search