Blog

Performance - Why is LookUp better than First/Filter?

Two popular ways to retrieve a single record are to call LookUp, or First(Filter. From a performance perspective, it's better practice to call the LookUp function to return a single record. This to post describes the reason why it's better to call LookUp, compared to First/Filter.

There are two popular methods to return a single record - we can call the LookUp function, or we can construct a formula that combines First and Filter. Although the best practice is to call to LookUp function where possible, the First/Filter combination is still very popular. This post describes why LookUp is a better choice.

Example scenario

As an example, let's take a system that stores issue records. Against each issue record, users have the ability to mark whether or not an issue is an emergency.


The requirement is to add a form to an app that displays the first record that is marked as an emergency. To accomplish this, we add a display form to a screen, and we set the data source property to the issue table.


To configure the form to display the required record, we set the Item property to a variable, which we set in one of two ways.

We can apply the LookUp function like so: 

Set(varRecord,
    LookUp('[dbo].[Issue]', IsEmergency=true)
)
Alternatively, we can apply the First/Filter pattern like so: 
Set(varRecord,
    First(
          Filter('[dbo].[Issue]', 
                 IsEmergency=true
          )
    )
)

How well does the LookUp pattern perform?

To examine how well the LookUp function performs, we run the screen and inspect what happens using the monitor tool. My post here describes how to use monitor in further detail.

From here, we see that the app issues a query that requests a single record. The presence of the top argument in the request confirms this.



If we now examine the response, we see that the data source returns just a single record record.

How well does the First(Filter.. pattern perform?

If we now repeat the same process with the First/Filter formula, we see that the data retrieval process is far less efficient

Because Filter function is designed to return multiple records (as opposed to a single record with the LookUp function), Power Apps requests 500 records from the data source. In practice, this value corresponds to the 'data row limit' value in the app settings, which could be up to the maximum of 2,000 records.

If we examine the response, we see that this respose takes 1,413ms as opposed to 138ms, and the size of the response is 45KB, as opposed to 0.4KB.


Arguably, the impact of using First/Filter would not be so bad if we were retrieving a record by a primary key value. However, LookUp also has the added benefit of being more readable, compared to calling to two nested functions.

Conclusion

There are two popular ways to retrieve a single record. We can call the LookUp function, or use a combination of the First and Filter functions.  The First/Filter pattern is more inefficient because Power Apps can potentially retrieve up to 2,000 rows when we just want to take a single record. This unnecessary data transfer will reduce the performance of an app..
Previous
Next
Related posts