Poor performance when patch whole collection to database

[AchatesChen]

In my case (a quiz app), every time I need to submit tens or even 100 records to MySQL server, however, either using ForAll or Collect(database, collection), system is working per each row, so the speed is very slow. I even tried Flow, it also needs about 2s for each record. Is there any way can bulk insert row to database?

[mdevaney]

@AchatesChen 

Yes, you can bulk update records in SQL using the PATCH function like this.  I'll provide a link to a tutorial article at the bottom of this post. 

Patch(your_SQL_table, your_updates_collection)

The update happens faster because multiple records are being simultaneously modified at-once.   Using a FORALL loop is a slower way to PATCH because the records get changed one-at-a-time.  However, you must follow a few rules:  Any column names in your_updates_collection must match the column names in your_SQL_table exactly.  If you don't include an ID number in your_updates_collection it will insert new rows.  If you include an ID number it will edit existing rows.

Link to article "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."

[AchatesChen]

not sure what is the difference but I will receive errors as below.

the first one is my database, the second is is the collection.

BTW, I am inserting, not updating. Any difference? I remember, in doc, when update, need to mention base records.

[mdevaney]

@AchatesChen 

Can you please show me how you built the collection Result_Head?

[AchatesChen]

See below:

[mdevaney]

@AchatesChen 
For whatever reason I have found that it is sometimes necessary to define the collection schema in the OnStart property of the app like this.  Make sure to Run App OnStart after inputting this code.

ClearCollect(Result_head,
    FirstN({
        id: 0,
        qzid: 0,
        email: "A",
        name: "A",
        start_time: Now(),
        end_time: Now(),
        total_point: 0,
        total_score: 0
    }),0
);
        

---
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."

[AchatesChen]

Sorry but it still doesn't work. 

it says that "the specific record was not found...". Looks like it is trying to update instead of insert.

Really frustrating!😫

[mdevaney]

@AchatesChen 
If you are trying to insert new records you must remove any references to an id column in Result_head and the schema.  That’s why PowerApps thinks you want to update.

---
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."

[AchatesChen]

Sorry for the late response.

I think because I am using MySQL, it will show error "Field 'id' is required.".

This is known issue that Power Apps doesn't support auto incremental id field of MySQL, while MSSQL or SP should be fine.

I guess no solution for me.

[adam-tobias]

Hi @mdevaney ,

Can this method work for all datasources? Not just for SQL? Can this be for SharePoint?

I actually read your blog post from your site before stumbling to this thread. Great approach. This is of interest to me as I notice slow performance with ForAll()+Patch() and looking for ways to optimize it.