SQL Performance while patching.

[WillPage]

I'm looking for some best practice guidance here.

I have a SQL database that contains a couple of tables. Table 1 contains some data, like a staging area that needs to be reviewed by an operator then patched into Table 2. The PK columns have the same names but other columns don't.

What I'm trying to do in PowerApps is take data from Table 1 and patch it into the second table. I want the user to be able to select a bunch of records from a gallery and commit them to Table 2 in bulk after reviewing them.

So, the gallery (Items = Table 1) OnSelect builds a collection and then outside the gallery I have a save button that patches the collection into Table 2 in a ForAll.

This works OK but it's very slow when more than about 10 records are selected. Furthermore, within that ForAll I mark the records in Table 1 as committed so I'm patching a boolean column in Table 1 with {Committed:true} as part of the same process. This causes it to be even slower, especially as there's a LookUp involved too.

Here's the full code:

ForAll(
    colTempSelectedRecords,
    Patch(
        '[dbo].[PriceBook]',
        Defaults('[dbo].[PriceBook]'),
        {
            'Supplier Code': localSupplierCode,
            ManufactureMarking: localManufactureMarking,
            PB_Brand: localBrand,
            PB_UnitDescription: localUnitDescription,
            PB_StockGroupCode: localStockGroupCode,
            PB_DiscountGroupCode: localDiscountGroupCode,
            PB_ListPrice: localListPrice,
            PB_StandardCostPrice: localStandardCostPrice,
            PB_SupplierDescription: localSupplierDescription,
            PB_Uploaded: localUploaded
        }
    );
    Patch(
        '[dbo].[TempPriceBook]',
        LookUp(
            '[dbo].[TempPriceBook]',
            'Supplier Code' = localSupplierCode && ManufactureMarking = localManufactureMarking && UploadedAsInt = localUploadedAsInt
        ),
        {Committed: true}
    )
)

I understand it's possible to patch a table directly, but I couldn't find any definitive guidance on whether that's actually possible with SQL. I tried experimenting with the column names in the local collection to get them to match Table 2, but Patch complains about having a table as an input.

Would a stored procedure in SQL be the go for this, which I can trigger in the app via a Flow, or is there a more efficient way of bulk patching records? I don't necessarily mind if it takes a long time to put the data into Table 2, but at least marking the records in Table 1 as committed should be fast so I can grey out the screen or disable the commit button for a shorter time.

[cwebb365]

Im a bit rusty with my sql datasource but I’m pretty sure my app doesn’t take too long to add record. It’s def. not lighting fast but not bad. I think because your doing two writes and a read for each record it might be slowing you down. Also in that local collection is the total count there 10 or is there more it’s going through and just not finding matches for?

One alternative I would look to do might be using a collect to the sql table instead of patching if it’s a 1 to 1 match.

Also for the committed true part look into updateif() function and change committed to true for selected items? Unless I’m not understanding the logic behind that.

[WillPage]

If I replicate the functionality in a flow, by passing the collection in as JSON, parsing it then adding rows and updating rows within an apply to each, 15 records takes about 5 seconds (vs 3 or 4 minutes). I think what it might be is because there's a gallery on the screen showing a view that's based on a join of the two tables with an except in there, and that gallery is refreshing/reloading every time the ForAll is marking a bit column as true in table 1.

Anyway, I just fling it off to a Flow and it's acceptable even if I'm going SQL Table -> View -> PowerApps -> Collection -> Flow -> Table -> View -> PowerApps, so I don't need to write a stored procedure and send a list of the PK values to make SQL do the work.

[v-yutliu-msft]

Hi @WillPage ,

Actually, the performance of an app may be affected by many reasons.

If you want to trigger a flow to update sql, you could build a flow like this:

(instant flow, trigger: powerapps)

Then you could trigger the flow to update sql in powerapps.

However, this will not help you improve your app's performance a lot.

I suggest you improve your app to improve its performance.

Here are some tips about improving app for your reference:

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

https://powerapps.microsoft.com/en-us/blog/performance-considerations-with-powerapps/

Best regards,