Boston Grails Meetup GORM Presentation

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.


9 Responses to “Boston Grails Meetup GORM Presentation”

  1. [...] This post was Twitted by groovytweets [...]

  2. Mike Hugo says:

    Thanks for the slides and the examples! re: caching – do you typically use ehcache instead of oscache (last time I looked oscache was the default in new grails projects)? Any particular preferences?

  3. Burt says:

    @Mike my understanding is that they changed it to OSCache because Ehcache doesn’t handle dev restarts (e.g. when you edit a domain class or src/groovy file) well but OSCache does. But Ehcache is the better of the two for production.

  4. Roshan Shrestha says:

    In your paper, for User and Role, you have removed the collections. Since you maintain the Sprint security plugin, is this solution carried over to the generated User and Role domain classes?

  5. Burt says:

    @Roshan this is an issue with the Spring Security plugin, but changing it would break backwards compatibility. I’m working on a new script that would use the approach that I proposed here, and some documentation to help people migrate.

  6. Ted Naleid says:

    Great post and presentation Burt. I love seeing what’s going on behind the scenes with the way grails maps belongsTo and hasMany and the performance implications of it.

    I always start with using belongsTo and hasMany, but you provide a number of good optimizations that can be used if there are situations where performance does become unacceptable.

  7. [...] reading through a couple of very helpful posts by Burt Beckwith, and reading through the Hibernate chapter on batch processing, we have all of the [...]

  8. [...] using the standard GORM mapping approach, i.e. using hasMany and belongsTo. As I pointed out here this is a performance concern when you have a large number of users, since granting a new user a [...]

  9. AA says:

    I’m reading that advice in your new book. It’s not necessary delete hasMany – belongsTo relationship, in many cases that is useful (complex hql queries) you only should not to use addTo and to create the children objects like independent objects.

Leave a Reply

Creative Commons License
This work is licensed under a Creative Commons Attribution 3.0 License.