You can use meaningful FK.
Alternatively you can pre-cache (either manually in a hash map or via cache groups) Towns, Streets and PostOffices. 22K of records in the cache shouldn't be a big deal. One possible caveat is a relationship back to HouseNumbers that may result in preventing garbage collection of HouseNumbers.
Andrus
On Oct 18, 2010, at 3:47 PM, Borut Bolčina wrote:
> 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 - 15:10:03 UTC