Improve Power Apps to SQL Patch Performance

For my use case, I had to:
1. Load a SQL table/view to a collection
2. Record user updates in collection
3. Insert updated records to SQL

The challenge I faced was the slow performance of ForAll and Patch commands when used with SQL. This is the standard method outlined in Power Apps reference documentation, but I couldn’t get it to work in scale.

To illustrate this point, I created a sample Power App and connected it to [SalesLT].[SalesOrderDetail] table in AdventureWorks database.

Combining SQL with For Loop for Maximum Pain

ForAll / Patch Command

ForAll(
    c_sales_order_detail,
    Patch(
        '[PA].[SalesOrderDetail]',
        Defaults('[PA].[SalesOrderDetail]'),
        {
            SalesOrderID: SalesOrderID,
            SalesOrderDetailID: SalesOrderDetailID,
            OrderQty: OrderQty,
            ProductID: ProductID,
            UnitPrice: UnitPrice,
            UnitPriceDiscount: UnitPriceDiscount,
            LineTotal: LineTotal,
            ModifiedDate: Now()
        }
    )
);

ForAll combined with Patch took 4 minutes and 2 seconds to write 542 records! Try demoing this to a client on WebEx, with spotty conference room WiFi. Well hopefully after this post, you will never have to experience it yourself.

JSON to the rescue

Patch Command with JSON

Patch(
    '[PA].[SalesOrderDetailJson]',
    Defaults('[PA].[SalesOrderDetailJson]'),
    {  
        OutputJson: JSON(c_sales_order_detail)
    }
);

Using JSON took .652 seconds for a whopping increase of 37,000% in processing time. By converting the collection to JSON format, you can avoid ForAll loop and multiple iterations of Patch with a single write to SQL.

To bring this solution in a full circle, JSON functions in SQL will replicate the table/view structure:

SELECT	CAST(JSON_VALUE(js.value, '$.SalesOrderID') AS INT) AS SalesOrderID
		,CAST(JSON_VALUE(js.value, '$.SalesOrderDetailID') AS INT) AS SalesOrderDetailID
		,CAST(JSON_VALUE(js.value, '$.OrderQty') AS SMALLINT)	AS OrderQty
		,CAST(JSON_VALUE(js.value, '$.ProductID') AS INT) AS ProductID
		,CAST(JSON_VALUE(js.value, '$.UnitPrice') AS MONEY) AS UnitPrice
		,CAST(JSON_VALUE(js.value, '$.UnitPriceDiscount') AS MONEY) AS UnitPriceDiscount
		,CAST(JSON_VALUE(js.value, '$.LineTotal') AS NUMERIC(38, 6)) AS LineTotal
		,CAST(JSON_VALUE(js.value, '$.rowguid') AS uniqueidentifier) AS rowguid
		,CAST(JSON_VALUE(js.value, '$.ModifiedDate') AS DATETIME) AS ModifiedDate
FROM	[PA].[SalesOrderDetailJson] AS sod
CROSS APPLY OPENJSON (sod.OutputJson) AS js

I hope this helps you in your journey to integrate Power Apps with SQL. If you have other tips-and-tricks you would like to share, please leave a comment below!