Improving Grails CreateCriteria query speed with joins

Question

I have a Grails application that does a rather huge createCriteria query pulling from many tables. I noticed that the performance is pretty terrible and have pinpointed it to the Object manipulation I do afterwards, rather than the createCriteria itself. My query successfully gets all of the original objects I wanted, but it is performing a new query for each element when I am manipulating the objects. Here is a simplified version of my controller code:

def hosts = Host.createCriteria().list(max: maxRows, offset: rowOffset) {
    // Lots of if statements for filters, etc.
}

def results = hosts?.collect{ [ cell: [
    it.hostname,
    it.type,
    it.status.toString(),
    it.env.toString(),
    it.supporter.person.toString()
    ...
 ]]}

I have many more fields, including calls to methods that perform their own queries to find related objects. So my question is: How can I incorporate joins into the original query so that I am not performing tons of extra queries for each individual row? Currently querying for ~700 rows takes 2 minutes, which is way too long. Any advice would be great! Thanks!

score 3 · Accepted Answer · answered Nov 05 '13 at 03:07

One benefit you get using criteria is you can easily fetch associations eagerly. As a result of which you would not face the well known N+1 problem while referring associations.

You have not mentioned the logic in criteria but I would suggest for ~700 rows I would definitely go for something like this:

def hosts = Host.createCriteria().list(max: maxRows, offset: rowOffset) {
    ...
    //associations are eagerly fetched if a DSL like below 
    //is used in Criteria query
    supporter{
        person{

        }
    }

    someOtherAssoc{
        //Involve logic if required
        //eq('someOtherProperty', someOtherValue)
    }
}

If you feel that tailoring a Criteria is cumbersome, then you can very well fallback to HQL and use join fetch for eager indexing for associations.

I hope this would definitely reduce the turnaround time to less than 5 sec for ~700 records.

I notice this method does not work with nullable fields. For example if the "supporter" field is empty, then that row will not be returned by the query at all. Is there a way to overcome this? — Nisrak, Nov 05 '13 at 21:31
Yes. Associations are be default eagerly fetched using `inner joins` hence no nullable fields. You can force it to use LEFT JOIN in order to get nullable fields. Follow [this other answer](http://stackoverflow.com/a/19391255/2051952), you will get the syntax. @Nisrak — dmahapatro, Nov 05 '13 at 21:39

Improving Grails CreateCriteria query speed with joins

1 Answers1