Summary Field Latency

[kcep]

We process songwriter royalties. It is not uncommon for us to be working with 100,000+ records. The summary field on our royalty report layout is extremely important. This tells the bottom line … (what the payout is). We frequently access this layout. The actual royalty checks are also printed through FileMaker (using the amount of the summary field). We only have one summary field on our royalty report layout. Today there are 138,150 records that I’m working with. We’ve been working with this database for about 13 years. The latency we experience with the one summary field on our royalty report layout is really aggravating. I am currently working on the machine that hosts the database (an eight core Mac Pro). Just for kicks today, I exported the amount line item field from all 138,150 records as an Excel export. When I opened up the Excel file and created a simple sum function at the bottom of the column, Excel calculated the total of all 138,150 cells in a split second. FileMaker takes about 20 seconds … over the internet (through FileMaker Server), it would probably take a day. So, obviously I must have created a flawed solution some 13 years ago. Or, FileMaker just cannot add as well as Excel. Someone please tell me what I did wrong and how I can fix it. ;-)

Currently using FileMaker Pro 13

[rwoods]

Good morning from the UK!

Many performance issues are caused by the use of unstored calculations. (this may or may not apply to you!)

Have a look at the field that your summary field is totalling. If that field is of type 'Calculation', and FileMaker tells you that it is 'Unstored' when you view it in the 'Manage Database' dialogue, then that may be the cause of the slowness.

FileMaker calculation fields are very convenient, but can be slow if the calculation is using fields from a related table, or an SQL statement. In that case, FileMaker has to recalculate that field every time your solution needs it, and the summary field would need it for every one of your 138K records to perform the summary, hence it is slow.

To resolve this you should try to have that field be stored, and generally you do this by changing it to a number (instead of a calculation). You then manage the contents of this field by updating it whenever it's value may have changed. You have to look at the buttons and scripts in your solution and spot wherever that field may need to be updated.

Another technique is using the 'Auto Enter Calculated Result' (AEC) option. Change the field to a 'Number' instead of a 'calculation' and put the same calculation as you currently have in your 'Calculation' field into the 'Auto Enter Calculated result' box. You then have to manage how FileMaker keeps that field up to date, since it won't do it automatically when the Auto Enter Calculation looks across relationships. FileMaker will only update an AEC when one of the fields that it references THAT IS IN THE SAME TABLE AS THAT AEC FIELD is changed. If that is your preferred option the you need to use a Let statement in the calculation that references a new 'Trigger' field in your table, and 'touch' that trigger whenever the value needs to change.

If that is your preferred option let me know and I will explain more! It's a little bit complicated.

[kcep]

Hi rwoods,

Thanks for the reply!

The field we are having a latency problem with is a “Summary” type field not “Calculation” (see the three attached screen shots).

I’m not familiar with how to make a “Calculation” field or “Number” field create a summary total … It sounds like your approach would be to eliminate this one problem Summary field (Writer_sIncomeEarningsTotal), and have a Calculation field or Number field accomplish the same summary total. If that cuts the latency issue way back, I’m all for it. :-)

Also, other things of interest …

I’ve only been talking about the latency problem on the Writer royalty report layout. Records are also created simultaneously in other tables …

* Publisher Royalties

* Commissions

* Other Recipients (heirs, ex-spouses)

Here is the bigger picture …

Example:

- Table 1 (The main royalty input table): If we receive a $1.00 royalty payment, a single record is created in Table 1 for $1.00

- Table 2 (Writer Royalties) gets a portion of that $1.00 by creating a related record(s). Many times there are several songwriters on a single song. So, a record is created in Table 2 for each songwriter. This splits the writer portion again and again.

- Table 3 (Publisher Royalties) also gets a portion of the original $1.00 by creating a related record(s). Again, many times there are several publishers on a single song. So, a record is created in Table 3 for each publisher. This splits the publisher portion again and again

- Table 4 (Commissions) - someone who is set up to receive a commission receives a portion of the original $1.00 by creating a related record.

- Table 5 (Other Recipients) - These also receive a portion of the original $1.00 by creating related record(s).

All of these naturally need to balance to the fraction of a cent.

I say all of that to say … Once I get the solution in place for the Writer Table, I’ll need to duplicate the solution into the other tables.

Tables 2, 3, 4 & 5 create related records (via script) when a royalty amount is entered into Table 1.

So, because of latency (20 seconds yesterday / today about 90 seconds) for the Writer Table layout to load, I also need to wait when I access each of the other tables too.

The Total Summary of Table 1 needs to equal the combined Summary totals of Tables 2, 3, 4 & 5.

Most of the records are created very rapidly … sometimes by the thousands within several minutes (via electronic input). However, at times we have to manually input/manipulate/adjust/delete records. This opens up the possibility of human error causing the system to not balance. We have a script in place which gathers the summary amounts of Tables 2, 3, 4 & 5 and compares that combined total to the summary of Table 1 … This “Balance Script” is sometimes run several times a day because the longer we wait to find a human error, the harder it is to find. Running the “Balance Script” basically shuts things down for several minutes. We tried hosting our database on a remote server, however because of this latency problem, we are still on a local network.

THANKS!

 

screen_shot_2.tiff

screen_shot_1.tiff

screen_shot_3.tiff

[kcep]

8 hours ago, rwoods said:

Many performance issues are caused by the use of unstored calculations. (this may or may not apply to you!)

Have a look at the field that your summary field is totalling. If that field is of type 'Calculation', and FileMaker tells you that it is 'Unstored' when you view it in the 'Manage Database' dialogue, then that may be the cause of the slowness.

Ok ... I think I'm starting to understand a little better. However, I can't figure out why I'm not able to change the "Unstored" field. See attached screen shot.ScreenShot4.tiff

I'm trying to investigate the other fields that this Calculation field is referencing. It's like pulling a stray thread on a sweater ... Things start to unravel all over the place. ;-)

Edited December 5, 2017 by kcep

[Lee Smith]

Hi kcep,

This topic explains it better than I could answer.

https://fmforums.com/topic/49163-calculation-field-vs-calculated-value-vs-lookup/

Lee

[kcep]

Thanks rwoods!

Thanks Lee!