ViewModels are good for efficient fetching of data since they declaratively explain what data that will be used. This enables MDriven to scan thru the expressions and fetch data with a lot fewer queries.

If you have a root of Something that has a list of details and the detail  in turn fetch even more details you could easily end up with x*y*z queries to the database.

But since the ViewModel use expressions that may be stackable we can rather easily fetch all x, all x.y and all x.y.z with 3 queries.

This is the standard behavior when using the MDriven ViewModels.

When you use seekers the result is collected in a collection variable commonly named vSeekerResult.

Search result is not covered by the standard efficient fetch algorithm because this data is not available at load time. This may in certain situations be less than ideal so we have added strategies to improve the efficient fetching in ViewModels using seekers – this article explains how.

All the ideas implemented and explained below came from a real case where I had a fairly complex UI to visualize and manage demand forecast for articles

There are thousands of objects shown in this one single image – it is a seeker, but it can also get data from a list tied to current user that is called Favorites.

Even with the normal Efficient fetch turned on this UI typically spewed out up to 900 questions to the persistence server.

To fully understand where the questions came from I used the debugger:

Check the PMapper and you will see all the PMapper calls.

The ones that we look for are the ones that fetch only 1 object – these are candidates for improvement.

When the log sees that only 1 object is fetched it also shows the ClassId.

You can look up classid to name in the debugger:

Once we know what it is that create the queries we can think of ways to fix it.

The new strategy to further improve the efficient fetch is to add a ViewModel Nesting with fetching hints. Any ViewModel nesting with a name that starts with “FetchHints” will be found and the columns within will be executed on any list that seeker logic delivers.

Like this:

Working like this I could reduce the load from this UI from 900 queries to 30 something.

Now the problem with search was solved – but when the data came from the other source – the user favorites – this logic is not applied.

I had to devise a way to allow for me to tell the ViewModel that this logic should execute on a list of objects.

To solve this a new standard variable was introduced in ViewModels: selfVM

The selfVM variable is a reference to the ViewModel we work on – self is the object context as before – but selfVM is the ViewModel holding the objects.

I think that this is  a good extension that may solve some limitations we have had before.

the selfVM introduce 3 new operations: ExecuteFetchHints, Save, ExecuteAction.

The ExecuteFetchHints is obviously the one I was after in this case. Save and ExecuteAction was added since there has been a demand for programmatic access to these functions.

My code for showing the users favorites in the UI above was extended like this:

vSeekerResult->Clear;
Singleton.oclSingleton.User.FavoriteArticle->select(fa|fa.Filter.SqlLike(vFavFilter+’%’) or vFavFilter.IsNullOrEmpty).FavoriteArticles->collect(a|
— Add the SA for the users country
let sa=a.SalesArticles->select(sa|sa.MarketingCompany=self)->first in
  (
     if sa.isnull then
        vSeekerResult
     else
        vSeekerResult.Add(sa)
     endif    
  )
);
selfVM.ExecuteFetchHints(vSeekerResult) 

And then the same efficient fetching could be applied to the data coming this way as well.

Also, see: How to use the ExecutePS function in selfVM