Unanswered

API key with over 50k records to get, takes over 9 hrs to get 15k

(0) Share

Report

Posted on by VF-24072232-0

Hi All,

I have a flow that uses a do until loop to get each page of JSON data into a csv variable then export to csv on a SharePoint file.

It took over 9 hrs to only retrieve 15600 records.

The do until loop stops at a max 5000 but it only did 156 loops (100 records per page).

Does anyone have any other methods to get large amounts of JSON data?

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (0)

narayan225 2,547 Super User 2025 Season 2 on at

Like (0)

Report

I am not sure where are you getting the data from but, I used do until and fetchxml with cookies and page number to get 82K records.

The do until ran 17 times and the flow took 1 day and 10 hours.

I used following as a reference: https://softchief.com/2021/12/11/retrieve-more-than-5000-records-from-dataverse-or-dynamics-365-in-power-automate-using-fetchxml-paging/

Hope this helps.

Cheers!

Was this reply helpful? Yes No
VF-24072232-0 6 on at

Like (1)

Report

@narayan225 Thank you for that - I will check it out.

Is it normal/ ok to take that long for a flow?

Was this reply helpful? Yes No
NsL Coder 475 Super User 2025 Season 2 on at

Like (0)

Report

In my experience, high number of records shouldn't take so long.

For example, in one of our platform, we can get only 5000 records per call to the API service endpoint, we have total of just under 500K records, that means ~100 loops. That only took about 20 - 30 minutes.

Now, I will say that I am not doing exactly what you are doing, the 5K records come as JSON array, and I load them into Azure SQL using OPENJSON function, and loading 5K records into a table took 2-3seconds only, the API call can take 8-10 seconds

I suggest you take a look at each loop in your run history, and see how long each action within the loop took and see what is causing the long delay per loop.

Was this reply helpful? Yes No
narayan225 2,547 Super User 2025 Season 2 on at

Like (0)

Report

It depends on the data source you are working with. I was working with Dataverse and I had three apply to each loops inside my process and 2 get and one set actions.

Depending on what your are doing and the data source you are using the time it takes to run may vary.

Was this reply helpful? Yes No
srduval 1,760 Moderator on at

Like (0)

Report

Post some more details about how your "do" is setup and the increment variable.
Also check your concurrency settings. They claim by default it will process as many actions as possible, but my experience is that it typically processes sequentially one or two at a time. depending on what you are doing with the set variable, that might trip you up,

Was this reply helpful? Yes No
VF-24072232-0 6 on at

Like (0)

Report

@NsL Coder The last GET HTML page 155 finished at 6pm but the increment variable didn't change to page 156 until 3.29am. It then stopped the do until and created the csv file at 3.29am.

@srduval my do until is set up until the length(body('Parse_JSON')) = 0 . Count is set to max records at 5000. Timeout I left at PT1H.

The increment variable is set as an integer starting at 0, then incremented to the value of 1.

There is an array variable set before the do until (CSVArray_ExistingRecords), I added a compose after the Parse JSON to add that variable and then a set variable with the below value.

union(outputs('Compose'),body('Parse_JSON'))

Does this then reset the original variable? As the Create CSV table is from this variable named CSVArray_ExistingRecords.

I hope this all makes sense.

Was this reply helpful? Yes No
NsL Coder 475 Super User 2025 Season 2 on at

Like (0)

Report
Are you saying that you put 5000 in the Count field and PT1H in the Timeout field of the Do until?

This does not make sense. Do until Count determines the max # of times Do until will run, EVEN if condition hasn't been met. Timeout determines the max time the Do until will run, EVEN if condition or Count hasn't been met.

PT1H means 1 hour. There is no way your Do until ran for 9h 31m as suggested by your original post's screenshot.

did you perhaps not have the Timeout set to PT1H on the 9h 31m run?

Are you sure that you have 50K records? if so, 100 records per loop, you only need ~500 loops, why you put 5000 as the Count?

Are you certain your HTTP action is "progressing" Can you show screenshot of the HTTP action in edit mode? (obviously cross out any sensitive info like API secret, etc)

You said your condition is length(body('Parse_JSON')) = 0, can you show the Parse JSON's schema?

What is the purpose of the increment variable? (if you want to know within your loop which loop count you are in, you can use IterationIndex expression)

I am not understanding what you said you are doing with your Compose and variable, but to do what you want to do, one of the following should be done:

Compose action should be union(variables('CSVArray_ExistingRecords'), body('Parse_JSON)), then Set Variable will set the CSVArray_ExistingRecords with the output of Compose

OR

Compose action is set with CSVArray_ExistingRecords, then Set Variable will set the CSVArray_ExistingRecords to union(outputs('Compose'), body('Parse_JSON'))

I stronglly suggest you set the Count to something small to test, like 5, You can leave Timeout empty/not set, this way you can see in 5 loops, are you correctly getting 5 sets of 100 records, unique 500 records instead of the same 5 sets of 100 records.

Was this reply helpful? Yes No
VF-24072232-0 6 on at

Like (0)

Report
@NsL Coder

Thanks for the response

Are you saying that you put 5000 in the Count field and PT1H in the Timeout field of the Do until?

Yes - that's what was entered:

"type": "Until",

"expression": "@equals(length(body('Parse_JSON')),0)",

"limit": {

"count": 5000,

"timeout": "PT1H"

Start time: 7/8/2024, 5:58:17 PM (Local Time)

End time: 7/9/2024, 3:29:31 AM (Local Time)

Are you sure that you have 50K records? if so, 100 records per loop, you only need ~500 loops, why you put 5000 as the Count?

Yes, I have a power BI report with the API 53887 records but I think it would load quicker with a csv

Are you certain your HTTP action is "progressing" Can you show screenshot of the HTTP action in edit mode?

This is the HTTP action - there is a initialize variable outside the do until loop and it increments the page number

You said your condition is length(body('Parse_JSON')) = 0, can you show the Parse JSON's schema?

It's using the body of the HTTP output and the schema is below

{

"Id": "00000000-0000-0000-0000-000000000000",

"CreatedOn": "2024-07-07T22:15:25.927Z",

"LastModifiedOn": "2024-07-07T22:15:25.927Z",

"CreatedBy": "00000000-0000-0000-0000-000000000000",

"LastModifiedBy": "00000000-0000-0000-0000-000000000000",

"IsDeleted": true,

"DocumentTemplateId": "00000000-0000-0000-0000-000000000000",

"Name": "string",

"Type": "string",

"Hidden": true,

"CanDuplicate": true,

"IsPrivate": true,

"IsResource": true,

"IsFollowup": true

}

What is the purpose of the increment variable?

As above it is to increment the page number

I am not understanding what you said you are doing with your Compose and variable, but to do what you want to do, one of the following should be done:

Compose action should be union(variables('CSVArray_ExistingRecords'), body('Parse_JSON)), then Set Variable will set the CSVArray_ExistingRecords with the output of Compose

OR

Compose action is set with CSVArray_ExistingRecords, then Set Variable will set the CSVArray_ExistingRecords to union(outputs('Compose'), body('Parse_JSON'))

Me neither - seeing the flow run it has an empty array for both input and output [], in the set variable I have the value as

union(outputs('Compose'),body('Parse_JSON'))

I stronglly suggest you set the Count to something small to test, like 5, You can leave Timeout empty/not set, this way you can see in 5 loops, are you correctly getting 5 sets of 100 records, unique 500 records instead of the same 5 sets of 100 records.

Thank you - I've tested with 5 and it did return 500 records in 1 second. I will test again with a larger number and see how long it takes .

Was this reply helpful? Yes No