Power Apps SQL Update Taking Time – Need Help with Efficient Bulk Update via Staging Table and Dynamic SP.

Question

Power Apps SQL Update Taking Time – Need Help with Efficient Bulk Update via Staging Table and Dynamic SP.

Dheeraj Singh 0

Question:

We are currently using Power Apps to perform manual data reconciliation between two tables — let’s say TBL-1 and TBL-2 (as shown in the screenshot below). Our users upload data from Excel via Power Apps and click “Update,” which triggers SQL Stored Procedures using the SQL Server connector.

Current Challenge:

The current implementation updates one row at a time (row-by-row update), which is resulting in performance delays as the number of records increases. Each update call from Power Apps sends an individual request, which is not scalable.

We want to change our approach to:

Insert all the rows (TBL1_ID, TBL2_ID, amounts, recon ref, etc.) into a staging table from Power Apps.
Then, trigger a single stored procedure from Power Apps that performs a bulk update for all matching records using a dynamic recon batch ID.
This should also allow validations to compare amounts from both tables by batch and alert if mismatched.

Attached SS for your reference.

1 answer

Your answer

Answer 1

Your attachments did not make it to the forum. Thus, I am not able to write any queries, if this is what you expected.

Generally, though, this makes me worried:

Insert all the rows (TBL1_ID, TBL2_ID, amounts, recon ref, etc.) into a staging table from Power Apps.

How would you do this? If you are still going to add one row to the table, there is not much gained. Whether you actually need the staging table, I cannot say, the main problem is the one-by-one processing. There is a certain overhead for each call. How much depend on a lot of things, but if the cloud is involved, the overhead is very significant. But it can certainly be on-prem as well.

If you introduced the staging table only to get rid of the one-by-one updates, you probably don't need the staging tables. But there can be other sound reasons to use it that I an not aware of.

As for how to send many rows at a time, there are a number of options:

Table-valued parameters.
SqlBulkCopy class.
Send data as XML that you shred in SQL Server.
Dito, but use JSON instead.

Exactly, how would do this from PowerApps, I don't know, since I am not familiar with PowerApps.

Share via

Power Apps SQL Update Taking Time – Need Help with Efficient Bulk Update via Staging Table and Dynamic SP.

1 answer

Your answer