Power Platform Community Forum Thread Details

Below are questions that I would need answers for and scenarios that I am facing while building the App

1. What are the best practices of using SQL with PowerApps (For instance, the PowerApps takes 2-5 minutes to retrieve the output data from On Prem SQL Stored procedure)
2. What will happen if I do a bulk edit in PowerApps for an SQL Table? (For instance, Can I use a Customized PowerApps function/control to UPDATE/INSERT SQL Table for hundreds of records in a single screen? )

Thank you in advance

Categories:

Building Power Apps

@Suresh1409

Using SQL as a data source for PowerApps is very effective.

There are some considerations to doing this properly (as with any datasource).

In regard to getting data:

1) Make sure you limit unneeded information. If you are not going to use fields in your app, don't bother returning them or including them.

2) If you find performance issues - first vet your query on the SQL server to determine its execution duration. If you find issues with that, modify your query for better performance.

3) In addition to #2 above - Make sure you INCULDE any filtering properties to your query in your test. Sometimes (on SQL) simply adding a filter (where clause) will dramatically, and negatively, impact performance.

4) Use views!

In regard to updating/creating:

1) You will be able to update as many tables and fields as you want. You will find that many operations will impact your performance. Consider a "fire and forget" approach if performance is an issue. With this, you can utilize Flow/PA to "fire" a payload of record alterations (usually in a JSON format) and then have Flow/PA "digest" the payload and perform the operations. You can even go a step beyond that and have flow provide the transaction to SQL and then, via trigger, process the payload directly on the SQL server. This then has very minimal overhead to PowerApps or even to Flow/PA. I have found this method highly effective for app performance.

2) Keep it simple. Where possible make sure you are limiting the information that you are updating unnecessarily. In other words, make sure you do more "logic" in your app to determine where and when an update/insert is needed.

Finally, when it comes to operations that "just take time", no matter how much you try - consider user feedback or async-offloading. For user feedback, it can be as simple as displaying messages or doing a timer-based automation. For async-offloading, this would be achieved using a control such as a Toggle to perform the operation while the user goes about the rest of the work.

Of course, all of this will depend greatly on your design and needs. So, your mileage will vary.

I hope this is helpful for you to some degree.