Power Platform Community Forum Thread Details

Hi Community,

I've had a performance issue challenge to tackle over a year ago, and I was scratching my head to find a way to solve it.

The requirement is quite simple: Sync data saved locally through SaveData() function back to a SQL Server on-prem using Data Gateway. Initially, the developer used a direct connection Patching data to SQL Server. It was a nightmare merging data from a Collection to SQL Server. Choosing client-side merging for that scenario wasn't working at all, it was taking over 0.5h to 4h to sync.

I thought server-side merging would be best and integrating PowerApps with Power Automate could potentially resolve this issue.

I simply created an XML (or you can create a JSON) because I could not use JSON in that version of SQL Server.

I set a global variable with the result of XML text to use later as a parameter:

Set(
gloXMLParemeter,

Concat(
colCollection,

Concatenate(

"<note ",

"' to='", colCollection[@to],

"' from='", colCollection[@from],

" ></note>"

)

);

Then I created a flow in Power Automate to run a SQL Server stored procedure and process that XML.

I run the above flow from PowerApps and get all the ids back from SQL Server once the Response in the flow gives me all ids back.

Clear(colSQLServerIds, 'Flow'.Run("<ROOT>" & gloXMLParemeter & "</ROOT>"));

Well, the result was excellent. Flow runs in 1ms or less and the whole process of sending data back to SQL Server on-prem and updating all collections with the ids I get back from Power Automate flow takes less than 5 seconds. I guess in my case, server-side data merge was a choice.

Contat and Contatenate function documentation:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-concatenate#:~:text=The%20Concat%20function%20concatenates%20the,are%20available%20within%20the%20formula.

Run a flow from PowerApps documentation:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/using-logic-flows

Develop offline-capable canvas apps:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/offline-apps

Kind regards,

Ilson Biscuola

Categories:

Building Power Apps