I have an application with an entity caled Environment, which has manytomany relationships over a bunch of classes (between 10 and 15).

The database is big (> 1GB)

At one point I need to select a set of one of those classes which belong to a specific environment.

So my HQL is:

SELECT n FROM Note n JOIN n.environments e WHERE n.status = :nstatus and (e.id = 1 OR e.id = 2)

The point is that that triggers loading every environment in the system and I think many of the hanging instances from those hanging classes. Therefore to select a set of 10 objects it takes over three minutes. It takes ages! I need to read only the environment id associated but not anyother of the enviornment field values.

So Ideally I would like to filter by environment.id without needing to load the entire environment object which is huge, and makes my query calls take too long.

I have tried annotating all hanging relationships from environment whit FetchType.Lazy and LazyCollectionOption.TRUE

but have had no luck.

Any ideas?

Thanks in advance.

Carlos.