Canvas Apps – what is the n+1 problem and how to avoid it

When building cloud applications, efficiency must always be on the mind of the developer. If your users are limited in how many requests they can send per day, it is better to send fewer large requests, rather than many small requests. What I mean by this, is, if you are repeatedly running logic on a related table of data, sometimes it is better to pull an entire table (or a subset of relevant records) to the client, rather than retrieving many single records in separate requests.

When I refer to the N+1 problem, I’m essentially saying that if a gallery generates at least one request per item, it is going to chew up your user’s daily request quota very quickly on an anyway sizeable table. The term n+1 originates from “big O notation”, a way for us to roughly document the performance of applications using algebra.

A Sample Scenario:

We have a gallery that is showing a list of contacts that are stored in our database. For each contact in our gallery, we want to check if they have already received our June marketing email. If so, we want to disable the button on that contact’s row, otherwise we want to leave it active.

In the sample image we can see that we have 12 contacts in our gallery, along with 12 buttons that are either disabled or enabled based on their associated email records.

On the “Display Mode” property on the button within our gallery, we have a query that looks to see if there are any related emails to these contacts that match the following criteria:

1. Does the subject match the text from our Text input box?

2. Is the contact we are querying for a recipient of this email?

The Gallery button’s ‘Display Mode’ Property Formula:

If(
    IsBlank(
        LookUp(
            'Email Messages',
            Inpt_QueryEmailSub.Value in Subject 
            And
            ThisItem.Email in 'To Recipients'
        )
    ),
    DisplayMode.Disabled,
    DisplayMode.Edit
)

The above code works, however it is afflicted by the n+1 problem! This code triggers when the button component is rendered on the screen, and sends a query to dataverse asking it about the “Email Messages” table. So in this case, every time the gallery loads, it will lead to 13 requests, and if we add a second control to the gallery with the same behavior, the problem will get exponentially worse. A (N)2 +1 problem!!

The solution?

This can be an easy problem to overlook, and may only pop up in performance testing / monitoring. However, it is also an easy problem to resolve. My advice is to create a local collection of records to query instead. Typically I would create the collection on a new button’s “On Select” property, and it would look something like:

//The new button's 'On Select' Code
ClearCollect(RetreivedEmailMessages, 'Email Messages');

And then we would simply modify the original display mode code to use our local collection:

//The Gallery button's 'Display Mode' Property Formula
If(
    IsBlank(
        LookUp(           
            RetreivedEmailMessages,
            Inpt_QueryEmailSub.Value in Subject 
            And
            ThisItem.Email in 'To Recipients'
        )
    ),
    DisplayMode.Disabled,
    DisplayMode.Edit

Although, I would recommend adding filter criteria to only retrieve the relevant email records, otherwise you run into the risk of hitting data row limits in your application! I talk about more about data row limits here.

This subtle change reduced the request count from 13 to just 2. An 84% improvement in this case! 🙂