Performance question: how to query if instance has any association?

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

Performance question: how to query if instance has any association?

rosenfeld
I know that "if (myDomain.associations) println 'myDomain has
associations'" will work but it will also fetch all data from associated
instances.

Is there an easier/shorter way of querying this in GORM without having
to write code like this:

if (Association.executeQuery('select a.id from Association a where
a.myDomain.id=?', [myDomain.id], [max: 1]))

In SQL I would write something like:

select exists(select 1 from association where my_domain_id=100)

In Rails, I'd do something like this with ActiveRecord:

my_domain.associations.exists?

And the following SQL would be generated:

SELECT 1 FROM "associations" WHERE "associations"."my_domain_id" = 100
LIMIT 1

Another way of writing this in Rails would be (same generated SQL):

Association.exists? my_domain_id: my_domain.id

Thanks in advance,

Rodrigo.


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

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Performance question: how to query if instance has any association?

bobbywarner
Same model as other issue (Book and Reference):

def book = new Book(title: 'Some Book').save()
assert Book.count() == 1
if (book.references) {
    println 'no references'
}

Hibernate: insert into book (id, version, title) values (null, ?, ?)
Hibernate: select count(*) as y0_ from book this_

It's not loading all the Book data for me, just the count.  Is the issue you are trying to avoid loading this count?  Or am I missing something?


Thanks,
Bobby
Reply | Threaded
Open this post in threaded view
|

Re: Performance question: how to query if instance has any association?

burtbeckwith
In reply to this post by rosenfeld
Assuming you have a back-reference from Association to MyDomain (either "MyDomain myDomain" or "static belongsTo = [myDomain: MyDomain]" you can use

   int count = Association.countByMyDomain(myDomain)

which will generate SQL equivalent to

   select count(*) from association where my_domain_id=?

If you don't have the back-reference you'll end up with a join table, so this approach will work (it will work in both cases):

   int count = MyDomain.withSession { session ->
      session.createFilter(myDomain.associations, 'select count(*)').uniqueResult()
   }

which generates more complicated SQL that joins the three tables but is still very efficient and doesn't load the collection instances.

Here I'm assuming you have "static hasMany = [associations: Association]"

Burt

On Monday, February 27, 2012 07:14:09 PM Rodrigo Rosenfeld Rosas wrote:

> I know that "if (myDomain.associations) println 'myDomain has
> associations'" will work but it will also fetch all data from associated
> instances.
>
> Is there an easier/shorter way of querying this in GORM without having
> to write code like this:
>
> if (Association.executeQuery('select a.id from Association a where
> a.myDomain.id=?', [myDomain.id], [max: 1]))
>
> In SQL I would write something like:
>
> select exists(select 1 from association where my_domain_id=100)
>
> In Rails, I'd do something like this with ActiveRecord:
>
> my_domain.associations.exists?
>
> And the following SQL would be generated:
>
> SELECT 1 FROM "associations" WHERE "associations"."my_domain_id" = 100
> LIMIT 1
>
> Another way of writing this in Rails would be (same generated SQL):
>
> Association.exists? my_domain_id: my_domain.id
>
> Thanks in advance,
>
> Rodrigo.


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

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Performance question: how to query if instance has any association?

rosenfeld
In reply to this post by bobbywarner
This is probably some Hibernate optimization.

Try to set up your dataSources for development environment to persist
your database to disk and not drop tables when starting the application.

Then create the book and exit the shell. Run Grails shell again and
you'll get this for the "if":

b = Book.get(1)
Hibernate: select book0_.id as id0_0_, book0_.version as version0_0_,
book0_.book_id as book3_0_0_, book0_.title as title0_0_ from book book0_
where book0_.id=?

if (!b.books) println 'has associated books' // Book has many books, yes
I know it doesn't make sense but it didn't require creating another
class in my example application :P

Hibernate: select books0_.book_id as book3_0_1_, books0_.id as id1_,
books0_.id as id0_0_, books0_.version as version0_0_, books0_.book_id as
book3_0_0_, books0_.title as title0_0_ from book books0_ where
books0_.book_id=?

Cheers,
Rodrigo.


Em 27-02-2012 19:43, bobbywarner escreveu:

> Same model as other issue (Book and Reference):
>
> def book = new Book(title: 'Some Book').save()
> assert Book.count() == 1
> if (book.references) {
>      println 'no references'
> }
>
> Hibernate: insert into book (id, version, title) values (null, ?, ?)
> Hibernate: select count(*) as y0_ from book this_
>
> It's not loading all the Book data for me, just the count.  Is the issue you
> are trying to avoid loading this count?  Or am I missing something?
>
>
> Thanks,
> Bobby
>
> --
> View this message in context: http://grails.1312388.n4.nabble.com/Performance-question-how-to-query-if-instance-has-any-association-tp4426285p4426352.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
>
>
>


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

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Performance question: how to query if instance has any association?

rosenfeld
In reply to this post by burtbeckwith
Hi Burt, the issue with count is that it is an expensive operations for
most databases.

I mean, it is not *that* expensive, but it can be much faster using
"exists" or finding the first associated record that won't look the
entire index.

Thanks,
Rodrigo.

Em 27-02-2012 19:55, Burt Beckwith escreveu:

> Assuming you have a back-reference from Association to MyDomain (either "MyDomain myDomain" or "static belongsTo = [myDomain: MyDomain]" you can use
>
>     int count = Association.countByMyDomain(myDomain)
>
> which will generate SQL equivalent to
>
>     select count(*) from association where my_domain_id=?
>
> If you don't have the back-reference you'll end up with a join table, so this approach will work (it will work in both cases):
>
>     int count = MyDomain.withSession { session ->
>        session.createFilter(myDomain.associations, 'select count(*)').uniqueResult()
>     }
>
> which generates more complicated SQL that joins the three tables but is still very efficient and doesn't load the collection instances.
>
> Here I'm assuming you have "static hasMany = [associations: Association]"
>
> Burt
>
> On Monday, February 27, 2012 07:14:09 PM Rodrigo Rosenfeld Rosas wrote:
>> I know that "if (myDomain.associations) println 'myDomain has
>> associations'" will work but it will also fetch all data from associated
>> instances.
>>
>> Is there an easier/shorter way of querying this in GORM without having
>> to write code like this:
>>
>> if (Association.executeQuery('select a.id from Association a where
>> a.myDomain.id=?', [myDomain.id], [max: 1]))
>>
>> In SQL I would write something like:
>>
>> select exists(select 1 from association where my_domain_id=100)
>>
>> In Rails, I'd do something like this with ActiveRecord:
>>
>> my_domain.associations.exists?
>>
>> And the following SQL would be generated:
>>
>> SELECT 1 FROM "associations" WHERE "associations"."my_domain_id" = 100
>> LIMIT 1
>>
>> Another way of writing this in Rails would be (same generated SQL):
>>
>> Association.exists? my_domain_id: my_domain.id
>>
>> Thanks in advance,
>>
>> Rodrigo.
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
>      http://xircles.codehaus.org/manage_email
>
>
>


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

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Performance question: how to query if instance has any association?

burtbeckwith
You're not counting the whole table - you're counting rows where the foreign key matches. You should have an index on that column, so it's more expensive than an exists check but surely you have more important issues to solve than this.

Burt

On Tuesday, February 28, 2012 09:56:23 AM Rodrigo Rosenfeld Rosas wrote:

> Hi Burt, the issue with count is that it is an expensive operations for
> most databases.
>
> I mean, it is not *that* expensive, but it can be much faster using
> "exists" or finding the first associated record that won't look the
> entire index.
>
> Thanks,
> Rodrigo.
>
> Em 27-02-2012 19:55, Burt Beckwith escreveu:
> > Assuming you have a back-reference from Association to MyDomain (either "MyDomain myDomain" or "static belongsTo = [myDomain: MyDomain]" you can use
> >
> >     int count = Association.countByMyDomain(myDomain)
> >
> > which will generate SQL equivalent to
> >
> >     select count(*) from association where my_domain_id=?
> >
> > If you don't have the back-reference you'll end up with a join table, so this approach will work (it will work in both cases):
> >
> >     int count = MyDomain.withSession { session ->
> >        session.createFilter(myDomain.associations, 'select count(*)').uniqueResult()
> >     }
> >
> > which generates more complicated SQL that joins the three tables but is still very efficient and doesn't load the collection instances.
> >
> > Here I'm assuming you have "static hasMany = [associations: Association]"
> >
> > Burt
> >
> > On Monday, February 27, 2012 07:14:09 PM Rodrigo Rosenfeld Rosas wrote:
> >> I know that "if (myDomain.associations) println 'myDomain has
> >> associations'" will work but it will also fetch all data from associated
> >> instances.
> >>
> >> Is there an easier/shorter way of querying this in GORM without having
> >> to write code like this:
> >>
> >> if (Association.executeQuery('select a.id from Association a where
> >> a.myDomain.id=?', [myDomain.id], [max: 1]))
> >>
> >> In SQL I would write something like:
> >>
> >> select exists(select 1 from association where my_domain_id=100)
> >>
> >> In Rails, I'd do something like this with ActiveRecord:
> >>
> >> my_domain.associations.exists?
> >>
> >> And the following SQL would be generated:
> >>
> >> SELECT 1 FROM "associations" WHERE "associations"."my_domain_id" = 100
> >> LIMIT 1
> >>
> >> Another way of writing this in Rails would be (same generated SQL):
> >>
> >> Association.exists? my_domain_id: my_domain.id
> >>
> >> Thanks in advance,
> >>
> >> Rodrigo.


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

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Performance question: how to query if instance has any association?

rosenfeld
I never said I would be counting the whole table. See what I've written:
"...that won't look the entire index".

I do have more important issues to solve, but as I'm writing a batch
processing procedure that can take a full hour or more, I'm very
concerned about performance as I have lots of associated objects and I
don't want to process them if there is already any associated object. If
"count" will be fast enough to not slow down my procedure, I'm okay with
it, but if a bigger code with a "find first" approach will make my batch
processing go faster, I'll opt for it, of course.

Anyway, I was suggesting that if Grails is going to add some method for
verifying existence of some association, it should probably use "exists"
or "find first" approach instead of count.

Thanks,
Rodrigo.

Em 28-02-2012 12:03, Burt Beckwith escreveu:

> You're not counting the whole table - you're counting rows where the foreign key matches. You should have an index on that column, so it's more expensive than an exists check but surely you have more important issues to solve than this.
>
> Burt
>
> On Tuesday, February 28, 2012 09:56:23 AM Rodrigo Rosenfeld Rosas wrote:
>> Hi Burt, the issue with count is that it is an expensive operations for
>> most databases.
>>
>> I mean, it is not *that* expensive, but it can be much faster using
>> "exists" or finding the first associated record that won't look the
>> entire index.
>>
>> Thanks,
>> Rodrigo.
>>
>> Em 27-02-2012 19:55, Burt Beckwith escreveu:
>>> Assuming you have a back-reference from Association to MyDomain (either "MyDomain myDomain" or "static belongsTo = [myDomain: MyDomain]" you can use
>>>
>>>      int count = Association.countByMyDomain(myDomain)
>>>
>>> which will generate SQL equivalent to
>>>
>>>      select count(*) from association where my_domain_id=?
>>>
>>> If you don't have the back-reference you'll end up with a join table, so this approach will work (it will work in both cases):
>>>
>>>      int count = MyDomain.withSession { session ->
>>>         session.createFilter(myDomain.associations, 'select count(*)').uniqueResult()
>>>      }
>>>
>>> which generates more complicated SQL that joins the three tables but is still very efficient and doesn't load the collection instances.
>>>
>>> Here I'm assuming you have "static hasMany = [associations: Association]"
>>>
>>> Burt
>>>
>>> On Monday, February 27, 2012 07:14:09 PM Rodrigo Rosenfeld Rosas wrote:
>>>> I know that "if (myDomain.associations) println 'myDomain has
>>>> associations'" will work but it will also fetch all data from associated
>>>> instances.
>>>>
>>>> Is there an easier/shorter way of querying this in GORM without having
>>>> to write code like this:
>>>>
>>>> if (Association.executeQuery('select a.id from Association a where
>>>> a.myDomain.id=?', [myDomain.id], [max: 1]))
>>>>
>>>> In SQL I would write something like:
>>>>
>>>> select exists(select 1 from association where my_domain_id=100)
>>>>
>>>> In Rails, I'd do something like this with ActiveRecord:
>>>>
>>>> my_domain.associations.exists?
>>>>
>>>> And the following SQL would be generated:
>>>>
>>>> SELECT 1 FROM "associations" WHERE "associations"."my_domain_id" = 100
>>>> LIMIT 1
>>>>
>>>> Another way of writing this in Rails would be (same generated SQL):
>>>>
>>>> Association.exists? my_domain_id: my_domain.id
>>>>
>>>> Thanks in advance,
>>>>
>>>> Rodrigo.
>


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

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Performance question: how to query if instance has any association?

talldave
well, from your first message it looks like you've solved the problem, you're just wondering if there's a nicer way to do it.  my suggestion would simply be a bit of code reorganization.  instead of:

if (Association.executeQuery('select a.id from Association a where
a.myDomain.id=?', [myDomain.id], [max: 1])) { ... }

move that query to your domain class inside a helper method (or could probably do a namedQuery):

def hasAssociation() {
    Association.executeQuery('select a.id from Association a where
a.myDomain.id=?', [this.id], [max: 1])
}

and then your if statement becomes a lot nicer:

if ( myDomain.hasAssociation() ) { ... }


however, this is something you'll have to replicate for every association you want to check.  that could probably be solved with a bit of metaclassing magic, but i think it's also a valid suggestion that something which efficiently performs this check be added directly to grails/GORM itself.  

i know i've certainly written code like the above, checking for the existence of an associated collection, although most likely it's done before then iterating over that collection if it does exist, so that shouldn't really be a performance hit.  still, this does get towards one of my larger concerns about grails in general, is that it's easy to write code that's doing way more behind the scenes than you anticipate, and i wonder if that leads to some of the persistent questions about grails and performance.  i haven't used rails, and this may not be a specific grails concern but one which comes up anytime you're using an ORM-based solution.

btw, i did do a bit of research on this and you might investigate hibernate's "extra-lazy" mapping/setting/whatever, but apparently it's not "directly" supported by grails and may open up additional cans of worms.  :)
Reply | Threaded
Open this post in threaded view
|

Re: Performance question: how to query if instance has any association?

rosenfeld
Thank you for your response talldave. See comments inline.

Em 28-02-2012 20:18, talldave escreveu:

> well, from your first message it looks like you've solved the problem, you're
> just wondering if there's a nicer way to do it.  my suggestion would simply
> be a bit of code reorganization.  instead of:
>
> if (Association.executeQuery('select a.id from Association a where
> a.myDomain.id=?', [myDomain.id], [max: 1])) { ... }
>
> move that query to your domain class inside a helper method (or could
> probably do a namedQuery):
>
> def hasAssociation() {
>      Association.executeQuery('select a.id from Association a where
> a.myDomain.id=?', [this.id], [max: 1])
> }
>
> and then your if statement becomes a lot nicer:
>
> if ( myDomain.hasAssociation() ) { ... }

Yes, that is usually what I do in those situations, I was just wanting
to make sure I wasn't doing extra work if GORM already supported this
through some API I wasn't aware of.

> however, this is something you'll have to replicate for every association
> you want to check.  that could probably be solved with a bit of metaclassing
> magic, but i think it's also a valid suggestion that something which
> efficiently performs this check be added directly to grails/GORM itself.

I really prefer to avoid meta-programming whenever possible. And I'm not
talking about Groovy and Grails, but on Ruby and Rails too.

I think it can easily abused and it makes understanding and debugging
much harder. For example, I prefer the approach taken by ActiveRecord in
Rails:

class MyModel < ActiveRecord::Base
end

Since, Ruby supports modules mix-ins I would actually prefer something
like this:

class MyModel
     include ActiveRecord::Persisted
end

There is no magic happening here. When you do "MyModel.where(name: 'Some
name')", "where" is defined in the parent class ActiveRecord::Base.

In Grails, I'd prefer that my domain classes were explicitly inherited
from some GORM base class too. That would avoid the need of injecting
all those GORM methods to it.

Also, I'd prefer the domain classes to detect a missing Hibernate
session and obtain a new one. For example, when you're writing
multi-thread procedures, you'll have to use something like the
"executor" plugin just because you need a Hibernate session for
manipulating the database through your domain classes.

I think this should be transparent:

package gorm;
class Base {
   def get(id) {
      obtainHibernateSessionIfMissing()
      doActualGet(id)
   }
}

There are situations where meta-programming fits great, like testing
frameworks and suites, DSLs and a few other use cases.

But we should always consider if there is a simpler way of accomplishing
some result without resorting to meta-programming.

Also, when creating DSL, we should also try to provide an optional
parameter for avoiding dealing with odd situations like this:

def process(reference) {
    ...
    MyDomain.withCriteria {
      reference { ge 'position', reference.position} // won't work
      ...
    }
    ...
}

In those cases, I have to write ugly code like this:

   "${'reference'}" {ge 'position', reference.position}

When I could write something like this:

def process(reference) {
    ...
    MyDomain.withCriteria { c ->
      c.reference { ge 'position', reference.position}
      ...
    }
    ...
}

> i know i've certainly written code like the above, checking for the
> existence of an associated collection, although most likely it's done before
> then iterating over that collection if it does exist, so that shouldn't
> really be a performance hit.

It is the opposite in my situation. I want to skip processing if there
is already any associated element, so I want avoid fetching those
associated elements that I don't need.

> still, this does get towards one of my larger
> concerns about grails in general, is that it's easy to write code that's
> doing way more behind the scenes than you anticipate, and i wonder if that
> leads to some of the persistent questions about grails and performance.  i
> haven't used rails, and this may not be a specific grails concern but one
> which comes up anytime you're using an ORM-based solution.

I don't think any framework will allow beginners to write better or more
performant code.

The developer still needs to know how its tool works. I just feel that I
have a harder time trying to get my code to perform *and* read well in
Grails than I do with Rails.

And that is mainly due to API design, which I feel ActiveRecord API is
much more powerful than the GORM one.

People say that Rails is not for beginners. Neither is Grails. And I
agree. The difference is that I find Grails hard to use even for
experienced programmers, due to lack of good documentation, bad API and
lots of unnecessary "gotchas"...

> btw, i did do a bit of research on this and you might investigate
> hibernate's "extra-lazy" mapping/setting/whatever, but apparently it's not
> "directly" supported by grails and may open up additional cans of worms.  :)

Said this way, I'm afraid of even looking at this feature ;)

Thanks,

Rodrigo.


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

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Performance question: how to query if instance has any association?

Ian Roberts
On 29/02/2012 11:20, Rodrigo Rosenfeld Rosas wrote:

> When I could write something like this:
>
> def process(reference) {
>    ...
>    MyDomain.withCriteria { c ->
>      c.reference { ge 'position', reference.position}
>      ...
>    }
>    ...
> }

The standard Groovy get-out for name clashes in a builder like this is
to be explicit about whether you intend to call the builder (delegate)
or variable in the containing scope (owner):

delegate.reference { ge 'position', reference.position }

Ian

--
Ian Roberts               | Department of Computer Science
[hidden email]  | University of Sheffield, UK

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

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Performance question: how to query if instance has any association?

rosenfeld
Em 29-02-2012 08:44, Ian Roberts escreveu:

> On 29/02/2012 11:20, Rodrigo Rosenfeld Rosas wrote:
>> When I could write something like this:
>>
>> def process(reference) {
>>     ...
>>     MyDomain.withCriteria { c ->
>>       c.reference { ge 'position', reference.position}
>>       ...
>>     }
>>     ...
>> }
> The standard Groovy get-out for name clashes in a builder like this is
> to be explicit about whether you intend to call the builder (delegate)
> or variable in the containing scope (owner):
>
> delegate.reference { ge 'position', reference.position }

Thanks, this worked for this case. And how about the following example?

MyDomain.where { delegate.reference == reference }

I got this: "Cannot query property "delegate" - no such property on
class myapp.MyDomain exists".

Maybe if "where" recognized "delegate", "it" or "this", it would help
supporting such kind of queries.

Cheers,
Rodrigo.


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

    http://xircles.codehaus.org/manage_email


Reply | Threaded
Open this post in threaded view
|

Re: Performance question: how to query if instance has any association?

Ian Roberts
On 29/02/2012 14:07, Rodrigo Rosenfeld Rosas wrote:
> Thanks, this worked for this case. And how about the following example?
>
> MyDomain.where { delegate.reference == reference }
>
> I got this: "Cannot query property "delegate" - no such property on
> class myapp.MyDomain exists".
>
> Maybe if "where" recognized "delegate", "it" or "this", it would help
> supporting such kind of queries.

No idea, sorry.  I've never used the new where queries (as most of my
Grails apps are still on 1.3.x) but I believe they're implemented in a
different way with some sort of compile time AST transformations rather
than being purely a runtime builder-based DSL.

Ian

--
Ian Roberts               | Department of Computer Science
[hidden email]  | University of Sheffield, UK

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

    http://xircles.codehaus.org/manage_email