cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Patron
Post Patron

SQL Data Spikes

Hello,

 

For the past few weeks I have been having recurring issues where a user tries to update multiple records and often fails doing so. This has happend quite a few times within the last week.

 

I have three tables that get updated within SQL: Inspections, Records, Items. This issue always happens to my Inspections table, which messes up the associated View. 

 

Here's the code that I use when a user updates a record:

Concurrent(
    Patch(
        '[dbo].[Inspections]',
        First(
            Filter(
                '[dbo].[Inspections]',
                Id = Value(InspectIdHome.Text)
            )
        ),
        {
            Base: DropBaseInspHome.Selected.Value,
            Unit: DropUnitInspHome.Selected.Value,
            TailNumber: DropTailNumInspHome.Selected.Value,
            Location: DropLocInspHome.Selected.Value,
            NextInspect: DateValue(LblNextInspect_2.Text),
            LastInspectionDate: DatePickMaintenance_2.SelectedDate,
            Status: DropStatusInspHome.Selected.Value
        }
    ),
    Patch(
        '[dbo].[Records]',
        Defaults('[dbo].[Records]'),
        {
            alseSn:  SerialInspHome.Text,
            Base: DropBaseInspHome.Selected.Value,
            Description: TxtBoxDescription_2.Text,
            Inspector: DropInspectorInspHome1.Selected.Value,
            Inspector2: DropInspectorInspHome2.Selected.Value,
            Location: DropLocInspHome.Selected.Value,
            maintAccomplishDate: DatePickMaintenance_2.SelectedDate,
            Type: "Inspect",
            Status: DropStatusInspHome.Selected.Value,
            TailNumber: DropTailNumInspHome.Selected.Value
        }
    ),
    Patch(
        '[dbo].[ Items]',
        First(
            Filter(
                '[dbo].[alseItems]',
                Id = Value(ItemIdHome.Text)
            )
        ),
        {
            Base: DropBaseInspHome.Selected.Value,
            Unit: DropUnitInspHome.Selected.Value,
            location: DropLocInspHome.Selected.Value,
            tailNumber: DropTailNumInspHome.Selected.Value,
            status: DropStatusInspHome.Selected.Value
        }
    )
);
Navigate(
    Success,
    ScreenTransition.Cover
)

It's been very difficult for me to recreate this error. Upon reviewing Azure, I did notice several data spikes where my utilization went up to nearly 100%:

DataUsage.PNG

 

Once a user saves a record, it will make a call to Refresh the database connection as follows:

 

Refresh('[dbo].[Inspections]')

What could cause this issue? I have at most, 3 users on a 100mb network connection. I know PowerApps can be a resource hog, but it shouldn't be this difficult. Thank you.

 

Brendon

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: SQL Data Spikes

Hi @BrendonBrooksP1 ,

Could you please share more details about the error message with your formula?

How do you collect the data entered via the users in your app? Using Edit form control or just a set of simple controls?

Have you taken a try to remove the Concurrent() function and perform your formula again?

 

If you collect the data entered via the users in your app using a set of simple controls (e.g. Dropdown, Text Input box, etc) rather than Edit form, please modify your formula as below:

 

Patch(
        '[dbo].[Inspections]',
        LookUp('[dbo].[Inspections]', Id = Value(InspectIdHome.Text)),
        {
            Base: DropBaseInspHome.Selected.Value,
            Unit: DropUnitInspHome.Selected.Value,
            TailNumber: DropTailNumInspHome.Selected.Value,
            Location: DropLocInspHome.Selected.Value,
            NextInspect: DateValue(LblNextInspect_2.Text),
            LastInspectionDate: DatePickMaintenance_2.SelectedDate,
            Status: DropStatusInspHome.Selected.Value
        }
);
Patch(
        '[dbo].[Records]',
        Defaults('[dbo].[Records]'),
        {
            alseSn:  SerialInspHome.Text,
            Base: DropBaseInspHome.Selected.Value,
            Description: TxtBoxDescription_2.Text,
            Inspector: DropInspectorInspHome1.Selected.Value,
            Inspector2: DropInspectorInspHome2.Selected.Value,
            Location: DropLocInspHome.Selected.Value,
            maintAccomplishDate: DatePickMaintenance_2.SelectedDate,
            Type: "Inspect",
            Status: DropStatusInspHome.Selected.Value,
            TailNumber: DropTailNumInspHome.Selected.Value
        }
);
Patch(
        '[dbo].[ Items]',
        LookUp('[dbo].[alseItems]',Id = Value(ItemIdHome.Text)),
        {
            Base: DropBaseInspHome.Selected.Value,
            Unit: DropUnitInspHome.Selected.Value,
            location: DropLocInspHome.Selected.Value,
            tailNumber: DropTailNumInspHome.Selected.Value,
            status: DropStatusInspHome.Selected.Value
        }
);
Navigate(
    Success,
    ScreenTransition.Cover
)

please take a try with above formula, then check if the issue is solved.

 

If you collect the data entered via the users in your app using Edit form, please consider modify your formula as below:

Set(EntryData, EditForm1.Updates);   /* <-- Store the filled Forms data into a variable, then reference corresponding values from this variable within your Patch function*/
Patch( '[dbo].[Inspections]', LookUp('[dbo].[Inspections]', Id = Value(InspectIdHome.Text)), { Base: EntryData.Base, Unit: EntryData.Unit, TailNumber: EntryData.TailNumber, ... } ); Patch( '[dbo].[Records]', Defaults('[dbo].[Records]'), { alseSn: EntryData.alesSn, Base: EntryData.Base, Description: EntryData.Description, Inspector: EntryData.Inspector, ... } ); Patch( '[dbo].[ Items]', LookUp('[dbo].[alseItems]',Id = Value(ItemIdHome.Text)), { Base: EntryData.Base, Unit: EntryData.Unit, location: EntryData.location, tailNumber: EntryData.tailNumber, status: EntryData.status } ); Navigate( Success, ScreenTransition.Cover )

Note: If your three Patch functions reference values from multiple Edit forms, please consider take a try to save these multiple form data into separated variables (e.g. EntryData, EntryData1, EntryData2, ...). Then within your Patch formula, reference values from proper variable.

 

Best regards,

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Highlighted
Super User
Super User

Re: SQL Data Spikes

@BrendonBrooksP1 

I'm not entirely sure if your post is a question on the problem you are having with the formula you posted or if it is the spikes.

I'm addressing more of the situation that you are having in regard to your posted formula.  

You mentioned "...update multiple records and often fails doing so." - can you go into more detail on this?  What fails and what happens when it fails?

 

So, in looking at your formula, I do notice a couple things that you might want to consider.

1) Your first patch statement to Inspections - you might want to do a Lookup('[dbo].[Inspections]', Id=Value(InspectIdHome.Text) rather than the Filter and First statement.  This will return essentially the same results, but with less formula. (This goes for your 3rd Patch formula as well)

2) In regard to that last formula in 1...I always see a red-flag when casting an ID from text.  Probably all good...but, if you have some other place that you have the ID as the true ID, I'd use that for your filter.  (This goes for your 3rd Patch formula as well)

3) You should consider checking for errors.  Since you have dependencies on all of these tables - if you were to do true transactions - you would check errors and rollback if there were issues.  But, short of that, at least check errors between patch statements.

4) Also in regard to 3, you end with a navigate to a Success screen, but again, no actual check for any error conditions.

 

Hopefully this will provide some fuel for thought, and at the least you can put some checking in the formula and perhaps see where the issues are coming from.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Post Patron
Post Patron

Re: SQL Data Spikes

You mentioned "...update multiple records and often fails doing so." - can you go into more detail on this?  What fails and what happens when it fails?

-The user selects a single item, and then performs and inspection. The user fills out data (location, description etc) and then clicks on a button that saves the record. What ends up happening is that the Inspections table never receives the update. The other two tables (Item and Records) have no problems and are updated. 

 

I'm not entirely sure if your post is a question on the problem you are having with the formula you posted or if it is the spikes.

I guess I'm having a problem with both? I'm trying to figure out if they're related or not. 

 

1) Your first patch statement to Inspections - you might want to do a Lookup('[dbo].[Inspections]', Id=Value(InspectIdHome.Text) rather than the Filter and First statement.  This will return essentially the same results, but with less formula. (This goes for your 3rd Patch formula as well)

-I suppose I could use the LookUp function if that speeds things or simply makes the code easier to read. I'll give that a shot this week.

 

2) In regard to that last formula in 1...I always see a red-flag when casting an ID from text.  Probably all good...but, if you have some other place that you have the ID as the true ID, I'd use that for your filter.  (This goes for your 3rd Patch formula as well)

-My current formula makes a call to get the ID of a record, and then uses that as a reference when I Patch. Again, I probably could get away with just using a LookUp function, or by using the Serial Number of the item instead of the ID.

 

3) You should consider checking for errors.  Since you have dependencies on all of these tables - if you were to do true transactions - you would check errors and rollback if there were issues.  But, short of that, at least check errors between patch statements.

Error checking has the be bane of my existence with PowerApps. I'm still fairly confused on how to incorporate them into any of my formuals. 

 

Thanks for the feedback

-Brendon

 

Highlighted
Community Support
Community Support

Re: SQL Data Spikes

Hi @BrendonBrooksP1 ,

Could you please share more details about the error message with your formula?

How do you collect the data entered via the users in your app? Using Edit form control or just a set of simple controls?

Have you taken a try to remove the Concurrent() function and perform your formula again?

 

If you collect the data entered via the users in your app using a set of simple controls (e.g. Dropdown, Text Input box, etc) rather than Edit form, please modify your formula as below:

 

Patch(
        '[dbo].[Inspections]',
        LookUp('[dbo].[Inspections]', Id = Value(InspectIdHome.Text)),
        {
            Base: DropBaseInspHome.Selected.Value,
            Unit: DropUnitInspHome.Selected.Value,
            TailNumber: DropTailNumInspHome.Selected.Value,
            Location: DropLocInspHome.Selected.Value,
            NextInspect: DateValue(LblNextInspect_2.Text),
            LastInspectionDate: DatePickMaintenance_2.SelectedDate,
            Status: DropStatusInspHome.Selected.Value
        }
);
Patch(
        '[dbo].[Records]',
        Defaults('[dbo].[Records]'),
        {
            alseSn:  SerialInspHome.Text,
            Base: DropBaseInspHome.Selected.Value,
            Description: TxtBoxDescription_2.Text,
            Inspector: DropInspectorInspHome1.Selected.Value,
            Inspector2: DropInspectorInspHome2.Selected.Value,
            Location: DropLocInspHome.Selected.Value,
            maintAccomplishDate: DatePickMaintenance_2.SelectedDate,
            Type: "Inspect",
            Status: DropStatusInspHome.Selected.Value,
            TailNumber: DropTailNumInspHome.Selected.Value
        }
);
Patch(
        '[dbo].[ Items]',
        LookUp('[dbo].[alseItems]',Id = Value(ItemIdHome.Text)),
        {
            Base: DropBaseInspHome.Selected.Value,
            Unit: DropUnitInspHome.Selected.Value,
            location: DropLocInspHome.Selected.Value,
            tailNumber: DropTailNumInspHome.Selected.Value,
            status: DropStatusInspHome.Selected.Value
        }
);
Navigate(
    Success,
    ScreenTransition.Cover
)

please take a try with above formula, then check if the issue is solved.

 

If you collect the data entered via the users in your app using Edit form, please consider modify your formula as below:

Set(EntryData, EditForm1.Updates);   /* <-- Store the filled Forms data into a variable, then reference corresponding values from this variable within your Patch function*/
Patch( '[dbo].[Inspections]', LookUp('[dbo].[Inspections]', Id = Value(InspectIdHome.Text)), { Base: EntryData.Base, Unit: EntryData.Unit, TailNumber: EntryData.TailNumber, ... } ); Patch( '[dbo].[Records]', Defaults('[dbo].[Records]'), { alseSn: EntryData.alesSn, Base: EntryData.Base, Description: EntryData.Description, Inspector: EntryData.Inspector, ... } ); Patch( '[dbo].[ Items]', LookUp('[dbo].[alseItems]',Id = Value(ItemIdHome.Text)), { Base: EntryData.Base, Unit: EntryData.Unit, location: EntryData.location, tailNumber: EntryData.tailNumber, status: EntryData.status } ); Navigate( Success, ScreenTransition.Cover )

Note: If your three Patch functions reference values from multiple Edit forms, please consider take a try to save these multiple form data into separated variables (e.g. EntryData, EntryData1, EntryData2, ...). Then within your Patch formula, reference values from proper variable.

 

Best regards,

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Super User
Super User

Re: SQL Data Spikes

@BrendonBrooksP1 

I would focus on that one patch that doesn't seem to work all the time. Perhaps that would be a good place to look at the Errors function to check if there is an issue before proceeding to the next patch.

Basically you can do an IsEmpty(Errors('[dbo].[Inspections]') right after that patch and see if there is an error.  Then proceed as appropriate. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Post Patron
Post Patron

Re: SQL Data Spikes

I removed the Concurrent() command and everything works. Should we not use Concurrent() to Patch multiple tables at once? I had thought that the command would speed up the patching proccess, but apparently it just causes more problems than they're worth. Thanks again @RandyHayes and @v-xida-msft . I appreciate your help. 

 

Brendon

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (9,100)