cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Borut BolĨina <borut.bolc...@gmail.com>
Subject Writable FKs
Date Mon, 18 Oct 2010 12:47:23 GMT
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

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message