Blog

SQL - What you need to know about bulk updating records

Bulk updating SQL data from Power Apps can be very slow. Read this to find out why.

The two main functions to update records are Update and UpdateIf. Sadly, these functions suffer from a couple of weaknesses. They can perform very slowly or at worst, they might not update all of the records that we expect. This post describes this behaviour to prevent you from being caught out by this.

Not all expected records are updated

To demonstrate, I’ll create a table called Journey. There are 1000 records in this table.


Next, I'll create a new app and add a button with the following formula. 

 UpdateIf('[dbo].[Journey]', JourneyID > 800, {JourneyDesc:"Journey approved"})

The purpose of this code is to set the description to 'Journey approved' for all records with a JourneyID greater than 800.

When I run the app and click the button, NONE of records are updated. This is obviously wrong. Why does this not work?

The reason is because the UpdateIf function is NOT delegable. By default, the 'data row limit for non-delegable queries' setting on a new app is set to 500 and therefore, the UpdateIf function will not update records above row 500. The only thing to mitigate this behaviour is to increase the delegation limit. But even when we do this, the problem will still occur with tables with more than 2000 rows.

Another problem is that the designer doesn't highlight the fact that UpdateIf is non-delegable. Usually, we would see a blue warning when we issue queries that are non-delegable. The absence of a warning here means that app builders might not be aware that UpdateIf won't work with large record sets.

The process works very slowly

To fix this issue, I increased the delegation limit to 2000 and ran the UpdateIf function again. The good news is that it updated all of the expected records. The bad news is that it ran very slowly, taking almost 2 minutes to update 200 records.

Why the slow performance? To find out, I ran SQL Profiler. Here's the result.



This profile shows that to update 200 records, PowerApps calls SQL Server 200 times - once for each record that it needs to update. If you're familiar with SQL Server, you'll know that this looks bad. Making hundreds of calls like this is both inefficient and time consuming.  The time it took to update 200 records was 75 seconds. This is an awful long time for a user to wait. 

I hope that in a future release, Microsoft can improve the way that it delegates this query, and reduce it a single call to SQL that looks like this:

update [dbo].[Journey] set [JourneyDesc] = 'Updated by Tim', [MileageInMiles] = 0, [UserID] = 0
output [inserted].[JourneyID], [inserted].[StartDate], [inserted].[UserID], [inserted].[VehicleID],
[inserted].[MileageInMiles], [inserted].[EndDate], [inserted].[JourneyDesc]
where [JourneyID] > 800

Does Patch work in the same way?

Another function that can update multiple records is Patch. Most often, app builders use Patch to update single records. It isn't commonly used to update multiple records and the usage of Patch to update multiple records looks like this:

Patch( DataSource, BaseRecordsTable, ChangeRecordTable1)

To update the SQL Journey table with Patch, I ran the following formula:

ClearCollect(BaseRecordsTable, Filter('[dbo].[Journey]', JourneyID > 800));
ClearCollect(ChangeRecordsTable, 
             UpdateIf(BaseRecordsTable, JourneyID> 800, {JourneyDesc:"Journey approved 2"})
);
Patch('[dbo].[Journey]', BaseRecordsTable, ChangeRecordsTable)

Profiling the update showed that Patch worked in the same way as UpdateIf. It calls SQL Server for each record that requires updating, and therefore provides no performance benefit over UpdateIf.


What about RemoveIf?

If you want to delete records with RemoveIf, it sadly behaves in the same way as UpdateIf. Just like the UpdateIf function, it makes a single call to SQL Server for each record that you want to delete. It's also not delegable, so it may not delete all the records that you want to delete.

Conclusion

Updating and deleting SQL Server records can be slow and inefficient. Until delegation support extends to the UpdateIf/RemoveIf functions, there isn't a simple workaround.

If you need to update or delete a large number of records in bulk, I recommend that you create a SQL Server Stored Procedure that carries out this task. You can then call this Stored Procedure from Flow, and initiate the Flow from your PowerApp. 

Previous
Next
Related posts