Jump to content
TJ53

Unstored calculation fields and performance

Recommended Posts

Let’s say we have two related tables: “Invoice” and “Invoice_Item”. We could create a calculation field in the “Invoice” table called “total_amount” with this formula:

total_amount = Sum (Invoice_Item::amount)

This field would have a negative impact in performance when appearing in the layout, since it would have to be defined as unstored, because it’s referencing a field from a related table.

Now let’s suppose this field is not used for any scripts, tooltips, conditional format, etc … would the performance of the database be negatively affected ONLY when this field appeared in a layout? 

In other words, would adding an unstored calculation field to a table involve a performance penalty, even in the “unreal” case where this field didn’t appear in any layout, script, conditional format, etc.? thanks in advance!

Share this post


Link to post
Share on other sites

Unstored calculation are evaluated as needed. If the field is not shown on the layout, and not referenced in any way, it will not be evaluated.

  • Like 1

Share this post


Link to post
Share on other sites
8 minutes ago, comment said:

Unstored calculation are evaluated as needed. If the field is not shown on the layout, and not referenced in any way, it will not be evaluated.

Thank you! now let's say we have a list layout where the unstored calculation field "total_amount_uc" is showing for each invoice. Therefore, it would have to be evaluated for each invoice record, and therefore there would be a performance penalty.

Now let's say that in order to improve performance, we create a number field called "total_amount_script". Then we create a script called "Update invoice total amount", that executes when an related invoice item amount is entered or modified (or an invoice item is deleted).

Would there be a difference in performance of setting the "total_amount_script" in these two different ways in the script created for updating the number field?

Option 1) Set Field [ Invoice::total_amount_script ; Sum(Invoice_Item::amount) ]

Option 2) Set Field [ Invoice::total_amount_script ; Invoice::total_amount_uc ]

Thanks in advance!

 

 

Share this post


Link to post
Share on other sites
38 minutes ago, TJ53 said:

Would there be a difference in performance of setting the "total_amount_script" in these two different ways

I don't think so - but all performance questions are best answered by performing an actual test. Sometimes the results can be surprising.

Performance aside, if you're going to use a script to populate a stored field with the aggregate value, then you don't need the unstored calculation field - and there is no good reason for the script to depend on its existence.

Note that there may be additional factors to consider here, for example record locking.

 

 

 

 

  • Like 1

Share this post


Link to post
Share on other sites

Thank you very comment for you insight. The main reason why I would consider going for option 2 (as long as there are no performance implications), even having to define an extra field, is because the unstored calculation field definition would be more easily accessible in case I needed to adjust the calculation.

Otherwise I would have to go to the script workspace and search for the “Update invoice total amount” subscript in order to make a change in the calculation.

On the other hand, I see going for option 2 looks a bit "strange" compared to how standard programming languages work, since in this case the logic would be part of the interface. Also could be confusing to have both fields in the table, and the unstored calculation field not showing in any layout, but only used in the update field script.

It looks like the best practice is to go for option 1, and that’s how I’ve done it in the past. But I have to say that lately I’m tempted to go for option 2, just for having the calculation more quickly accessible in case I need to review!

Edited by TJ53

Share this post


Link to post
Share on other sites

One could turn your argument around: suppose you are editing the script, and you want to make a change to the Set Field[] step; you would have to leave the script workspace and look for the calculation field in order to make the change there.

I believe that if an action is scripted, it's good practice to place all the necessary logic within the script. And if you divide your scripts into logical parts and take care to comment each part, you should not have to look very hard in order to find what you're looking for.

  • Like 2

Share this post


Link to post
Share on other sites

+1 for comment's comments.

As for a field definition being more easily accessible. Do you have any awareness of the risks of doing that?

I suspect not; or you would not have made the statement. Though maybe you just don't have anybody using this system.

What happens when you're defining a field while users are entering data? What can happen to the integrity of the file?

You want users out of the system when doing this. Thus your timeslot for accessibility is extremely limited.

  • Like 1

Share this post


Link to post
Share on other sites

Thank you very much both. I assumed best practice was option 1, and that's how I've been working so far, but was just wondering about option 2.

20 minutes ago, BruceR said:

+1 for comment's comments.

As for a field definition being more easily accessible. Do you have any awareness of the risks of doing that?

I suspect not; or you would not have made the statement. Though maybe you just don't have anybody using this system.

What happens when you're defining a field while users are entering data? What can happen to the integrity of the file?

You want users out of the system when doing this. Thus your timeslot for accessibility is extremely limited.

Yes, I am aware. That's why I rarely do development in live production systems, specially when doing scripting. In any case that's very valid point, thank you!

Share this post


Link to post
Share on other sites

Performance issues aside (and I try to avoid unstored calcs if I can, I prefer my logic in scripts), there is another factor to consider.  If part of the business logic changes and you are using unstored calcs then by changing the calc you risk changing all the historic records.  Which can be a huge problem if you don't think through that ahead of time.

  • Like 1

Share this post


Link to post
Share on other sites

Thank you very much for the responses. 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • By Richard Carlton
      FileMaker Coaches' Corner - Tip 11 - Improve Performance - FileMaker - FileMaker Experts
      https://youtu.be/5D2qoA_S7u8
      Get up to speed with the FileMaker Pro 17 Video Training Course! 
      Top Rated Course by FileMaker Expert, Richard Carlton.  
      http://learningfilemaker.com/fmpro16.php
      Experience Richard's dynamic and exciting teaching format, while learning both basic, intermediate, and advanced FileMaker development skills. With 27 years of FileMaker experience and a long time speaker at FileMaker's Developer Conference ,Richard will teach you all the ins and outs of building FileMaker Solutions.  The course is 50 hours of video content!
      Richard has been involved with the FileMaker platform since 1990 and has grown RCC into one of the largest top tier FileMaker consultancies worldwide. 
      Richard works closely with RCC's staff: a team of 28 FileMaker 
      developers and supporting web designers. He has offices in California, Nevada, and Texas.

      Richard has been a frequent speaker at the FileMaker Developers Conference on a variety of topics involving 
      FileMaker for Startups and Entrepreneurs, and client server integration.

      Richard is the Product Manager for FM Starting Point, the popular and most downloaded free FileMaker CRM Starter Solution.

      Richard won 2015 Excellence Award from FileMaker Inc (Apple Inc) for outstanding video and product creation, leading to business development.

      RCC and LearningFileMaker.com are headquartered in Santa Clara, CA.

      http://www.rcconsulting.com/
      Please feel free to contact us at support@rcconsulting.com
      If you want to explore building I.O.S apps for I Phone or I Pad and deploying those out to the Apple App Store.
       
      Here is a video introduction to our iOS App Training https://www.youtube.com/watch?v=cVxQe_yAshw
      Looking for FM Starting Point free software download: http://www.fmstartingpoint.com
      For More Free FileMaker Videos Check out Http://www.filemakerfree.com
      Visit http://www.learningfilemaker.com for all facets of FileMaker Award Winning Video Training.
      Please Visit Our Channel: https://www.youtube.com/user/FileMakerVideos Please Subscribe While There.

      Please Comment, Like & Share All of Our Videos.

      Feel Free to Embed any of Our Videos on Your Blog or Website.
      Follow Us on Your Favorite Social Media
      https://www.facebook.com/FileMakerVideos
      https://twitter.com/filemakervideos
      https://plus.google.com/+FileMakerVideos/videos

      Filemaker Pro 17 Training Videos
      FileMaker 17 Videos
      Filemaker Pro 17 Video Course
      #FileMakerVideos
      #FileMakerTrainingVideos
      #WhatisFilemaker17
      #FilemakerPro17Training
      #Filemaker17VideoTutorial
      #FilemakerPro17Videos
      #FileMakerCoaches'CornerTip11
    • By MSPJ
      So I have a hosted solution, with a dashboard with a couple portals. When I go to scroll a portal, scrolling even one portal screen has a delay of a few seconds, ie I click the scroll bar or try to drag it, and it takes about 3 seconds before it moves. Running locally, it's instantaneous. Running on a dev server on my LAN, there's a slight delay, but minimal.
      Having read various threads about this situation, my first thought was it's due to a filtered portal, so I removed filtering, but that made no difference.
      I also read various posts about unstored calcs and other factors that would cause all the data to have to be transferred to the client over the WAN.
      But here's the thing - at the moment, there are about 20 records in the database, related to the particular portal. 
      Clearly, moving that data can't be the primary issue - right? 
      I do have a lot of Execute SQL calculations in related tables, and I've read that can cause poor performance. But wouldn't that only be a factor when there are a lot of records?  
      Are there design /schema choices that would cause significant lag regardless of the amount of data? 
      Thanks,
      Michael
    • By lan
      Environment:
      FMS 15 Windows Server 2012 host on Azure Plug in: 360Works ScriptMaster Purpose for using the Plug in: zip the pdf files. Problems We have:
      There are a large amount temp files created on the client machine which run the fmp to connect the server , the temp files won't removed. What I am looking for:
      Any script step can avoid these temp files stay in temp folder. How these files created? I attached a screenshot.
       
      Thanks

       
    • By TJ53
      Is there any difference in terms of performance between a calculation field (stored and indexed) and field defined as auto-enter calculated value (indexed)?
      For example, we have an “INVOICE” table, with a field called “date_invoice_sent”, and we’d like to have a boolean field called “is_sent”.
      The calculation would be “not IsEmpty(date_invoice_sent)”
      So we have two options here:
      - Calculation field (stored, number result).
      - Number field defined as “auto-enter / calculated value / do not replace … unchecked”.
      Would there be any difference in performance between the two options? thanks in advance!
    • By JerrySalem
      I have a system being hosted using FMS14.  I also have a duplicate database hosted for testing/exporting using FMS15. PSOS is much slower in my hands with FMS15.
      Both servers are physical boxes, with similar (maybe identical) specs (Cores, RAM HD Space, HD Space available)
      I also have a Server Side script that creates a found set, exports data to excel and emails it to a user.  (This is based on https://www.skeletonkey.com/restoring-filemaker-clients-found-set-within-server-side-script/).  The exports have a couple of related fields, but no unstirred calculations.
      Using FMS14 this has been working just fine.  Using FMS15 it has been significantly slower.
      As a test, I exported 13,000 records from both FMS14 and FMS15.
      Using FMS14, it takes about 10 Seconds to recreate the found set, then 70 Seconds to create the export.  
      Using FMS15, it takes about 20 Seconds to recreate the (same) found set, then 175 Seconds to export the data.
      In another series of tests, this time exporting 30,000 records (more realistic in my scenario) I found;
      Using FMS14, it takes about 25 Seconds to recreate the found set, then 168 Seconds to create the export.  (2.5x longer to find/2x longer to export 3x records)
      Using FMS15, it takes about 90 Seconds to recreate the (same) found set, then 825 Seconds to export the data. 9x longer to find/11x longer to export 3x records)
      In the last series of tests, this time exporting 50,000 records I found;
      Using FMS14, it takes about 59 Seconds to recreate the found set, then 262 Seconds to create the export.  (6x longer to find/4x longer to export 5x records)
      Using FMS15, it takes about 160 Seconds to recreate the (same) found set, then 1700 Seconds to export the data.(16x to find/24x to export 5x records)
      Any ideas?  Anyone see similar results with PSOS on FMS15?  
      Again, the machines are identical, the databases are identical and the scripts are identical.  the only difference is FMS14 vs. FMS15.  I am also letting another user pull data using ODBC.  That has also gotten extremely slow using FMS15, but that is a discussion for another thread.  I am not using WebD on this server.  In general the FMS15 database performs find using FMP Clients.  But the FMS15 functions are not very impressive.  I am petrified to move my production database to FMS15, and even considering moving the test server back to FMS14.  Help!
       
      TIA
      Jerry
  • Who Viewed the Topic

    1 member has viewed this topic:
    hutchlad 
×

Important Information

By using this site, you agree to our Terms of Use.