Your browser may have trouble rendering this page. See supported browsers for more information.

|<<>>|151 of 273 Show listMobile Mode

Optimizing data access for high-latency networks: part IV

Published by marco on

 In the previous two articles, we managed to reduce the number of queries executed when opening the calendar of Encodo’s time-tracking product Punchclock from one very slow query per person to a single very fast query.

Because we’re talking about latency in these articles, we’d also like to clear away a few other queries that aren’t related to time entries but are still wasting time.

Lazy-loading unneeded values

In particular, the queries that “Load values” for person objects look quite suspicious. These queries don’t take a lot of time to execute but they will definitely degrade performance in high-latency networks.[1]

 Lazy-loads for people are very fast queries

As we did before, we can click on one of these queries to show the query that’s being loaded. In the screenshot below, we see that the person’s picture is being loaded for each person in the drop-down list.

 Lazy-loading pictures

We’re not showing pictures in the drop-down list, though, so this is an extravagant waste of time. On a LAN, we hardly notice how wasteful we are with queries; on a WAN, the product will feel…sluggish.

What is a load-group?

In order to understand the cause of these queries, you must first know that Quino allows a developer to put metadata properties into different load-groups. A load-group has the following behavior: If the value for a property in a load-group is requested on an object, the values for all of the properties in the load-group are retrieved with a single query and set on that object.

The default load-group of an object’s metadata determine the values that are initially retrieved and applied to objects materialized by the ORM.

The metadata for a person puts the “picture” property of a person into a separate load-group so that the value is not loaded by default when people objects are loaded from the data driver. This is a good balance because business logic will avoid downloading a lot of unwanted picture data by default.

Business logic that needs the pictures can either explicitly include the picture in the query or let the value be lazy-loaded by the ORM when it is accessed. The proper solution depends on the situation.

Lazy-loaded property values

As before, we can check the stack trace of the query to figure out which application component is triggering the call. In this case, the culprit is the binding list that we are using to attach the list of people to the drop-down control.

The binding list binds the values for all of the properties in a metaclass (e.g. “person”), triggering a lazy load when it accesses the “picture” property. To avoid the lazy-load, we can create a wrapper of the default metadata for a person and remove/hide the property so that the binding list will no longer access it.

This is quite easy[2], as shown in the code below.

var personMetaClass = new WrapMetaClass(Person.Metadata);
personMetaClass.Properties.Remove(Person.MetaProperties.Picture);
var query = new Query(personMetaClass);

With this simple fix, the binding list no longer knows about the picture property, doesn’t retrieve values for that property and therefore no longer triggers any queries to lazily load the pictures from the database for each person object.

The screenshot of the statistics window below shows up that we were successful. We have two main queries: one for the list of people to show in the dropdown control and one for the time entries to show in the calendar.

 Just a single query for person data

Final version

For completeness, here’s the code that Punchclock is using in the current version of Quino (1.11).

var personMetaClass = new WrapMetaClass(Person.Metadata);
personMetaClass.Properties.Remove(Person.MetaProperties.Picture);

var accessToolkit = new PostgreSqlMetaDatabase().AccessToolkit;

var query = new Query(personMetaClass);
query.CustomCommandText = new CustomCommandText();
query.CustomCommandText.SetSection(
  CommandTextSections.Where, 
  CommandTextAction.Replace,
  string.Format(
    "EXISTS (SELECT id FROM {0} WHERE {1} = {2})", 
    accessToolkit.GetName(TimeEntry.Metadata), 
    accessToolkit.GetField(TimeEntry.MetaProperties.PersonId), 
    accessToolkit.GetField(Person.MetaProperties.Id)
  )>
);
var people = Session.GetList<Person>(query);

Future, improved version

Once we fix the but in the WhereExists join type mentioned in the previous article and add the fluent methods for constructing wrappers mentioned in the footnote below, the code will be as follows:

var personMetaClass = 
  Person.Metadata.
  Wrap().
  RemoveProperty(Person.MetaProperties.Picture);

var accessToolkit = new PostgreSqlMetaDatabase().AccessToolkit;

var people = 
  Session.GetList<Person>(
    new Query(personMetaClass).
    Join(Person.MetaRelations.TimeEntries, JoinType.WhereExists).
    Query
  );

This concludes our investigation into performance issues with Quino and Punchclock.


[1]

You may have noticed that these calls to “load values” are technically lazy-loaded but don’t seem to be marked as such in the screenshots. This was a bug in the statistics viewer that I discovered and addressed while writing this article.

 Calls to 'load values' now marked as lazy loads

[2]

This is a rather old API and hasn’t been touched with the “fluent” wand that we’ve applied in other parts of the Quino API. A nicer way of writing it would be to create an extension methods called Wrap() and RemoveProperty that return the wrapper class, like so:

var personMetaClass = 
  Person.Metadata.
  Wrap().
  RemoveProperty(Person.MetaProperties.Picture);

var query = new Query(personMetaClass);

But that will have to wait for a future version of Quino.