- Print
- DarkLight
- PDF
Paging using Azure Logic Apps SQL Connector
- Print
- DarkLight
- PDF
In one of the previous #ServerlessTips, We have discussed how the underlying Azure connector service will include ‘protections’ to avoid unintended performance issues. This is required to prevent organizations from pulling too much data from a service, too quickly.
The SQL connector is no different. It does include protections from calling the connector too frequently, but it also has some limits on how much data can be retrieved at a time.
The current limit is 2048 records in a single call.
For example, we can create a simple logic app that runs on a schedule that will retrieve all rows from our Customers table. Once the records are returned, we will send the record count to Request bin through an HTTP action.
When we run this logic app, we determine that sure enough, only 2048 records are returned, even though our table contains 6762 records.
We can then use this information to restrict what page of records we want to return through our Offset statement.
When we configure our logic app, we need to provide the Execute stored procedure action with these values.
To address this requirement, we can use the following pattern where we use variables and an Until loop to continue to loop until we run out of records to process.
When we test our logic app we will discover that our loop ran 7 times which is correct.
Our table includes 6762 records. So the first 6 iterations of our loop return 1000 records each. Then the 7th, and final loop, returns 762.