I'm not sure why i used INNER JOIN .. 1=1. Cross join is more clear, thanks.
Anyway, I like the power in making general expressions as:
"I want to show these companies in my list, and for all of them I need these
relations for what I'm going to show. Please give me them as fast as
possible, but I don't really care how"
_session.QueryOver<Company>().Skip(20).Take(20).OrderBy(x=>x.Name).Asc)
.EagerFetchMany(x => x.PushReportInfos)
.EagerFetch(x => x.AgreementStatus)
.EagerFetchMany(x => x.SecurityTokens)
.EagerFetchMany(x => x.NaceEntries)
.EagerFetchMany(x => x.NaceEntries.First().NaceEntry)
.Where "some really complicated stuff with EXISTS and stuff"
You can get that with subselects too, but in that case "some really
complicated stuff" will be repeated for each sub select.
In my approach filtering is only done once, and the database engine simply
joins the tables on the resulting root entities
If you follow an approach whith a query for only the root entities and then
get the rest by using some kind of " WHERE IN (root ids)" approach
you're essentially doing the same as me, except that your need more code and
you are sending a lot of ids to and fro.
You are especially likely to get into trouble if you need nested relations
or if you for some reason need to consider thousands of entities.
I still need to do some performance comparisons though, as everything else
is just hand waving :)
On 16 February 2011 20:36, nadav s <[email protected]> wrote:
> 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
>>
>>
>
