Question>>> Unstored Calc... Performance.. and FMSP

[Richard Carlton]

Dear Mr Carlton

 

I am a fan and one of the first people who supported and purchased both the FMGO13 and FM Starting Point Videos.

 

 

I am a developer since FileMaker 7, located in Dubai since 1995.

 

 

I've watched both Video series. But there are some crucial things relating to performance that are still not clear to me.

 

 

FM_Starting_Point: There are many Unstored calculations in the contacts table. Many of them can be changed to Stored calcs by using the Evaluate( your calculation ; z_Load_Globals_Trigger)

 

z_Load_Globals_Trigger is a field that is set to 1 to update all calculations.

 

I read a documentation that says we need to minimize all unstored calculations as much as possible. Whereas in your FM_Starting there are so many Unstorred calcs? Doesn't this affect performance ?

 

If this method is not required or advised can you please explain to me why?

 

 

Also you did mention this in your video. Yet you are not using it everywhere ? Is there a reason?

 

Video: #1012 Portal Summary Sum Totals

Description

Sum Calcs can be slow... for WAN connections.. thats why we don't use them in FMSP

 

You didn't use this method in 

table INV_Invoices

field Subtotal_Price

code Sum(T12j_invoices_LINE_ITEM||id_invoice|::ExtendedPrice)

 

Subtotal_Price is an Unstored calc... we can make it a Number Field or a stored calculation.

 

(there are many other fields that can use this method too....)

 

Another way

We could also upgrade this by adding to the relation graph

T12j_invoices_LINE_ITEM||id_invoice|

constant1 = constant1

 

In browse mode update constant1 by setting it to 1. This will force the portal to update the calculation (hence we don't need it to be an Unstored calc)

 

What are your personal thoughts.. is this wrong what I am saying, are there potential problems that I am not aware of ?

 

Please do help... your professional input is important.

 

Best regards,

HK

 

Hi Hassan,

 

Unstored calculations only put load on FIleMaker when they are displayed.  If they are NOT displayed… then it is not an issue.

 

For a large database solution (many gigs)… Or a solution where the clients has slow connections…in general… it is good to not have Calc fields at all…. But number fields or text fields that can be updated… only when needed by script.

 

FMSP will work fine given its structure… because the unstored calcs are only displayed on a limited basis… and only when needed.

 

However… to maximize performance on a SLOW connection…from the server… you could change these to text and number fields… and then only update when needed.

 

FMSP is designed to scale up… to a large size… but further optimization can be done by totally removing ALL the calcs, etc.   On the flip side of “total optimization” is the need for new people to understand how FMSP works…how to customize it…and how to build FIleMaker solutions.

 

So we do use some calcs… depending upon the need.  If we see an obvious bottle neck… like a Portal Total… then those are definitely wired up to be driven by a script.

 

----

 

On the Invoice Data Entry Screen...Line items… typically there is never a problem using a Sum Calc field there… as you pointed out we used. There are almost always a very limited number of items. But Technically yes, we could change that to a number field and a script update.

 

Portals become issues when there are a lot of related records… and the user has set the portal to sort.   That’s bad.  Or if there are lots of records to be summarized.

 

On the Invoice Line Items… there are almost always only a few items… and the users are frequently looking at the bottom of the invoice for the latest data… meaning that it always has to be up to date.

 

It is more complex to setup… but a trigger that updates the totals… only when the lines items change… would be technically faster.   Just more complex for new people to understand.

 

I have to balance perfect optimization of speed... for end-user understanding.

 

Thanks,

 

Richard