USING FETCH PLANS FOR OPTIMISED DATABASE SERVER ACCESS

  Fetch Plans are very powerful feature of OpenAccess ORM. When interacting with a persistent object, they provide us with the possibility to fine-tune the fetching of the fields we are using from the database and not the whole object. This is resulting in better performance and less load on the database server.

  A Fetch Plan represents collection of strings. These strings are the names of the Fetch Groups which includes the fetched fields for each of the persistent classes. A field can be a part of a given fetch group when marked with the FetchField attribute with the name of the desired fetch group given as parameter.  There can be more than one FetchField attributes assigned to a field so it can be fetched in different groups depending on your project’s business logic.

  There are different ways to define fetch groups and organize the fetch plan: defining attributes by hand, storing information in the app.config, etc. In this article however the focus is on the easier and more intuitive visual support, provided by the OpenAccess wizards inside Visual Studio.

  To define a fetch group we should start the forward mapping wizard, choose the class that you want to define a group for, and select the FetchGroup tab. It works for reverse mapped persistent models also.



  After doing so, click the Add/Remove button. Then add a position, type a name for it (you are defining a fetch group) and click Ok.

   Since you have defined a fetch group for the chosen class, a column of check boxes is displayed next to the class fields. You need to check the checkboxes next to the fields you want fetched when a plan containing your fetch group is executed.
 

  If a given field is of reference type the following fetch group feature can be used - you can define the next fetch group that is executed when accessing the reference field/property:


  That means that not all the fields from the referenced object will be fetched but only those that are specified in the fetch group of the referenced object named with the string we type in our Next Fetch Group text box.

To get you closer to the idea of this, here is a simple example to display the functionality we refer to:
There is a fetch group defined for the Order class of the Northwind data-model called “theGroup":

   The OrderDetails reference field is among the selected for fetching and a Next Fetch Group also called “theGroup” is defined for it:


  This group is also defined for the OrderDetails class and the product field is selected:


   Same step is repeated for the Product class where only the product name and id are added to be fetched :


  To get advantage of our defined fetch groups we need to add them to our fetch plan by calling the FetchPlan.Add() method of the object scope. We can add the names of the fetch groups for the different classes, or only the name of the fetch group for the ”root” class if our groups are logically connected in a hierarchy(using the “Next Fetch Group” feature) as we have done in our case. Before we add any fetch groups though, we first need to clear the scope’s current fetch plan. That is done by calling the scope’s method FetchPlan.Clear(). The fetch plan by default contains only the “Default” fetch group. The “Default” fetch group tells the object scope to retrieve all the value fields of an object and only the ID’s of the reference ones.

Here is the complete sample code:
  When you require access to only some of the properties of a persistent object that is few levels deeper in the object graph, using the appropriate fetch plan can be a considerable performance booster. In our case we only extract the order objects. In our logic then we need to access all their details, the products of the details and only the names of those products. By “telling” the scope’s fetch plan to use our defined fetch group hierarchy all this is possible and no additional calls to the database are made.