I gave a talk last week
at the Boston Grails meetup and wanted to share the slides. There were two code demonstrations in the Grails console so I’ll describe those here.
The general gist of the presentation was that mapped collections in Hibernate (and therefore Grails, since GORM wraps Hibernate) are a potentially risky choice for representing One-to-Many and Many-to-Many relationships.
To see what’s going on I added
loggingSql = true
to DataSource.groovy so we’d see the generated SQL in the console when comparing the standard and modified versions of the code.
First we create a Library and associate 20 (a completely arbitrary number) Visits:
def library = new Library(name: 'the library')
20.times {
library.addToVisits(new Visit(personName: "person$it"))
library.save(flush: true)
}
Clear the output window and add one more Visit:
def library = Library.list()[0]
library.addToVisits(new Visit(personName: "new person"))
library.save(flush: true)
and you’ll see SQL similar to
select this_.id as id3_0_, this_.version as version3_0_, this_.name as name3_0_
from library this_;
select visits0_.library_id as library3_1_, visits0_.id as id1_, visits0_.id as id2_0_,
visits0_.version as version2_0_, visits0_.library_id as library3_2_0_,
visits0_.person_name as person4_2_0_, visits0_.visit_date as visit5_2_0_
from visit visits0_ where visits0_.library_id=?;
insert into visit (id, version, library_id, person_name, visit_date)
values (null, ?, ?, ?, ?);
call identity();
update library
set version=?, name=?
where id=? and version=?;
which means that it’s first loading the Library, then loading all of its previous Visits (!), then creating the new Visit, and finally bumping the version of the Library since it’s “changed”. Not good – not only do we incur a potentially huge performance hit to add one new instance (once there are a large number already), we also run a high risk of a false optimistic locking failure.
Doing the equivalent work with the updated versions:
def library = new Library(name: 'the library').save()
20.times {
new Visit(personName: "person$it", library: library).save(flush: true)
}
and
def library = Library.list()[0]
new Visit(personName: "new person", library: library).save(flush: true)
we get SQL that makes a lot more sense:
select this_.id as id2_0_, this_.version as version2_0_, this_.name as name2_0_
from library this_;
insert into visit (id, version, library_id, person_name, visit_date)
values (null, ?, ?, ?, ?);
call identity();
First the Library is loaded, and then the Visit is created. No other Visit instances need to be loaded since we know the new instance is unique, and there’s no need to version the Library.
For the Many-to-Many example, we create a Role and grant it to 20 Users:
Role role = new Role(name: 'ROLE_USER').save()
20.times {
def user = new User(username: "user$it").save()
user.addToRoles(role)
user.save(flush: true)
}
and clear the console and grant the Role to one more User:
def role = Role.list()[0]
def user = new User(username: "new person").save()
user.addToRoles(role)
user.save(flush: true)
and you’ll see SQL similar to
select this_.id as id0_0_, this_.version as version0_0_, this_.name as name0_0_
from role this_;
insert into user (id, version, username)
values (null, ?, ?);
call identity();
select users0_.role_id as role1_0_, users0_.user_id as user2_0_
from user_roles users0_
where users0_.role_id=?;
update role
set version=?, name=?
where id=? and version=?;
update user
set version=?, username=?
where id=? and version=?;
insert into user_roles (user_id, role_id)
values (?, ?);
So we can see that it first loads the Role, then creates the User, then loads all of the previous Users that have that Role (proxies in this case, but still a lot of instances for no good reason), then it bumps the version for both the Role and the User, and finally adds a row to the join table.
Doing the same work with the updated versions:
Role role = new Role(name: 'ROLE_USER').save()
20.times {
def user = new User(username: "user$it").save()
UserRole.create(user, role, true)
}
and
def role = Role.list()[0]
def user = new User(username: "new user").save()
UserRole.create(user, role, true)
we get more reasonable SQL:
select this_.id as id4_0_, this_.version as version4_0_, this_.name as name4_0_
from role this_;
insert into user(id, version, username)
values (null, ?, ?);
call identity();
select userrole_.user_id, userrole_.role_id
from user_role userrole_
where userrole_.user_id=? and userrole_.role_id=?;
insert into user_role(user_id, role_id)
values (?, ?);
where we can see that it loads the Role, creates the User, does a lightweight unique check in the join table, and then inserts the join table row.
Download the presentation
here and the sample projects at
here and
here.
I gave a talk last week at the Boston Grails meetup and wanted to share the slides. There were two code demonstrations in the Grails console so I’ll describe those...
