fetch=subselect does a subselect for the original query that isn't really neccessary because you have the ids in the session, and that subselect might be a big one sometimes. I think batch size is the best approach. The thing is that currently you have to set the value in the mappings... there is a patch i've uploaded to jira, i know fabio doesn't like it, still don't really understand why exactly, but it enables you to override the batch size that was set in the mappings per session, and i think it could be useful in plenty of use cases.
On Wed, Feb 16, 2011 at 7:41 PM, Fabio Maulo <[email protected]> wrote: > 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 > >
