How to optimize fetching multiple records from a table

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:

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