Canvas Apps Performance

How to optimize fetching multiple records from a table

Multiple records

Introduction

There are several use cases when you might want to fetch multiple or all records from a table. A perfect example is that of Environment Variables. To learn more about Environment Variables, click here.

Approach 1 to fetch multiple records

One approach is to fetch each record separately.

Set(
    varFunctionAppURL,
    LookUp(
        'Environment Variable Values',
        'Environment Variable Definition'.'Schema Name' = "FunctionAppURL",
        Value
    )
);
Set(
    varAPIURL,
    LookUp(
        'Environment Variable Values',
        'Environment Variable Definition'.'Schema Name' = "ApiUrl",
        Value
    )
);
Set(
    varAPIMTeamsBaseURL,
    LookUp(
        'Environment Variable Values',
        'Environment Variable Definition'.'Schema Name' = "APIMTeamsBaseURL",
        Value
    )
);

Approach 2 to fetch multiple records

A better approach is to fetch all the records in a collection. And then to use the collection to fetch individual records. This is, of course, only possible if the number of records in the table are less than 2000.

ClearCollect(
    colEnvironmentVariables,
    'Environment Variable Values'
);
Set(
    varFunctionAppURL,
    LookUp(
        colEnvironmentVariables,
        'Environment Variable Definition'.'Schema Name' = "FunctionAppURL",
        Value
    )
);
Set(
    varAPIURL,
    LookUp(
        colEnvironmentVariables,
        'Environment Variable Definition'.'Schema Name' = "ApiUrl",
        Value
    )
);
Set(
    varAPIMTeamsBaseURL,
    LookUp(
        colEnvironmentVariables,
        'Environment Variable Definition'.'Schema Name' = "APIMTeamsBaseURL",
        Value
    )
);

Here is an image of the monitor tool showing the difference in times taken for these 2 approaches:

Screenshot of the monitor tool showing the time taken for data calls for both approaches to fetch multiple records
Data calls for both approaches to fetch multiple records

The total time for the 1st approach is: 269 + 94 + 88 = 451 ms

Whereas the total time for the 2nd approach is: 96 + 1 = 97 ms (1/5th of the time for approach 1)

Recent articles

  1. How to optimize passing a record to Patch
  2. Optimize fetching multiple columns of a record
  3. How to optimize Lookup function by not using dot operator

2 thoughts on “How to optimize fetching multiple records from a table”

Leave a Reply