Niels,
Your concepts are interesting.
A simple question: why are you using INNER JOIN (...) ON 1=1 instead of
CROSS JOIN?
Anyway: while the whole idea might work, it would be interesting to measure
it against existing approaches using real use cases. I suspect (read: guess)
the gains do not offset the loses.
Also, my general approach is to NEVER fetch collections. I prefer to use
load batching, or a couple roundtrips.
As applied to paging, this will get even easier when we introduce HQL syntax
for paging.
But as said: all research is useful, even if we don't end up using the exact
original idea.
Diego
On Wed, Feb 16, 2011 at 10:15, Niels Kühnel <[email protected]> wrote:
> 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
>
