You can try even fetch="subselect" to upload each collection in just one roundtrip For instance given a Company with a collection of Contacts when you get the first page of Companies NH will fetch all contacts of the first page in one roundtrip. fetch="subselect" may fail in some circumstances under MSSQL thank to its nice way to get pagination (perhaps in 2012 the will realize that result-sets are paged in web and that developers are humans).
On Wed, Feb 16, 2011 at 2:21 PM, Diego Mijelshon <[email protected]>wrote: > 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 >> > > -- Fabio Maulo
