CODEWITHSUNDEEP
mysqlsqlperformancehibernategrails
Nisrak
Nisrak

Reputation: 335

Improving Grails CreateCriteria query speed with joins

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!

Upvotes: 1

Views: 722

Answers (1)

dmahapatro
dmahapatro

Reputation: 50265

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.

Upvotes: 3

Related Questions