the sub select is always inefficient, especially when there is an initial
complex query (with sub queries in it), and its a killer when its a two
level tree (when fetching the grandchildren). fixing it was really really
easy, and i can't see any downside to it.
different use cases in a web app:
use case 1: sub select\batch size is NOT desired
the user searches for car companies by some criteria. the user will then
choose (double click on a grid's row or something) one of the
companies to see it in full details. each company has one-to-many car
types (mazda -> mazda 3, mazda 5, mazda 6...) and each
car type will be displayed in its own tab, when at first, the newest car
type or the most expensive one, doesn't matter is selected.
each car type has its models, mazda3 2008 isn't the same as 2010 (i don't
that much about cars and not sure the years are correct,
but there are differences between the models).
the result: if carType.Models is mapped with some batch size, say 10, the
models of 10 of the car types are now fetched, although
the user only watches the models of one of the car types, if there could
be lots of models for each car type, it slowed the first tab,
and made the other tabs faster, because their car types are now loaded,
but its not what is desired, because the user is expected to
click on only one of other tabs or something.
use case 2: desired:
the user wanna see some custom developed report (ui that can be
implemented with MRS/Cognus or any other reporting framework,
and we have all kinds of reports that live up to this definition, and
for some good reasons also). for the report the user searches for
car companies by some criteria (some search form) and then expects to
see the returned companies, paged of course, but with all
of their car types, and for each of the car type - all of its models.
here, a sub select or batch fetching is a must or else we'll get a CP
with join fetching, or N^2 + 1 if we do regular lazy loading (like we
wanted to do in the first situation).
of course we can work around that, and thats exactly what we do, using a
generic mechanizm that for reports, eager fetches with sub selects and not
joins, the association it was asked to fetch. for the regular queries, it
just use the default which is regular lazy.
it would have been really really nice, if i could have set, for the report
query, query.SetFetchMode("CarTypes", FetchMode.SubSelect)
or if you will, query.SetBatchSize("CarTypes", 20)
and same for models
query.SetFetchMode("CarTypes.Models", FetchMode.SubSelect) or
query.SetBatchSize("CarTypes.Models", int.MaxValue).
it must be max value because i want all the models, and can't possibly know
how many car types are going to be there. of course it won't be alot,
because the "query" is going to use paging, but i don't really know if its
20, 40, or something else.
batch size, currently makes me choose between the use cases, slowing down
one of them, or makes me query and connect the associations my self. same
goes for sub select, which also issues an inefficient query for CarTypes and
a killer query for the Models
before my fix it would have been:
select ...
from Models m
where m.CarTypeId in
(select c.Id
from CarTypes c
where c.CompanyId in
(select company.Id
from Companies company
where <could be some crazy crteria - this is the same where
clause of the very original query>))
(i was able to make itthe inefficiency of the query
On Wed, Sep 1, 2010 at 6:58 PM, Fabio Maulo <[email protected]> wrote:
> I don't know which is the problem... you said that there is a problem and
> you want change it using the same tech used by batch-size (using uploaded
> ids) because subselect seems inefficient in some cases.
>
>
> On Wed, Sep 1, 2010 at 12:48 PM, nadav s <[email protected]> wrote:
>
>> btw, i don't really get what is the problem with subselect, as it lets you
>> efficiently fetch a whole object graph for the N fathers that were fetched
>> in some query, in the most efficient way possible
>>
>>
>> On Wed, Sep 1, 2010 at 6:46 PM, nadav s <[email protected]> wrote:
>>
>>> i don't think its thats low priority, because it is actually a thing
>>> people expect to happen when they set a fetch mode to Eager, at least i've
>>> seen alot of situations when people really thought that thats whats going to
>>> happen (later finding out it killed their query with CP)
>>>
>>> about when it is helpful - exactly in the situations diego described. two
>>> use cases,
>>> in one of them you query the fathers and gonna need only one of the
>>> father's collection, and for the other
>>> you're gonna need all of their collections.
>>> it gets more complicated when there are grandchildren involved, and in
>>> one of the situations you want the grand children of one of the childs, and
>>> in the other situation, because you load an object graph, you're gonna need
>>> all of them.
>>>
>>> now, either you implement (similar to what diego said) the loading of the
>>> collections yourself, or you gonna have to live with the batch size slowing
>>> down the first situation, where you would have prefered lazy loading without
>>> batching
>>>
>>>
>>> On Wed, Sep 1, 2010 at 5:22 PM, Diego Mijelshon
>>> <[email protected]>wrote:
>>>
>>>> I have entities where batch loading helps in some use cases but it loads
>>>> lots of unneeded entities/collections in other complex use cases, where I
>>>> have many proxies but only use a few.
>>>> My current workaround is doing "manual batch loading" (i.e. dummy query)
>>>> in the cases where I need it.
>>>>
>>>> It would be definitely a low-priority but nice-to-have feature.
>>>>
>>>> Diego
>>>>
>>>>
>>>>
>>>> On Wed, Sep 1, 2010 at 10:12, Fabio Maulo <[email protected]> wrote:
>>>>
>>>>> It is possible for batcher (INSERT, UPDATE,DELETE).
>>>>> I don't understand where it is useful for collection/relations
>>>>> batch-size.
>>>>>
>>>>>
>>>>> On Wed, Sep 1, 2010 at 9:37 AM, Diego Mijelshon <
>>>>> [email protected]> wrote:
>>>>>
>>>>>> Being able to override batch-size would be useful. Implementing it
>>>>>> requires messing with more than one part of the infrastructure, though.
>>>>>>
>>>>>> Diego
>>>>>>
>>>>>>
>>>
>>
>
>
> --
> Fabio Maulo
>
>
