Improve Performance for Ger rows (V2) and Add Row into a Table

(0) Share

Report

Posted on by IPC_ahaas

1,387 Moderator

I'm working on a Form that pulls record from our on-premises SQL Server as needed.

This has been a manual process for us to now and I've finally gotten a working automated process, so I don't have to do this each time it's requested (about once every two weeks).

The problem I've noticed is that my method is extremely slow:

1. I use a Switch to define the query

2. The query gets passed to the Get rows (V2), using the query created from the Switch above.

3. I then loop through the resulting data and add a row into the Excel template

Step 3 is where this is extremely slow. I can access the Excel document and watch as each record is slowly added. These requests have sometimes 10,000 records so it takes hours to complete.

What can I do to help speed up this process, knowing the final result is an Excel document?

Categories:

Building flows

I have the same question (0)

All responses (9)

Answers (0)

Sundeep_Malik 6,484 on at

Like (3)

Report

Hey @IPC_ahaas

There are two ways by which you can improve performance of your flow.

1) Easier but not the best: Use Concurrency

2) Make scripts to improve your performance. Check the following technique by @takolota

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-p/1624706

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (1)

Report
Couple of questions.

Is this data being appended to existing rows in an Excel Table, or are you creating a new Excel file for each Flow?
Are you fixed on an Excel file, or would you be ok with a CSV file? If you're ok with CSV, then you don't even need a loop and should be very efficient even with 10,000 records.

Was this reply helpful? Yes No
IPC_ahaas 1,387 Moderator on at

Like (0)

Report

1. Yes, only because from my research it's not possible to create an Excel file with data, only to populate an existing one.
2. Our company lives and dies by Excel, but I could always teach them how to convert it, so that would be sufficient.

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (1)

Report
For CSV you can do the following. Note that I'm getting my data from a SharePoint list, but exactly the same as if you were getting from a SQL table.

For my Start Time I've used the following expression to get a nicer date format:
formatDateTime(item()?['StartTime'], 'yyyy-MM-dd')

And for the File Name I'm concatenating "Log-" with the current date and time, then ".csv". This would give you a file name similar to Log-2022-11-24 014010.csv
concat('Log-', formatDateTime(utcNow(), 'yyyy-MM-dd hhmmss'), '.csv')

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (1)

Report

If you wanted to create a new Excel file each time, you could setup an Excel file with your Table that would be your base template. Then in your Flow you would Get content from that Excel file and use the content within a Create file to give you a new Excel file based on the template.

Was this reply helpful? Yes No
takolota1 4,974 Moderator on at

Like (1)

Report

@grantjenkins @IPC_ahaas

I’ve also been working on a flow to efficiently convert large CSVs to new Excel tables if you want to avoid teaching everyone how to manually convert things…

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-To-New-Excel-Table/m-p/1826096

Was this reply helpful? Yes No
IPC_ahaas 1,387 Moderator on at

Like (0)

Report

Concurrency shaved an hour and a half off the export time, so that is a huge improvement. Thank you!

Was this reply helpful? Yes No
IPC_ahaas 1,387 Moderator on at

Like (0)

Report

Impressive, but I want to avoid using OneDrive.

Was this reply helpful? Yes No
takolota1 4,974 Moderator on at

Like (0)

Report

@IPC_ahaas

You don’t need to. As long as you have a CSV output you can insert it in the CSV data + Set-up action so the rest of the flow can use that instead of OneDrive.

Was this reply helpful? Yes No