Slow layouts become faster after changing Auto-Enter calculation

[MoMo]

Hi all

My server is running on FMS 13.0v9 and my clients are running FMP 12.0v4. The mismatch is due to my clients still mostly being on OS X 10.6.8. I'm having a strange issue with slowness that I was hoping to get some insight on. Here goes:

I have a layout (named "Checkin Layout") that access a table and a few related tables. The main table has a field named "CheckedIn" which contains either a 1 or a 0. The user checks a checkbox, which enters the value "1". The auto-enter calculation is as follows:

Case (CheckedIn  = 1 ; 1 ; 0)

This makes it so that the value isn't ever empty, but will be either 0 or 1. Users have lately been complaining that toggling this checkbox is becoming incredibly slow. I initially tried to remove objects from the Checkin Layout, but found that even if the layout contained nothing but the field in question, toggling the checkbox was still incredibly slow. I then tried changing the auto-enter calculation, thinking that may help. I changed the "Case" function to "If" and noticed that the speed shot up immediately. However, I found that if i closed and reopened my layout, the slowness came back. After some more experimentation, I found that opening the "Manage -> Database" window and changing ANY definitions or calculations would make the Checkin Layout extremely fast, until it is closed. Then once it is opened it becomes slow again. 

I'm curious if there is anything happening when I adjust definitions that I could do via scripting. The slowness is taking its toll on productivity, and I can't be around to mess with definitions every time a user needs to check something in. Is there anything I can do??

Thanks in advance for your help.

MoMo

 

[schamblee]

Anyone can experience slowness based on a number of items.    Your calculation for CheckIn is not needed.  Auto-Enter calculations only calculate on a brand new record, unless you deselect the "do not replace existing value" . Deselecting this option can cause a delay.

I would setup the above field with no auto-enter, the default will be blank or 0.  (I recommend using type number)  Place the field on the layout as a check box with one value of 1, so If it is checked it is 1.  If is not checked then it becomes 0.   To verify placed the field on the layout a second time as a text box,, so you can view the value, select Boolean as the format from the inspector. You can select what is displayed, but the default is if non-zero is Yes and zero is No.  The default value with a new record will always be 0.   If you need it to be 1 when the record is created then check data in your auto-enter calculation then enter a 1.  

[Ron Cates]

35 minutes ago, schamblee said:

I would setup the above field with no auto-enter, the default will be blank or 0.

There's a big difference between blank and zero in FileMaker world.

[MoMo]

Thanks for the response schamblee, but the issue is not that auto-calculation. I've found that removing all of the auto-calculations does not itself speed up the layout. In fact, I went so far as to remove every object and field from the layout except for CheckedIn and it was still slow. Furthermore, as Ron Cates said, there is a significant difference between 0 and empty for my purposes, so I need the auto-enter calc to be there. 

 

The only thing that seems to speed it up is opening the field definitions and making an arbitrary tweak to just about any field definition. I'm wondering if there's any way to mimic that behavior via scripting, so i can speed up my layouts for users without going under the hood every time. 

 

 

[schamblee]

Non-needed calculations can slow down your layouts.   You don't need the case statement and as I stated in my above post if you deselect the "Do not replace existing value" then the calculation may be recalculated and can slow the layout.

Select data in the auto-enter calculation and set the value to 0.  I added a sample file to show how to you the checkin checkbox with only at 0 in the auto-enter calculation. 

 

My answer was based on your first post, and yes calculations can slow your layout.  Scripts, Script Triggers, Portals  plus other items can slow your layouts.  

New Feature in FM15 helps with the portal issue.

 

CalculationTestFile.fmp12

[LaRetta]

 

4 hours ago, schamblee said:

You don't need the case statement and as I stated in my above post if you deselect the "Do not replace existing value" then the calculation may be recalculated and can slow the layout.

The calculation will only reevaluate when a local referenced field (within it) is modified so it does not slow down the layout except when its value is modified and it should still be instant; it is after all, a stored value.

4 hours ago, schamblee said:

Select data in the auto-enter calculation and set the value to 0.  I added a sample file to show how to you the checkin checkbox with only at 0 in the auto-enter calculation. 

Your file breaks if the User selects 1.  You can tell by creating the checkin field on the layout without your data formatting applied and make it two rows high.  You will see that now both a 0 and a 1 are in the field.  I am sure you understand this and it was just an oversight.

Hi MoMo, welcome to FMForums!  

For the auto-enter calculation, try this:  

GetAsBoolean ( Self ) 

... and you must uncheck 'Do not replace existing value if any'.  This will insert 0 in new records but toggle from then on.

On 6/21/2016 at 9:32 AM, MoMo said:

Users have lately been complaining that toggling this checkbox is becoming incredibly slow.

Still, the auto-enter checkbox would not make the difference you are noticing unless it is used in the relational key to those 'other related tables' you mention.  Is this why you must have the 0 in the field ... so a relationship works?

As more related records are added, it can get slower.  The other issue, as was mentioned, is layout objects.  Some things to consider:

• How many objects are on the layout?  Can you put some in popover, slider or tab control?
• How many portals on the layout and how many rows showing?  Is the portal filtered?
• Are there aggregate calculations on the layout, such as Sum(), Count() etc?
• Are you using a lot of conditional formatting on objects?

A screen shot in layout mode and more detail about your relationships will help.  

 

I see you've removed all objects and it is still slow.  The last thing to consider is trying Recover.  

1. Select your file (when it is closed)
2. Select 'advanced options' 
3. Select ONLY 'copy blocks as is' and 'Rebuild field indexes now'

[LaRetta]

I had also wanted to ask if you have any calculations which reference this CheckedIn field.  Are there any other dependencies which act upon its value?

Edited June 23, 2016 by LaRetta
corrected CheckedOut to CheckedIn

[MoMo]

LaRetta,

Thanks for your responses and for your kind welcome!

There are indeed a few calculations that happens when the checkbox is toggles. The User, Date, and Box that an Item is checked into are all notated in their own calculation fields, then another calculation field is made that summarizes this information for user readability. Unfortunately, there may be other calculations that occur when this field is updated, but since I did not create the table and only maintain and update, I can't be certain without combing through hundreds of fields (I'm probably going to have to do this eventually!). There aren't relationships based off the field, but there are summaries (how many items are checked in within this collection or project), which is why I prefer to have 0 versus blank.

Regarding the objects on the layout: I do now believe that any conditional formatting or number of objects in the layout has any affect at all. The proof comes from the fact that I made a new layout that contained ONLY the checkbox and it was still slow. After I realized this, I stopped trying to prune the layout and started trying to fix the actually table design.

Regarding the Recover method: I have tried to make a compacted copy, and that actually took the size of the DB from 5.3 GB to 3.8 GB, but i didn't see any increase in performance.  Would the recover method do anything different? 

Sorry to continue to harp on this one point, but the weirdest thing about all of this is that the checkbox is fast if I open the Manage Database window and tweak and arbitrary field calculation definition. It's as is opening the Manage Database loads up all of the info the layout needs in order to be fast, but it then loses the performance boost if the layout is closed and re-opened. I really don't understand how or why this is happening, but am reluctant to chalk it up to voodoo or the phases of the moon. 

That being said, I'm going to try and check all of my calculation fields to see if they are relying on the CheckedIn field. I'll update this thread with any findings. Thanks!

Peace,

Morgan

Quick update to this issue: The CheckIn box being slow may be somewhat of a red herring. If I try to make any changes at all to a record then try to page to the next record it lags for at least 2-4 seconds. Is it possible some sort of commit is happening when changing records? Is there any way to fix this?

[Fitch]

Maybe you have a layout script trigger?

Does the checkbox have a script trigger?

Any ExecuteSQL calculations going on?