Quantcast

fecth:join does NOT work in Grails 2.0.1 - N+1 problem

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

fecth:join does NOT work in Grails 2.0.1 - N+1 problem

jl_monteagudo
Sorry for this post, becase I post something similar some weeks ago.

The problems is that if I configure a domain class to get its associations eagerly, it only generates an INNER JOIN QUERY the first time that I excecute the query. The following times it does NOT generate a JOIN QUERY and access to the database N+1 times.

Here is the sample:

class Location {
        String city
}


class Author {
    String name
    Location location
 
    static hasMany = [ books: Book ]
       
        static mapping = {
                location fetch: 'join'
        }
}


Then I run the following script in the console:

if (Author.count() == 0) {

    def location1 = new Location(city:"location1").save(flush:true)
    def location2 = new Location(city:"location2").save(flush:true)
    def location3 = new Location(city:"location3").save(flush:true)
   
    new Author(name:"name1", location:location1).save(flush:true)
    new Author(name:"name2", location:location1).save(flush:true)
    new Author(name:"name3", location:location1).save(flush:true)
    new Author(name:"name4", location:location2).save(flush:true)
    new Author(name:"name5", location:location3).save(flush:true)

}

Author.list().each { author ->
    println author.location.city
}


The first time that I run the script I get the following SQL log:

Hibernate: select count(*) as y0_ from author this_
Hibernate: insert into location (id, version, city) values (null, ?, ?)
Hibernate: insert into location (id, version, city) values (null, ?, ?)
Hibernate: insert into location (id, version, city) values (null, ?, ?)
Hibernate: insert into author (id, version, location_id, name) values (null, ?, ?, ?)
Hibernate: insert into author (id, version, location_id, name) values (null, ?, ?, ?)
Hibernate: insert into author (id, version, location_id, name) values (null, ?, ?, ?)
Hibernate: insert into author (id, version, location_id, name) values (null, ?, ?, ?)
Hibernate: insert into author (id, version, location_id, name) values (null, ?, ?, ?)

Hibernate: select this_.id as id1_1_, this_.version as version1_1_, this_.location_id as location3_1_1_, this_.name as name1_1_, location2_.id as id0_0_, location2_.version as version0_0_, location2_.city as city0_0_ from author this_ inner join location location2_ on this_.location_id=location2_.id

location1
location1
location1
location2
location3


The second time that I run the script I get:

Hibernate: select count(*) as y0_ from author this_

Hibernate: select this_.id as id1_1_, this_.version as version1_1_, this_.location_id as location3_1_1_, this_.name as name1_1_, location2_.id as id0_0_, location2_.version as version0_0_, location2_.city as city0_0_ from author this_ inner join location location2_ on this_.location_id=location2_.id

location1
location1
location1
location2
location3

And the following times I always get:

Hibernate: select count(*) as y0_ from author this_

Hibernate: select author0_.id as id1_1_, author0_.version as version1_1_, author0_.location_id as location3_1_1_, author0_.name as name1_1_, location1_.id as id0_0_, location1_.version as version0_0_, location1_.city as city0_0_ from author author0_ inner join location location1_ on author0_.location_id=location1_.id where author0_.id=?

Hibernate: select author0_.id as id1_1_, author0_.version as version1_1_, author0_.location_id as location3_1_1_, author0_.name as name1_1_, location1_.id as id0_0_, location1_.version as version0_0_, location1_.city as city0_0_ from author author0_ inner join location location1_ on author0_.location_id=location1_.id where author0_.id=?

Hibernate: select author0_.id as id1_1_, author0_.version as version1_1_, author0_.location_id as location3_1_1_, author0_.name as name1_1_, location1_.id as id0_0_, location1_.version as version0_0_, location1_.city as city0_0_ from author author0_ inner join location location1_ on author0_.location_id=location1_.id where author0_.id=?

Hibernate: select author0_.id as id1_1_, author0_.version as version1_1_, author0_.location_id as location3_1_1_, author0_.name as name1_1_, location1_.id as id0_0_, location1_.version as version0_0_, location1_.city as city0_0_ from author author0_ inner join location location1_ on author0_.location_id=location1_.id where author0_.id=?

Hibernate: select author0_.id as id1_1_, author0_.version as version1_1_, author0_.location_id as location3_1_1_, author0_.name as name1_1_, location1_.id as id0_0_, location1_.version as version0_0_, location1_.city as city0_0_ from author author0_ inner join location location1_ on author0_.location_id=location1_.id where author0_.id=?

location1
location1
location1
location2
location3


I don't understand why the second time that I run the script Hibernate accesses only one time to the database, and why the third and next times that I run the script Hiberante accesses 5 times to the database.

The workaround for this issue is working with HQL, but it is much less desirable.

Can somebody help me with this issue?

Thank you very much in advance !!
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: fecth:join does NOT work in Grails 2.0.1 - N+1 problem

pledbrook
> I don't understand why the second time that I run the script Hibernate
> accesses only one time to the database, and why the third and next times
> that I run the script Hiberante accesses 5 times to the database.
>
> The workaround for this issue is working with HQL, but it is much less
> desirable.
>
> Can somebody help me with this issue?

This is likely to be one of the issue reported in
http://jira.grails.org/browse/GRAILS-8888 - does that sound right to
you?

Peter

--
Peter Ledbrook
Grails Advocate
SpringSource - A Division of VMware

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: fecth:join does NOT work in Grails 2.0.1 - N+1 problem

Graeme Rocher-4
Administrator
In reply to this post by jl_monteagudo
As Peter pointed it, it is a known issue. I am working on this issue
right now in fact

Cheers

On Wed, Mar 21, 2012 at 3:46 PM, jl_monteagudo <[hidden email]> wrote:

> Sorry for this post, becase I post something similar some weeks ago.
>
> The problems is that if I configure a domain class to get its associations
> eagerly, it only generates an INNER JOIN QUERY the first time that I
> excecute the query. The following times it *does NOT generate a JOIN QUERY*
> and access to the database *N+1 times*.
>
> Here is the sample:
>
> class Location {
>        String city
> }
>
>
> class Author {
>    String name
>    Location location
>
>    static hasMany = [ books: Book ]
>
>        static mapping = {
>                location fetch: 'join'
>        }
> }
>
>
> Then I run the following script in the console:
>
> if (Author.count() == 0) {
>
>    def location1 = new Location(city:"location1").save(flush:true)
>    def location2 = new Location(city:"location2").save(flush:true)
>    def location3 = new Location(city:"location3").save(flush:true)
>
>    new Author(name:"name1", location:location1).save(flush:true)
>    new Author(name:"name2", location:location1).save(flush:true)
>    new Author(name:"name3", location:location1).save(flush:true)
>    new Author(name:"name4", location:location2).save(flush:true)
>    new Author(name:"name5", location:location3).save(flush:true)
>
> }
>
> Author.list().each { author ->
>    println author.location.city
> }
>
>
> The first time that I run the script I get the following SQL log:
>
> Hibernate: select count(*) as y0_ from author this_
> Hibernate: insert into location (id, version, city) values (null, ?, ?)
> Hibernate: insert into location (id, version, city) values (null, ?, ?)
> Hibernate: insert into location (id, version, city) values (null, ?, ?)
> Hibernate: insert into author (id, version, location_id, name) values (null,
> ?, ?, ?)
> Hibernate: insert into author (id, version, location_id, name) values (null,
> ?, ?, ?)
> Hibernate: insert into author (id, version, location_id, name) values (null,
> ?, ?, ?)
> Hibernate: insert into author (id, version, location_id, name) values (null,
> ?, ?, ?)
> Hibernate: insert into author (id, version, location_id, name) values (null,
> ?, ?, ?)
>
> Hibernate: select this_.id as id1_1_, this_.version as version1_1_,
> this_.location_id as location3_1_1_, this_.name as name1_1_, location2_.id
> as id0_0_, location2_.version as version0_0_, location2_.city as city0_0_
> from author this_ inner join location location2_ on
> this_.location_id=location2_.id
>
> location1
> location1
> location1
> location2
> location3
>
>
> The second time that I run the script I get:
>
> Hibernate: select count(*) as y0_ from author this_
>
> Hibernate: select this_.id as id1_1_, this_.version as version1_1_,
> this_.location_id as location3_1_1_, this_.name as name1_1_, location2_.id
> as id0_0_, location2_.version as version0_0_, location2_.city as city0_0_
> from author this_ inner join location location2_ on
> this_.location_id=location2_.id
>
> location1
> location1
> location1
> location2
> location3
>
> And the following times I always get:
>
> Hibernate: select count(*) as y0_ from author this_
>
> Hibernate: select author0_.id as id1_1_, author0_.version as version1_1_,
> author0_.location_id as location3_1_1_, author0_.name as name1_1_,
> location1_.id as id0_0_, location1_.version as version0_0_, location1_.city
> as city0_0_ from author author0_ inner join location location1_ on
> author0_.location_id=location1_.id where author0_.id=?
>
> Hibernate: select author0_.id as id1_1_, author0_.version as version1_1_,
> author0_.location_id as location3_1_1_, author0_.name as name1_1_,
> location1_.id as id0_0_, location1_.version as version0_0_, location1_.city
> as city0_0_ from author author0_ inner join location location1_ on
> author0_.location_id=location1_.id where author0_.id=?
>
> Hibernate: select author0_.id as id1_1_, author0_.version as version1_1_,
> author0_.location_id as location3_1_1_, author0_.name as name1_1_,
> location1_.id as id0_0_, location1_.version as version0_0_, location1_.city
> as city0_0_ from author author0_ inner join location location1_ on
> author0_.location_id=location1_.id where author0_.id=?
>
> Hibernate: select author0_.id as id1_1_, author0_.version as version1_1_,
> author0_.location_id as location3_1_1_, author0_.name as name1_1_,
> location1_.id as id0_0_, location1_.version as version0_0_, location1_.city
> as city0_0_ from author author0_ inner join location location1_ on
> author0_.location_id=location1_.id where author0_.id=?
>
> Hibernate: select author0_.id as id1_1_, author0_.version as version1_1_,
> author0_.location_id as location3_1_1_, author0_.name as name1_1_,
> location1_.id as id0_0_, location1_.version as version0_0_, location1_.city
> as city0_0_ from author author0_ inner join location location1_ on
> author0_.location_id=location1_.id where author0_.id=?
>
> location1
> location1
> location1
> location2
> location3
>
>
> I don't understand why the second time that I run the script Hibernate
> accesses only one time to the database, and why the third and next times
> that I run the script Hiberante accesses 5 times to the database.
>
> The workaround for this issue is working with HQL, but it is much less
> desirable.
>
> Can somebody help me with this issue?
>
> Thank you very much in advance !!
>
>
> --
> View this message in context: http://grails.1312388.n4.nabble.com/fecth-join-does-NOT-work-in-Grails-2-0-1-N-1-problem-tp4492516p4492516.html
> Sent from the Grails - user mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>    http://xircles.codehaus.org/manage_email
>
>



--
Graeme Rocher
Grails Project Lead
SpringSource - A Division of VMware
http://www.springsource.com

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: fecth:join does NOT work in Grails 2.0.1 - N+1 problem

jl_monteagudo
Yes, it is this issue.

Thank you very much for your work!!
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: fecth:join does NOT work in Grails 2.0.1 - N+1 problem

Graeme Rocher-4
Administrator
It is a change in the way the Hibernate query cache works in 3.6.
Disable the query cache:

hibernate {
    cache.use_second_level_cache = true
    cache.use_query_cache = false // disable here
    cache.region.factory_class = 'net.sf.ehcache.hibernate.EhCacheRegionFactory'
}

There are good reasons for doing so as well. See

http://tech.puredanger.com/2009/07/10/hibernate-query-cache/

We have changed the default to false for the next release of 2.0.x

Cheers

On Wed, Mar 21, 2012 at 6:36 PM, jl_monteagudo <[hidden email]> wrote:

> Yes, it is this issue.
>
> Thank you very much for your work!!
>
>
> --
> View this message in context: http://grails.1312388.n4.nabble.com/fecth-join-does-NOT-work-in-Grails-2-0-1-N-1-problem-tp4492516p4493107.html
> Sent from the Grails - user mailing list archive at Nabble.com.
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>    http://xircles.codehaus.org/manage_email
>
>



--
Graeme Rocher
Grails Project Lead
SpringSource - A Division of VMware
http://www.springsource.com

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: fecth:join does NOT work in Grails 2.0.1 - N+1 problem

jl_monteagudo
Hi Graeme,

Now it is working perfectly.

Thank you very much for your help!!!
Loading...