Petrogad Petrogad - 1 year ago 96
Groovy Question

Groovy and Hibernate - Performant Queries in one to Many relationships

I have inherited a rather complex groovy application and am trying to understand how I can better solve some of the nasty query patterns that presently exist.

Simply, I have something like:

Class A {
User user
Bravo bravo
static hasMany = [foo: Foo, bar: Long]

Class Foo {
User user
Delta delta
static belongsTo = [a: A]
static hasMany = [woo: Woo]

Class Woo {
User user
Alpha alpha
static belongsTo = [foo: Foo]

I'm attempting to do
which is returning me a
, however when I start itering over that list I'm hitting hundreds of queries that are further building out

I attempted to add
Foo fetch:"join"
to the Foo class and
Woo fetch: "join"
to the Woo class, however this means that EVERY query that touches these two will always make those queries (and still doesn't solve the hundreds of queries hitting

I'm a rather novice when it comes to using HQL, however this is causing some serious issues with performance and large queries at scale. Is there a better way to tackle this problem (and hopefully avoid hundreds of select queries)?

Answer Source

It looks like you're using GORM (which is the ORM used by the Grails framework).

To solve your problem, you need to decide what your most common use case is. If you want to keep fetching lazily by default, you can locally change the fetch strategy when you need to: A.findAllByUser(user, [fetch: join]).

If on the other hand you want to make eager fetching the default but want to disable it locally, I believe you should use lazy: false instead of fetch: join in your domain class because it is safer with one-to-many associations. And you should be able to disable it for any given query (e.g. A.findAllByUser(user, [lazy: true]).

See here.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download