Processing large SQL datasets into string variables

[traecal]

Ultimately my final goal is to create an PDF report with a listing of all my data (incoming from a SQL stored procedure) formatted for the end user. Currently I have a flow that functions perfectly to that goal. The issue is that while fine for smaller datasets, it takes 20+ minutes to run for larger datasets which is well beyond the limit any of my users would want to wait for an on-demand report.

I've done several things to try to optimize the script, even embedding all the HTML code for the row data into the stored procedure so that the only thing the flow needs to do is load all the data into a single string variable and give that to Onedrive to convert into the PDF (previously I created the HTML from traditional columnar SQL data which took even longer).

I've searched low and high and found the only way to get data from SQL into a string variable is to parse the response, and then run an apply to each on those records to put into a string variable as such:

For context you can see my data already has the HTML tags and such in it. It is just a single column of text data with essentially one table row record per line.

I take this data, append it to a header and a footer record that contains the rest of the HTML styling, then convert to PDF. Every step in this process runs quickly except the Apply to Each....which takes as I mentioned 20+ minutes when I get to higher row counts. My live app currently does all this within the PowerApp (no stored procedure, just SQL read from the table) that would be calling this Flow, it does it very fast, but I'm stuck in a 2,000 row limit and the users are exceeding that.

My question is, do I have any other way to get these rows into the variable other than iterating? I've passed all the work to the stored procedure so I don't need PowerAutomate to do anything but concatenate.

[ekarim2020 · Accepted solution]

You could try this method, using a Select action to select all the Record Properties for each row. Then use the Join action to join the strings of 'Record' property' together ('append'), but joining the strings with a space character in between. The join with field is a required field so we can't leave it empty.

 

Hope this helps.

Ellis

 

[traecal]

Ellis - THANK YOU - I cannot tell you the number of hours I had into this building all the backend and having everything working until I scaled it up. The same record count that was taking 20+ minutes is now under 2 minutes which for a 140 page report isn't the worst performance all things considered. It absolutely would have never occured to me to take this approach...in fact I didn't even know about that text mode. I honestly thought I was going back to the client this morning to tell them they had no choice but to break this up into pieces...so again, THANK YOU!