Unanswered

SQL Update takes 3 minutes for 29 rows.

(1) Share

Report

Posted on by EK-09102106-0

Hi, Everyone!
I am updating MS SQL - roughly 35 rows, 9 columns each. Matching the rows on an ID. This takes about 3 minutes, which feels like an eternity. Also, the gallery refreshes 35 times, which adds to the delay. How can I update the code or the tell the gallery to stop refreshing?

I understand Matthew Devaney's patch is faster, but I can't figure out how to make it update the right row.

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

//Create collection to update AuditResults
ClearCollect(
    colUpdateAudit_Submit,
    ForAll(
        galleryResultsUpdate.AllItems,
        {
            ID: Value(ID_Update.Text),
            Result: Results_Update.Selected.Value,
            Severity: Severity_Update.Selected.Value,
            Comments: Comments_Update.Text,
            Pass_Fail_Points: Value(Pass_Fail_Points.Text),
            Severity_Value: Value(Severity_Value.Text),
            Weighted_Value: Value(Weighted_Value.Text),
            Updated_By: User().FullName,
            Updated_On: Now()
        }
    )
);
//Update AuditResults db
    ForAll(
             colUpdateAudit_Submit,
                Patch(AuditResults,
                    LookUp(AuditResults, ID = colUpdateAudit_Submit[@ID]),
                    {Result: colUpdateAudit_Submit[@Result],
                    Severity: colUpdateAudit_Submit[@Severity], 
                    Comments: colUpdateAudit_Submit[@Comments], 
                    Pass_Fail_Points: colUpdateAudit_Submit[@Pass_Fail_Points],
                    Severity_Value: colUpdateAudit_Submit[@Severity_Value],
                    Weighted_Value: colUpdateAudit_Submit[@Weighted_Value],
                    Updated_By:User().FullName, 
                    Updated_On:Now()
                    }
                    )
                );
 
Thanks so much!

Categories:

General Questions

All responses (2)

Answers (0)

EK-09102106-0 8 on at

Like (0)

Report

SQL Update takes 3 minutes for 29 rows.

Warren! I'm honored that you would help me. Thank you!

I tweaked it a bit to get the data types to be happy; this is what I implemented:

ForAll(

galleryResultsUpdate.AllItems As _Data,

Patch(

AuditResults,

LookUp(

AuditResults,

ID = _Data.ID

),

{

Result: _Data.Result_Update.Selected.Value

,Severity:_Data.Severity_Update.Selected.Value

,Comments: _Data.Comments_Update.Text

,Pass_Fail_Points: Value(_Data.Pass_Fail_Points.Text)

,Severity_Value: Value(_Data.Severity_Value.Text)

,Weighted_Value: Value(_Data.Weighted_Value.Text)

,Updated_By: User().FullName

,Updated_On: Now()

}

)

);

It still takes roughly the same amount of time, processing each row and refreshing the page. It will be disconcerting to the users because it flips the drop downs to the defaults before it puts them back when it is done processing. I will look into adding a "this row changed" flag so that I can only save the rows that changed.
I appreciate the alternate way to submit data to sql tables. Thank you!
WarrenBelz 143,532 on at

Like (0)

Report
SQL Update takes 3 minutes for 29 rows.

Hi EK-09102106-0

Firstly try this
ForAll( galleryResultsUpdate.AllItems As _Data, Patch( AuditResults, LookUp( AuditResults, ID = _Data.ID ), { Result: _Data.Result, Severity: _Data.Severity, Comments: _Data.Comments, Pass_Fail_Points: Data.Pass_Fail_Points, Severity_Value: _Data.Severity_Value, Weighted_Value: _Data.Weighted_Value, Updated_By: User().FullName, Updated_On: Now() } ) )

The article from Matt Devaney I believe relates to SharePoint as a data source - I am not sure this would work with SQL, (I do not use it with Power Apps), however it is worth a try
Patch( AuditResults, ForAll( galleryResultsUpdate.AllItems As _Data, { ID: _Data.ID, Result: _Data.Result, Severity: _Data.Severity, Comments: _Data.Comments, Pass_Fail_Points: Data.Pass_Fail_Points, Severity_Value: _Data.Severity_Value, Weighted_Value: _Data.Weighted_Value, Updated_By: User().FullName, Updated_On: Now() } ) )

Please click Does this answer your question if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it a Like.
MVP (Business Applications) Visit my blog Practical Power Apps Buy me a coffee