Hi,
I'm new on this list, so I'm very sorry if I break all your rules...
I have devised a new way for eager loading collections, collections'
collections etc. at once without making the Cartesian product. I've
done a proof of concept implementation but I think it's solid enough
for further investigation
The basic idea is to add a "fork" to the query to add the missing
structure when two unrelated tables are joined as:
SELECT A.ID AS A, Forks.Fork, B.ID AS B, C.ID AS C FROM A
INNER JOIN (SELECT 1 AS Fork UNION ALL SELECT 2) AS Forks ON 1=1
LEFT JOIN B ON B.A = A.ID AND Forks.Fork = 1
LEFT JOIN C ON C.A = A.ID AND Forks.Fork = 2
While spending some time with eager loading I've also improved paging
in Sql server 2005+/CE 4.0 so that you can get root entities, say, 10
to 20 even though the query has eagerly fetched collections. I haven't
yet looked into if the same method can be used with other databases
If you have the time, you can read more about my ideas at
http://kuhnel.wordpress.com/2010/10/15/the-eager-fork-%E2%80%93-taming-cartesian-explosions-in-databases/
and
http://kuhnel.wordpress.com/2011/02/13/paging-eager-fetched-nhibernate-collections-efficiently/
and some proof of concept code at
https://bitbucket.org/nielskuhnel/eager-nh-extensions
I still have to do a proper performance comparison against lazy
loading and futures but my ad hoc results are encouraging. I've also
studied execution plans in SQL Server and the "fork" tables doesn't
seem to upset it in any way.
It's implemented as a custom dialect so no changes to the core are
needed per se.
Cheers,
Niels Kühnel
