RelationPredicateBucket vs Prefetch ?

Posts   
 
    
hemang
User
Posts: 4
Joined: 13-Nov-2014
# Posted on: 13-Nov-2014 18:29:40   

Hello,

As I understand little, (correct me if I am erong)

RelationPredicateBucket : Make the join query in back as you specified in relation (one query)

Prefetch : It prefetch the all entity added in PrefetchPath with its relation. For eg, if I have added 2 entity which has relation then it will generate 2 SQL query

Where to use which method? As per my knowledge, Join queries are more efficient then number of queries.

Would like to know the difference between them and which more efficient in which condition?

LLBLGen Pro version : 4.2 LLBLGen Pro Runtime Framework Using Adapter with entityFetchCollection Database : MS SQL 2008

Thank you

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 13-Nov-2014 19:14:14   

For entity fetching:

Relations/Joins are used to filter or limit the returned records of the main entity you are fetching. So you are not fetching records from related tables/entities.

Prefetchs are used to fetch related entities.

Please let me give you an example.

Example #1:

var filter = new RelationPredicateBucket();
filter.Relations.Add(CustomerEntity.Relations.OrderEntity..);
adapter.FetchEntityCollection(customers, filter);

The above code will fetch Customers, only those who have orders.


var prefetch = new PrefetchPath2(EntityType.OrderEntity);
prefetch.Add(OrderEntity.PrefetchPathCustomers);
prefetch.Add(OrderEntity.PrefetchPathOrderDetails);
adapter.FetchEntityCollection(customers, null, prefetch);

The above code will issue 3 SQL commands. One to fetch all Orders. One to fetch all Customers, filtered on the CustomerIds collected from the first query. Another to fetch all OrderDetails filtered on OrderIds collected in the first query.

Then the graph is constructed at runtime, so you get each OrderEntity with its correct OrderDetails collection and the Correct Customer entity.

hemang
User
Posts: 4
Joined: 13-Nov-2014
# Posted on: 14-Nov-2014 11:59:49   

Thanks a ton. I got the clarity on my query. simple_smile

As I understand from your article that, we should use "Prefetch" if you want to fetch the child entites also.

As we know prefetch creates the 3 SQLCommand (as per example you gave). Just to know more, Joins should be more efficient then fetching individual entity and then join internally.

Performance point of you Prefetch is efficient for huge amount of data for more than 3 entities related?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 14-Nov-2014 18:37:38   

It's a trade between doing 3 round trips, versus one trip but with much more data, because joins results in duplicates and a lot of redundant data.

Anyway you have the option to fetch a flat list of fields resulted from Joins by using the TypedLists or DynamicLists and then you have the option to project the resultsets ito Entities.