Hello,
I would like to discuss one design issue. I am inserting a large number of
objects (500.000) and for each of them I must set 3 relationships. These
target relationship tables consists of 6000, 15000 and 450 records.
Approaching this solution naively like this:
loop of 500.000 iterations {
Town town = persistenceService.findTownByMid(townMid); //
table town has 6000 records
Street street =
persistenceService.findStreetByMid(streetMid); // has 15000 records
PostOffice postOffice =
persistenceService.findPostOfficeByMid(postOfficeMid); // has 450 records
HouseNumber houseNumber =
dataContext.newObject(HouseNumber.class); // 500.000 to be inserted
houseNumber.setMid(mid);
houseNumber.setNumber(number);
houseNumber.setLetter(letter);
houseNumber.setTown(town);
houseNumber.setStreet(street);
houseNumber.setPostOffice(postOffice);
dataContext.commitChanges();
} // end loop
is not only very slow (1 hour for inserting 150.000 records), but memory
consuming (250 MBytes for 150.000 records).
For each of 500.000 house numbers I must do 3 database selects to fetch the
right object for the relationship. Obviously this is a flawed approach.
In the past I used iteration through data rows to read data fast in a memory
efficient way, but there were no relationships involved. So I was thinking
about setting the 3 foreign keys for the house number by an added FK setter
method in my HouseNumber class and having three custom prepopulated maps for
each relationship containing [mid,id] pairs as a "lookup table".
Any other idea?
Regards,
bob
This archive was generated by hypermail 2.0.0 : Mon Oct 18 2010 - 12:47:56 UTC