I'm having real performance issue here when using ManyToMany relationships
between two of my entity classes, maybe my mapping is all wrong but I think I tried everything.
These two entities are updateable from both sides (e.g you can add Stations/Lines from a Company
and also add Companies to a Station/Line).
I find it kind of strange that Hibernate loads ALL objects when I do the update when it could just
have done the insert into the join table.
Any help here is appreciated :)
thx,
Kjetil
Specs: Hibernate (annotations & entity-manager) 3.2.0.ga against Oracle 10g, Spring JPAtemplate
Mapping:
Code:
@ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.REMOVE}, fetch = FetchType.LAZY)
@JoinTable(name="OISNCC.SM_CONTACTS_DEFAULT",
joinColumns =
@JoinColumn(name = "STL_IRN_NR", referencedColumnName = "IRN_NR"),
inverseJoinColumns =
@JoinColumn (name = "OCP_IRN_NR", referencedColumnName = "IRN_NR")
)
public List<Company> getCompanies() {
return companies;
}
@ManyToMany(cascade = {CascadeType.PERSIST, CascadeType.REMOVE}, fetch = FetchType.LAZY)
@JoinTable(name = "OISNCC.SM_CONTACTS_DEFAULT",
joinColumns =
@JoinColumn(name = "OCP_IRN_NR", referencedColumnName = "IRN_NR"),
inverseJoinColumns =
@JoinColumn(name = "STL_IRN_NR", referencedColumnName = "IRN_NR")
)
public List<StationLine> getStationLines() {
return stationLines;
}
Example code:Code:
StationLine sl = sService.findById(new Long(480715201));
long start;
start = Calendar.getInstance().getTimeInMillis();
List<Company> companies = sl.getCompanies();
System.out.println("\n\nFound " + companies.size() + " stations for Company " + sl.getIdText() + "(Query took "+(Calendar.getInstance().getTimeInMillis() - start)+" ms)");
for (Iterator<Company> iterator = companies.iterator(); iterator.hasNext();) {
Company c = iterator.next();
System.out.println("\tCompany on station/line: " + c.getIdText());
}
start = Calendar.getInstance().getTimeInMillis();
List<Company> otherCompanies = sService.findCompaniesNotOnStationLine(sl);
System.out.println("\n\nFound " + otherCompanies.size() + " other companies NOT on " + sl.getIdText()+ "(Query took "+(Calendar.getInstance().getTimeInMillis() - start)+" ms)");
/*for (Iterator<Company> iterator = otherCompanies.iterator(); iterator.hasNext();) {
Company c = iterator.next();
System.out.println("\tCompany NOT on station/line: " + c.getIdText());
}*/
Random r = new Random();
int i = Math.abs(r.nextInt(otherCompanies.size() - 1));
System.out.println(i + " -> " + otherCompanies.get(i).getIdText());
companies.add(otherCompanies.get(i));
start = Calendar.getInstance().getTimeInMillis();
sl.setCompanies(companies);
System.out.println("Companies is set.. saving..");
sService.update(sl);
System.out.println("Save took "+(Calendar.getInstance().getTimeInMillis() - start)+" ms)");
Ouput:
Hibernate: select stationlin0_.IRN_NR as IRN1_9_1_, stationlin0_.ID_TEXT as ID2_9_1_, stationlin0_.EXTERNAL_ID as EXTERNAL3_9_1_, stationlin0_.CREATED as CREATED9_1_, stationlin0_.MODIFIED as MODIFIED9_1_, stationlin0_.TERMINATED as TERMINATED9_1_, stationlin0_.CCE_IRN_NR as CCE7_9_1_, stationlin0_.ORG_NR as ORG8_9_1_, stationlin0_.SSU_IRN_NR as SSU9_9_1_, stationlin0_.STATION_FLAG as STATION10_9_1_, stationlin0_.SHIFT_DAY as SHIFT11_9_1_, stationlin0_.SHIFT_TIME as SHIFT12_9_1_, stationlin0_.SCHEDULE_DATE as SCHEDULE13_9_1_, stationlin0_.ZONE_IRN as ZONE14_9_1_, stationlin0_.FICTITIOUS_OBJECT_TYPE as FICTITIOUS15_9_1_, stationlin0_.PRESENTATION as PRESENT16_9_1_, stationlin0_.PPCS_STATION_GROUPS_IRN as PPCS17_9_1_, controlcen1_.IRN_NR as IRN1_6_0_, controlcen1_.ID_TEXT as ID2_6_0_, controlcen1_.EXTERNAL_ID as EXTERNAL3_6_0_, controlcen1_.CREATED as CREATED6_0_, controlcen1_.MODIFIED as MODIFIED6_0_, controlcen1_.TERMINATED as TERMINATED6_0_, controlcen1_.PHONE_NR as PHONE7_6_0_, controlcen1_.FAX_NR as FAX8_6_0_, controlcen1_.EMAIL as EMAIL6_0_, controlcen1_.ID_TEXT_NOR as ID10_6_0_ from OISNCC.STATION_LINES stationlin0_ inner join OISNCC.CONTROL_CENTRES controlcen1_ on stationlin0_.CCE_IRN_NR=controlcen1_.IRN_NR where stationlin0_.IRN_NR=?
Hibernate: select companies0_.STL_IRN_NR as STL4_2_, companies0_.OCP_IRN_NR as OCP3_2_, company1_.IRN_NR as IRN1_3_0_, company1_.ID_TEXT as ID2_3_0_, company1_.EXTERNAL_ID as EXTERNAL3_3_0_, company1_.CREATED as CREATED3_0_, company1_.MODIFIED as MODIFIED3_0_, company1_.TERMINATED as TERMINATED3_0_, company1_.CCE_IRN_NR as CCE7_3_0_, controlcen2_.IRN_NR as IRN1_6_1_, controlcen2_.ID_TEXT as ID2_6_1_, controlcen2_.EXTERNAL_ID as EXTERNAL3_6_1_, controlcen2_.CREATED as CREATED6_1_, controlcen2_.MODIFIED as MODIFIED6_1_, controlcen2_.TERMINATED as TERMINATED6_1_, controlcen2_.PHONE_NR as PHONE7_6_1_, controlcen2_.FAX_NR as FAX8_6_1_, controlcen2_.EMAIL as EMAIL6_1_, controlcen2_.ID_TEXT_NOR as ID10_6_1_ from OISNCC.SM_CONTACTS_DEFAULT companies0_ left outer join OISNCC.OIS_COMPANIES company1_ on companies0_.OCP_IRN_NR=company1_.IRN_NR inner join OISNCC.CONTROL_CENTRES controlcen2_ on company1_.CCE_IRN_NR=controlcen2_.IRN_NR where companies0_.STL_IRN_NR=?
Found 3 stations for Company TestCompany (Query took 40 ms)
Company on station/line: Test station 1
Company on station/line: Test station 2
Company on station/line: Test station 3
Hibernate: select company0_.IRN_NR as IRN1_3_, company0_.ID_TEXT as ID2_3_, company0_.EXTERNAL_ID as EXTERNAL3_3_, company0_.CREATED as CREATED3_, company0_.MODIFIED as MODIFIED3_, company0_.TERMINATED as TERMINATED3_, company0_.CCE_IRN_NR as CCE7_3_ from OISNCC.OIS_COMPANIES company0_ where company0_.IRN_NR not in (922770201 , 521741201 , 429478201 , 922779201 , 574789201 , 429553201 , 706453201 , 1043627201 , 574771201)
Found 243 other companies NOT on TestCompany (Query took 461 ms)
138 -> Test station 99
Companies is set.. saving..
Hibernate: select unitroles0_.STL_IRN_NR as STL7_2_, unitroles0_.ID as ID2_, unitroles0_.ID as ID11_1_, unitroles0_.CCE_IRN_NR as CCE2_11_1_, unitroles0_.STL_IRN_NR as STL7_11_1_, unitroles0_.MUN_MUID as MUN8_11_1_, unitroles0_.INACTIVE as INACTIVE11_1_, unitroles0_.URT_CODE as URT9_11_1_, unitroles0_.TRAVEL_TIME as TRAVEL4_11_1_, unitroles0_.ROLE_SWL as ROLE5_11_1_, unitroles0_.ROLE_SECL as ROLE6_11_1_, controlcen1_.IRN_NR as IRN1_6_0_, controlcen1_.ID_TEXT as ID2_6_0_, controlcen1_.EXTERNAL_ID as EXTERNAL3_6_0_, controlcen1_.CREATED as CREATED6_0_, controlcen1_.MODIFIED as MODIFIED6_0_, controlcen1_.TERMINATED as TERMINATED6_0_, controlcen1_.PHONE_NR as PHONE7_6_0_, controlcen1_.FAX_NR as FAX8_6_0_, controlcen1_.EMAIL as EMAIL6_0_, controlcen1_.ID_TEXT_NOR as ID10_6_0_ from OISNCC.UNIT_ROLES unitroles0_ inner join OISNCC.CONTROL_CENTRES controlcen1_ on unitroles0_.CCE_IRN_NR=controlcen1_.IRN_NR where unitroles0_.STL_IRN_NR=?
Hibernate: select stationlin0_.OCP_IRN_NR as OCP3_2_, stationlin0_.STL_IRN_NR as STL4_2_, stationlin1_.IRN_NR as IRN1_9_0_, stationlin1_.ID_TEXT as ID2_9_0_, stationlin1_.EXTERNAL_ID as EXTERNAL3_9_0_, stationlin1_.CREATED as CREATED9_0_, stationlin1_.MODIFIED as MODIFIED9_0_, stationlin1_.TERMINATED as TERMINATED9_0_, stationlin1_.CCE_IRN_NR as CCE7_9_0_, stationlin1_.ORG_NR as ORG8_9_0_, stationlin1_.SSU_IRN_NR as SSU9_9_0_, stationlin1_.STATION_FLAG as STATION10_9_0_, stationlin1_.SHIFT_DAY as SHIFT11_9_0_, stationlin1_.SHIFT_TIME as SHIFT12_9_0_, stationlin1_.SCHEDULE_DATE as SCHEDULE13_9_0_, stationlin1_.ZONE_IRN as ZONE14_9_0_, stationlin1_.FICTITIOUS_OBJECT_TYPE as FICTITIOUS15_9_0_, stationlin1_.PRESENTATION as PRESENT16_9_0_, stationlin1_.PPCS_STATION_GROUPS_IRN as PPCS17_9_0_, controlcen2_.IRN_NR as IRN1_6_1_, controlcen2_.ID_TEXT as ID2_6_1_, controlcen2_.EXTERNAL_ID as EXTERNAL3_6_1_, controlcen2_.CREATED as CREATED6_1_, controlcen2_.MODIFIED as MODIFIED6_1_, controlcen2_.TERMINATED as TERMINATED6_1_, controlcen2_.PHONE_NR as PHONE7_6_1_, controlcen2_.FAX_NR as FAX8_6_1_, controlcen2_.EMAIL as EMAIL6_1_, controlcen2_.ID_TEXT_NOR as ID10_6_1_ from OISNCC.SM_CONTACTS_DEFAULT stationlin0_ left outer join OISNCC.STATION_LINES stationlin1_ on stationlin0_.STL_IRN_NR=stationlin1_.IRN_NR inner join OISNCC.CONTROL_CENTRES controlcen2_ on stationlin1_.CCE_IRN_NR=controlcen2_.IRN_NR where stationlin0_.OCP_IRN_NR=?
Hibernate: select unitroles0_.STL_IRN_NR as STL7_2_, unitroles0_.ID as ID2_, unitroles0_.ID as ID11_1_, unitroles0_.CCE_IRN_NR as CCE2_11_1_, unitroles0_.STL_IRN_NR as STL7_11_1_, unitroles0_.MUN_MUID as MUN8_11_1_, unitroles0_.INACTIVE as INACTIVE11_1_, unitroles0_.URT_CODE as URT9_11_1_, unitroles0_.TRAVEL_TIME as TRAVEL4_11_1_, unitroles0_.ROLE_SWL as ROLE5_11_1_, unitroles0_.ROLE_SECL as ROLE6_11_1_, controlcen1_.IRN_NR as IRN1_6_0_, controlcen1_.ID_TEXT as ID2_6_0_, controlcen1_.EXTERNAL_ID as EXTERNAL3_6_0_, controlcen1_.CREATED as CREATED6_0_, controlcen1_.MODIFIED as MODIFIED6_0_, controlcen1_.TERMINATED as TERMINATED6_0_, controlcen1_.PHONE_NR as PHONE7_6_0_, controlcen1_.FAX_NR as FAX8_6_0_, controlcen1_.EMAIL as EMAIL6_0_, controlcen1_.ID_TEXT_NOR as ID10_6_0_ from OISNCC.UNIT_ROLES unitroles0_ inner join OISNCC.CONTROL_CENTRES controlcen1_ on unitroles0_.CCE_IRN_NR=controlcen1_.IRN_NR where unitroles0_.STL_IRN_NR=?
.
..
<REMOVED 3000 lines of the same select statements>
...
Hibernate: select unitroles0_.STL_IRN_NR as STL7_2_, unitroles0_.ID as ID2_, unitroles0_.ID as ID11_1_, unitroles0_.CCE_IRN_NR as CCE2_11_1_, unitroles0_.STL_IRN_NR as STL7_11_1_, unitroles0_.MUN_MUID as MUN8_11_1_, unitroles0_.INACTIVE as INACTIVE11_1_, unitroles0_.URT_CODE as URT9_11_1_, unitroles0_.TRAVEL_TIME as TRAVEL4_11_1_, unitroles0_.ROLE_SWL as ROLE5_11_1_, unitroles0_.ROLE_SECL as ROLE6_11_1_, controlcen1_.IRN_NR as IRN1_6_0_, controlcen1_.ID_TEXT as ID2_6_0_, controlcen1_.EXTERNAL_ID as EXTERNAL3_6_0_, controlcen1_.CREATED as CREATED6_0_, controlcen1_.MODIFIED as MODIFIED6_0_, controlcen1_.TERMINATED as TERMINATED6_0_, controlcen1_.PHONE_NR as PHONE7_6_0_, controlcen1_.FAX_NR as FAX8_6_0_, controlcen1_.EMAIL as EMAIL6_0_, controlcen1_.ID_TEXT_NOR as ID10_6_0_ from OISNCC.UNIT_ROLES unitroles0_ inner join OISNCC.CONTROL_CENTRES controlcen1_ on unitroles0_.CCE_IRN_NR=controlcen1_.IRN_NR where unitroles0_.STL_IRN_NR=?
Hibernate: select companies0_.STL_IRN_NR as STL4_2_, companies0_.OCP_IRN_NR as OCP3_2_, company1_.IRN_NR as IRN1_3_0_, company1_.ID_TEXT as ID2_3_0_, company1_.EXTERNAL_ID as EXTERNAL3_3_0_, company1_.CREATED as CREATED3_0_, company1_.MODIFIED as MODIFIED3_0_, company1_.TERMINATED as TERMINATED3_0_, company1_.CCE_IRN_NR as CCE7_3_0_, controlcen2_.IRN_NR as IRN1_6_1_, controlcen2_.ID_TEXT as ID2_6_1_, controlcen2_.EXTERNAL_ID as EXTERNAL3_6_1_, controlcen2_.CREATED as CREATED6_1_, controlcen2_.MODIFIED as MODIFIED6_1_, controlcen2_.TERMINATED as TERMINATED6_1_, controlcen2_.PHONE_NR as PHONE7_6_1_, controlcen2_.FAX_NR as FAX8_6_1_, controlcen2_.EMAIL as EMAIL6_1_, controlcen2_.ID_TEXT_NOR as ID10_6_1_ from OISNCC.SM_CONTACTS_DEFAULT companies0_ left outer join OISNCC.OIS_COMPANIES company1_ on companies0_.OCP_IRN_NR=company1_.IRN_NR inner join OISNCC.CONTROL_CENTRES controlcen2_ on company1_.CCE_IRN_NR=controlcen2_.IRN_NR where companies0_.STL_IRN_NR=?
Hibernate: select stationlin0_.OCP_IRN_NR as OCP3_2_, stationlin0_.STL_IRN_NR as STL4_2_, stationlin1_.IRN_NR as IRN1_9_0_, stationlin1_.ID_TEXT as ID2_9_0_, stationlin1_.EXTERNAL_ID as EXTERNAL3_9_0_, stationlin1_.CREATED as CREATED9_0_, stationlin1_.MODIFIED as MODIFIED9_0_, stationlin1_.TERMINATED as TERMINATED9_0_, stationlin1_.CCE_IRN_NR as CCE7_9_0_, stationlin1_.ORG_NR as ORG8_9_0_, stationlin1_.SSU_IRN_NR as SSU9_9_0_, stationlin1_.STATION_FLAG as STATION10_9_0_, stationlin1_.SHIFT_DAY as SHIFT11_9_0_, stationlin1_.SHIFT_TIME as SHIFT12_9_0_, stationlin1_.SCHEDULE_DATE as SCHEDULE13_9_0_, stationlin1_.ZONE_IRN as ZONE14_9_0_, stationlin1_.FICTITIOUS_OBJECT_TYPE as FICTITIOUS15_9_0_, stationlin1_.PRESENTATION as PRESENT16_9_0_, stationlin1_.PPCS_STATION_GROUPS_IRN as PPCS17_9_0_, controlcen2_.IRN_NR as IRN1_6_1_, controlcen2_.ID_TEXT as ID2_6_1_, controlcen2_.EXTERNAL_ID as EXTERNAL3_6_1_, controlcen2_.CREATED as CREATED6_1_, controlcen2_.MODIFIED as MODIFIED6_1_, controlcen2_.TERMINATED as TERMINATED6_1_, controlcen2_.PHONE_NR as PHONE7_6_1_, controlcen2_.FAX_NR as FAX8_6_1_, controlcen2_.EMAIL as EMAIL6_1_, controlcen2_.ID_TEXT_NOR as ID10_6_1_ from OISNCC.SM_CONTACTS_DEFAULT stationlin0_ left outer join OISNCC.STATION_LINES stationlin1_ on stationlin0_.STL_IRN_NR=stationlin1_.IRN_NR inner join OISNCC.CONTROL_CENTRES controlcen2_ on stationlin1_.CCE_IRN_NR=controlcen2_.IRN_NR where stationlin0_.OCP_IRN_NR=?
Save took 23524 ms
(then the test waits for a couple of seconds and does..)
Hibernate: delete from OISNCC.SM_CONTACTS_DEFAULT where STL_IRN_NR=?
Hibernate: insert into OISNCC.SM_CONTACTS_DEFAULT (STL_IRN_NR, OCP_IRN_NR) values (?, ?)
Hibernate: insert into OISNCC.SM_CONTACTS_DEFAULT (STL_IRN_NR, OCP_IRN_NR) values (?, ?)
Hibernate: insert into OISNCC.SM_CONTACTS_DEFAULT (STL_IRN_NR, OCP_IRN_NR) values (?, ?)
Hibernate: insert into OISNCC.SM_CONTACTS_DEFAULT (STL_IRN_NR, OCP_IRN_NR) values (?, ?)
Hibernate: insert into OISNCC.SM_CONTACTS_DEFAULT (STL_IRN_NR, OCP_IRN_NR) values (?, ?)
Hibernate: insert into OISNCC.SM_CONTACTS_DEFAULT (STL_IRN_NR, OCP_IRN_NR) values (?, ?)
Hibernate: insert into OISNCC.SM_CONTACTS_DEFAULT (STL_IRN_NR, OCP_IRN_NR) values (?, ?)
Hibernate: insert into OISNCC.SM_CONTACTS_DEFAULT (STL_IRN_NR, OCP_IRN_NR) values (?, ?)
Hibernate: insert into OISNCC.SM_CONTACTS_DEFAULT (STL_IRN_NR, OCP_IRN_NR) values (?, ?)
Hibernate: insert into OISNCC.SM_CONTACTS_DEFAULT (STL_IRN_NR, OCP_IRN_NR) values (?, ?)
Login
FAQ
Search