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

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
Community Support Team
Community Support Team

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

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

 

Community Support Team
Community Support Team

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

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.

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,308)