Load related entities with a single query

[pz07]

As far a I can see the library load every related entity of a given entity with a separate SQL query sent to the DB. This holds even for entities that are marked as lazy loaded.

E.g. for a entity with 3 related one-to-many entities, any select query sends 3 additional queries to the DB, a query per one-to-many relation. For a select query returning multiple rows, 3 additional queries are sent for every row. For a query returning 1000 rows it gives 3000 queries in total.

This is a huge performance bottleneck. I haven't find anything in the documentation, which could potentially solve the problem (except excluding some relations for the query, but this would case objects lacking relations, so it's not an option if the functionality needs it).

Is there a way to overcome this limitation?

[kostaskougios]

There are test cases that make sure one to many are not fetched. I just run OneToManyLazyLoadSuite / test("select is lazy") and it is OK.

https://github.com/kostaskougios/mapperdao/blob/master/src/test/scala/com/googlecode/mapperdao/OneToManyLazyLoadSuite.scala#L25

If something access the one-to-many fields, then those will be loaded. A debugger for example will always access them, forcing mapperdao to fetch them.

Do you have some code where the issues occurs?

[pz07]

I don't consider it as a bug. It's just performance bottleneck.

Related entities could be marked as lazy loaded, true. I doesn't solve the problem, though, in a use case when some huge number of entities needs to be processed and all related entities are likely to be used in the process. It would end up in roughly the same number of queries sent to the DB.

Ideally, it should be a single query (optionally paginated) with a join for every relation. I tried to do something like this by using select ... from ... join type of query, but although the query sent contains the join, the related entity itself if loaded with a separate query for each base entity anyway.

[kostaskougios]

Ah yes well mapperdao is not optimized that way I am afraid. It has the N+1 selects problem.