cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WillPage
Solution Sage
Solution Sage

SQL Performance while patching.

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.

3 REPLIES 3
cwebb365
Super User
Super User

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.

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
Community Support
Community Support

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:

4171.PNG

 

(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,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (3,929)