Prefetch Path Problem

Posts   
 
    
arschr
User
Posts: 893
Joined: 14-Dec-2003
# Posted on: 15-Jan-2014 16:04:40   

I have the following prefetch path statement:


ppe01.SubPath.Add(CgmBanEntity.PrefetchPathInvoices,5,null,null,new SortExpression(InvoiceFields.BillYyyymm|SortOperator.Descending));

The important point is that I am trying to sort on InvoiceFields.BillYyyyMm descending and then return the first 5 items from that list for each parent entity (CgmBanEntity).

Instead it seems I am getting the first 5 items from the list for each parent those 5 are then sorted in descending sequence on this field.

I haven't put together a test case yet, I first wanted to check that my expectation is the way it should work, sort first then limit the list.

LlblGen 4.1 OrmSupprtClass v4.1.13.206

(If I do need a repro case, do we use adventure works?)

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 15-Jan-2014 22:19:31   

Please check and post the generated SQL for the prefetch query.

Please note that the prefetch Query is run once, not once per parent. So it fetches all related entities to all parent entities fetched in the previous query. Filtered by a range of parent Ids or be a subquery to the parent.

arschr
User
Posts: 893
Joined: 14-Dec-2003
# Posted on: 15-Jan-2014 23:18:38   

Right now I think I was just not understanding the data I was seeing well enough, and what LlblGen is doing is fine.

but could you verify that:

my expectation is the way it should work, sort first then limit the list.

I do understand that the query runs once not once for each parent

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 16-Jan-2014 06:51:06   

arschr wrote:

but could you verify that:

my expectation is the way it should work, sort first then limit the list.

Yes, it should work that way. Please make a test and confirm that. You also should look into the generated SQL to understand what is happening behind the scenes. Check Troubleshooting and debugging or use OrmProfiler.

David Elizondo | LLBLGen Support Team
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 16-Jan-2014 10:56:03   

It indeed sorts the subpath query, then for each parent, it reads the first 5 elements from the resultset. This is done in memory (skipping rows in the datareader till the right row is read), so if there are parents which have a tremendous amount of child elements, this is not very efficient. It's however not possible to do otherwise (perhaps with a CTE, but I'm not sure), as the single query has to return per parent the first 5 elements, but there's no statement in sql which can do that.

Frans Bouma | Lead developer LLBLGen Pro