Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 3 posts ] 
Author Message
 Post subject: How to count quickly
PostPosted: Thu Dec 17, 2009 7:40 pm 
Newbie

Joined: Mon Jun 20, 2005 12:20 pm
Posts: 17
Hi, I need to count numbers of objects in search result for pagination in jsf table. The HQL query is:

Code:
select count(distinct o.objectId) from ObjectDB o left join o.properties p, ChoiceDB c, ObjectDB op where o.parent is not null and ((o.project.id=114 and (o.type.id=140))) or (o.project.id=147 and (o.type.id=168)))


I have only about total of 100 objects in MySQL database, but it takes 20015 ms to return the result. After counting, I exeecuted the similar query to get the first 8 objects for the page:

Code:
select distinct o from ObjectDB o left join o.properties p, ChoiceDB c, ObjectDB op where o.parent is not null and ((o.project.id=114 and (o.type.id=140))) or (o.project.id=147 and (o.type.id=168)))


It takes 125ms to return objects, which much faster. I thought that the count operation should be very quick since it does not need to load the objects from table. But in my case the performance for counting is not acceptable.

Any suggestion to improve the performance will be appreciated.
-ZJ


Top
 Profile  
 
 Post subject: Re: How to count quickly
PostPosted: Fri Dec 18, 2009 5:35 pm 
Newbie

Joined: Wed Oct 21, 2009 7:33 am
Posts: 11
You are not using ObjectDB op . Try taking that out.


Top
 Profile  
 
 Post subject: Re: How to count quickly
PostPosted: Tue Dec 22, 2009 3:31 pm 
Newbie

Joined: Mon Jun 20, 2005 12:20 pm
Posts: 17
Thank you for looking at the issue. I dynamically generate query based on the user's input. ObjectDB op may be used in some cases. Here is my simplified user case:

Quote:
class ObjectDB {
long objectID;
Set<AbstractPropertyDB> properties;
}

class ReferencedObjects extends AbstractPropertyDB {
long propertyID;
Set<ObjectDB> objectValue;
}


There is an association table (object_referencedObject) for the many-to-many relationship. The user may search for ObjectDBs which references to a give set of other ObjectDBs.

I made some changes in my query:

Quote:
select count(distinct o) from ObjectDB o left join o.properties p left join p.choiceValue as cv left join p.objectValue as ov where o.parent is not null and ((o.project.id=365 and (o.type.id=386 and ((p.propertyDefinition.id=410 and (ov.objectId in (429) and ov in elements(p.objectValue)))))))


It is now hundred times faster. After looking at the hibernate-generated query, I think there is still room to imporve since the query joins ObjectDB. Is is possible to avoid the self-join by using the associatiion table? Something like:

Quote:
select count(distinct o) from ObjectDB o left join o.properties p left join p.objectValue.objectID as ovID where ovID in (429)


Or simply put my question in this way: what the best way to search for parents for a given set of child IDs?

Again, thank and happy new year,
-ZJ


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.