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
Login
FAQ
Search