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)
2 thoughts on “How to optimize fetching multiple records from a table”