Patch multiple records with one server call instead of multiple

[Meneghino]

As the result of co-operation with @BitLord69, we experimented with patching multiple records using the following syntax:

Patch(Data source, Table of record primary IDs, Table of change records)

Where the second and third parameter have the same number of rows.
 
Well, performance is awful.  It seems that in any case PowerApps is still patching one record at a time, since the gallery showing 'Data source' was flickering and updating as more and more records were updated.
 
Suggest updating the connector to patch all the records in one server call, performance would be much improved.

[BrendonBrooksP1]

I can't stress this improvement enough. My users have enough time to go to the bathroom before the application has finished patching data to my SQL database.

[mr-dang]

Agreed. I've gotten temporarily locked from the GoogleAPI for Google Sheets because too many calls were made at a time when accessing a datasource multiple times for CRUD operations. 

[AnthonyPhan]

Agreed, I sent weeks building an app that users refuse to use due to the "slow peformance" they are experiencing. 

[CWesener]

Updating status and adding @GregLi

[Meneghino]

Actually I have thought ot a work-around, but have not tested it.  Basically, instead of using Collect to insert your data into SQL using the built-in functions (Collect or Patch, which both work poorly with multiple rows) then use Flow to parse JSON data instead, either in Flow itself or with a stored procedure in SQL Server.
 
For example:
1) In PowerApps, use the concatenation functions to create a JSON string with your multiple records to insert (hold that thought for later)
2) In SQL Server create a stored procedure to parse the JSON string to insert records into your DB table using the OPENJSON function
3) In Flow accept a string input from PowerApps and pass it as a parameter to the stored procedure called from SQL Database
4) In PowerApps, add the Flow and supply the string from step 1 as a parameter

5) In PowerApps, refresh the data source to reflect the changes in the data

You can also return the result of the stored procedure by defining OUTPUT parameters in SQL, return those to Flow and from Flow to PowerApps via the Return Response action.  See here for this last point.
 
Any feedback from anyone who is willing to try would be great.

[AnthonyPhan]

@Meneghino I can confirm that your idea does work. I have implemented a similar method but using "Exercute SQL query" instead of using a stored procedure. With this said, it turns out you can actually patch multiple records using the patch funcation. I was able to patch a few hundred rows in less than 10 seconds. The way you do it is to save the record returned by "Defaults(SQLTable)" and then make copies of that record to construce a table. after that you can simple patch the entire new table using PAtch(SQLTable, defaultsConstructedCollection) 

[sai1]

Hello, I'm trying to patch my collection to SQL table. creating the records in SQL table from the collection. Collection - Collection1 SQL Table - dbo.Powerapp I tried the below formula but its not working patch(Data source, Table of record primary IDs, Table of change records) I have primary key and exact same column names in the SQL database. I don't know what am i missing. Any help on this is appreciated.