Solved: Issues with Inserting/Patching Large Data Sets

bmmiller1989 · ‎02-10-2021

I am building an app that is taking data from an existing on-prem SQL datasource and performing manipulations/calculations on that data and then inserting it back into a different SQL table with new columns added that powerapps is responsible for creating.

I am looking for some help to get around the 8-10 min load times and latency issues when filtering and moving the data throughout the canvas app and specifically around patching to the datasource after the manipulations have been completed within the gallery.

There are over 150,000 records in the originating SQL DB. The main functions of the app I am creating is filtering through the data to find the following characteristics from Territory > Division > Unit Name. The unit name will give me specific details of what that unit has in terms of details but I have to filter down to get to that level.

Here are the current steps in place:

1. Screen that shows dropdown filters that allow to select a Territory > Divisions in that Territory > Unit Names inside that Division.

2. OnSelect button creates a collection of the data for that Unit Name and takes me to another screen full of galleries

3. There are 4 galleries that contains 10+ textboxes across where some of the data is being pulled from the Collection above and some of it is based on calculations from the Collection therefore creating a new column

4. The Save Button on this screen is currently taking the data from the Galleries, doing a ForAll > Patch into into a SQL table. The issue here is that it is needing to loop through each individual line of the gallery in order to patch it into SQL. These are all net new to the table (Defaults). Below is the current ForAll statement for one of my galleries. This statement DOES work but the issue is that it takes over 5 min to patch 30-40 rows of data. I need to find a more efficient way to get the data from these galleries into the final SQL datasource.

ForAll(
Gallery.AllItems,
Patch(SQL,
Defaults(SQL),
{
AppealName: 'Appeal Name',
AppealType: 'Appeal Type',
AppealYear: 'Appeal Year',
AvgGift: Value(AvgGift.Text),
EstimatedQTY: Value(NumberMailed.Text),

Status: "Pending",

+ 15 more columns in the same format
}
)
);

I have tried to build a collection from the gallery and then collect it into the SQL table, but the issue I am having here is that it is combining the initial collection from the button click in step 2 above with the new columns created inside the gallery on the page load and making the new columns in the collection into a group which prevents me from being able to run the collect statement due to it trying to insert a control (the grouped item) instead of a text value.

ClearCollect(colTest,Gallery.AllItems);
Collect(SQL, colTest)

Anonymous · ‎02-11-2021

@bmmiller1989

Let me have a go at explaining what I think is happening here:

1. Collection 1 created

2. Gallery 1 Items = Collection 1

3. TextInput fields (or they maybe labels?) are added to Gallery 1 for AvgGift and EstimatedQTY

4. Data is manually added to these input fields

5. Button click runs your code listed above

If this is correct, then the reason why AvgGift & EstimatedQTY are receiving row 4 data is because that's the last row selected before clicking your button ie Row 4 was the Gallery1.Selected item. If you were to add 4 rows of data THEN select, say, row 10 with your mouse then click the button, you'd find row 10 in your collection instead of row 4.

As I explained initially, any controls (labels, textinput, etc) that are added to a gallery that are not tied to the Items property of the gallery are SINGLE controls ... ie they are not adding new columns to your data they are just holding the data in these controls in memory (somewhere).

The [easiest] way to get around this issue is build the collection schema FIRST as a NEW COLLECTION, then use this as your Items property of Gallery 1.

So, in your diagram you'd insert a collection creation AFTER the creation of Collection 1 using your code above with these slight changes:

ClearCollect(
       colTest,
          AddColumns(
             ShowColumns(
               Collection_1,
               "DataSource",
               "Division",
               "CMS_Budging_ID"
             ),
         "AvgGift","",
         "EstimatedQTY",""
         )
);

Gallery 1 then uses this collection (colTest) as it's Items.

The textInputs controls, AvgGift and EstimatedQTY, in Gallery 1, can then be referenced in the DEFAULT property as ThisItem.AvgGift and ThisItem.EstimatedQTY.

Then for these textinputs, add this code to the OnChange property of each:

AvgGift

Patch(colTest, ThisItem, {AvgGift: txtAvgGift.Text})

** Note: I've called the input field txtAvgGift here. If it has the same name as the column heading your code will fail (same goes for txtEstimatedQTY below).

EstimatedQTY

Patch(colTest, ThisItem, {EstimatedQTY: txtEstimatedQTY.Text})

Now, when you add values to these fields in your gallery, colTest will get updated. You can then simply use @mdevaney final Patch statement to send your data to your source ... no need for the AddColumns(ShowColumns ... etc before you do.

Hopefully I've explained this well enough? Let me know if you need further assistance.

View solution in original post

Pstork1 · ‎02-10-2021

Are the new values being calculated by Power Apps or input from the UI? If they are being input from the UI there is very little you will be able to do about your times using Power Apps. But if they are automatically calculated you might want to look at using Power Automate instead. ForAll() isn't an actual Loop since Power Apps is declarative not procedural. But Power Automate has the ability to do an actual Loop on data. You can also use a combination of Power Apps and Power Automate. Power Apps to get the filter variables you need and then pass them to Power Automate for the actual processing.

-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

mdevaney · ‎02-10-2021

@bmmiller1989
You can dramatically increase performance if you PATCH the whole table at once instead of using FORALL + PATCH.

// create a table to batch uploads
ClearCollect(
    colUploadRecords,
    AddColumns(
        ShowColumns(
            Gallery.AllItems,
            "AppealName",
            "AppealType",
            "AppealYear",
        ),
        "AvgGift", Value(AvgGift.Text),
        "EstimatedQTY", Value(NumberMailed.Text),
        "Status", "Pending"
    )
);

// remove duplicate records
ClearCollect(
    colUniqueRecords,
    ForAll(
        Distinct(colUploadRecords, ThisRecord),
        ThisRecord.Result
    )
);


// upload the batch
Patch(
    SQL,
    colUniqueRecords
);

Here's a further explanation of the method:

Patch Multiple Records 10x Faster

https://matthewdevaney.com/patch-multiple-records-in-power-apps-10x-faster/

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

bmmiller1989 · ‎02-10-2021

@mdevaney

Thank you for the direction.

I built out the above but the issue is now the collection is only getting the first row of data and duplicating that same row vs. grabbing each unique row of the gallery. Any thoughts on that? This points me to a ForAll that I know we are trying to say away from.

mdevaney · ‎02-10-2021

@bmmiller1989
I have added some more code in my original post to remove the duplicates. You will need to PATCH colUniqueRecords now instead.

bmmiller1989 · ‎02-10-2021

@mdevaney Thank you!

The initial collection is still only bringing in the first row and not all the items in the gallery. Thoughts on that one?

mdevaney · ‎02-10-2021

@bmmiller1989
Perhaps the delegation limit is set to 1 in advanced settings?

bmmiller1989 · ‎02-10-2021

@mdevaney are you referring to the Data row limit for non-delegable queries? That is set to the max 2000.

mdevaney · ‎02-10-2021

@bmmiller1989
Yes, I am. I can't think of another reason why it would be only 1 row...

Anonymous · ‎02-10-2021

@bmmiller1989

Like @mdevaney , I'm unsure why his code fails for you. I've just tested it and, other than an errant comma after "AppealYear" it works fine for me.

You may need to check your Filters at Step 1 to make sure they are working as expected ie getting unique records.

To your original questions, I think that your use of 4 galleries is causing some confusion here. If I was wanting to build out this as a solution I'd keep these galleries for the UI/UX design then create a second collection at Step 2 specifically for Upload only.

So,

- your original collection can do what it does now

- at Step 2, create a 2nd collection (call it colUpload), this will initially be the same as the other collection and would only have the Upload columns needed ... use AddColumns/DropColumns/ShowColumns/RenameColumns as needed

- as you make changes across your galleries, update records in colUpload --> use Update/UpdateIf/Patch ??

- when finished, simply Patch( SQL, colUpload)

As a side note, when you add labels in galleries that are disconnected from the Items property (you mention a 'new column' but it's just a label) when you try to collect these into a collection with the other data they come across as tables, ie

But you've already discovered this yourself. That's why I'd recommend the above process where you update a collection (ie colUpload) as changes are made rather than relying on the included label working as you need it to, because it won't.