Patching large CDS entities

[Anonymous]

Hello guys,

I am working on a canvas app and I'm running into performance issues when app tries to patch CDS entity.

What this part of app does: Basically you select one or several users from user pool and then go to next screen to select items that will be assigned to them. I collect users to 'ContactsCollection' and items to 'ItemsCollection' and a relationship between them is a row in this entity, let's call it 'My Cds Entity'. Here's a part of a code I'd like to improve:

ForAll(
    ItemsCollection,
    ForAll(
        ContactsCollection,
        If(
            CountRows(
                Filter(
                    'My Cds Entity',
                    contact_lookup.contactid = Contact.contactid,
                    item_lookup.itemid = Item.mcw_legalentityid
                )
            ) = 0,
            Patch(
                'My Cds Entity',
                Defaults('My Cds Entity'),
                {                    
                    contactemail: Contact.emailaddress1,
                    contact_lookup: Contact,
                    item_lookup: Item,
                    rel_id: Contact.id & Item.id
                }
            )
        )
    )
);

So basically it loops through both collections and searches in 'My Cds Entity' if it already has a connection and if it doesn't creates a new row in CDS. Problem is that 'My Cds Entity' has a lot of rows already, I don't know how to know(count) exact number of rows in CDS entity, but it should be well over 8000 in test environment alone.

Obviously this process takes quite a lot of time with that many records, for example in test environment for 2 users to assign 10 items each it takes about 6 minutes.

So my question would be is it possible to improve this and how? I heard you could get your datasource('My Cds Entity') into a collection and work on it locally, but would it help my situation if it will have rowscounts well into tens of thousands? And how long would it patch entire collection to CDS then? Maybe you could do it with a flow somehow? I've been working on PowerApps for a few weeks so don't even know if it's possible to improve on this, any suggestions are appreciated. 🙂

[v-xida-msft]

Hi @Anonymous ,

Based on the formula that you provided, I think you have faced a Delegation warning issue, is it true?

Currently, the CountRows function is not delegable within PowerApps, in other words, it could only process at most 2000 records locally.

I have made a test on my side, please consider modify your formula as below:

ForAll(
    ItemsCollection,
    ForAll(
        ContactsCollection,
        If(
            Sum(
                ForAll(Filter('My Cds Entity', contact_lookup.contactid = Contact.contactid, item_lookup.itemid = Item.mcw_legalentityid), 1),
                Value
            ) = 0,
            Collect(                                   /* <-- Use Collect function to replace Patch function */
                'My Cds Entity',
                {                    
                    contactemail: Contact.emailaddress1,
                    contact_lookup: Contact,
                    item_lookup: Item,
                    rel_id: Contact.id & Item.id
                }
            )
        )
    )
);

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

[Anonymous]

@v-xida-msft  thanks for your reply ,

I did have delegation error but it was on this part:

contact_lookup.contactid = Contact.contactid

And it still exists, but I think I'll deal with it later. What I'm more concerned about is performance(unless you think those two are related). Couple questions about using Collect instead of Patch. I'll have to name all the fields in this scenario? Because I assume I can't use Defaults() like I did with Patch. And other thing is, will it help performance using Collect?

[v-xida-msft]

Hi @Anonymous ,

Actually, there are many reasons that an app can be slow to load, slow to respond, and slow to write to data sources. Please check the following blog for more details:

https://baizini-it.com/blog/index.php/2017/09/13/powerapps-improving-app-performance/

More details about Perdormance Tips in canvas app, please check the following article:

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

Note: The Collect function would be executed faster than Patch function. You can quickly measure your app’s performance by turning on Developer Tools in Microsoft Edge or Google Chrome while running the app.

Best regards,

[Anonymous · Accepted solution]

Yeah, so if anyone ever wonders what I actually did:

I collected my entity to local collection and use collection instead of Cds entity for checkups. Since entity has many rows it loads on app starting for few seconds, but it's fine. Everything else I left the same and performance difference is huge