Considering meaningful FK,
My current working setup of the towns and street table looks like (mid is
unique for both tables and candidate for meaningful FK):
CREATE TABLE IF NOT EXISTS `maps`.`town` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`mid` INT UNSIGNED NOT NULL ,
`name` VARCHAR(60) NOT NULL ,
PRIMARY KEY (`id`) ,
UNIQUE INDEX `mid_UNIQUE` (`mid` ASC) )
ENGINE = InnoDB
CREATE TABLE IF NOT EXISTS `maps`.`street` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`mid` INT UNSIGNED NOT NULL ,
`name` VARCHAR(50) NULL ,
`town_id` INT UNSIGNED NOT NULL ,
INDEX `town_id` (`town_id` ASC) ,
PRIMARY KEY (`id`) ,
UNIQUE INDEX `mid_UNIQUE` (`mid` ASC) ,
CONSTRAINT `town_id`
FOREIGN KEY (`town_id` )
REFERENCES `maps`.`town` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
And current relationship from street to town
<db-relationship name="town" source="street" target="town"
toMany="false">
<db-attribute-pair source="town_id" target="id"/>
</db-relationship>
<obj-relationship name="town" source="Street" target="Town"
deleteRule="Nullify" db-relationship-path="town"/>
and the reverse
<db-relationship name="streets" source="town" target="street"
toMany="true">
<db-attribute-pair source="id" target="town_id"/>
</db-relationship>
<obj-relationship name="streets" source="Town" target="Street"
deleteRule="Deny" db-relationship-path="streets"/>
Are you proposing to use target="mid" (instead of id) in the
<db-relationship name="town" source="street" target="town"
toMany="false">
<db-attribute-pair source="town_id" target="mid"/>
</db-relationship>
This way using
street.setTown(localTown);
does not set the relationship, it inserts NULL for town_id FK in the streets
table. Or did you mean something else?
-Borut
2010/10/18 Andrus Adamchik <andru..bjectstyle.org>
> 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 : Tue Oct 19 2010 - 07:37:00 UTC