Speed of unstored calc versus stored calc on remote volume - test results

[truelifeajf]

Hi all,

 

In an attempt to speed up my FM12 solution which is hosted on a FileMaker server in a data center, I'm looking a the nitty gritty of how to speed things up for the users.

 

We've likely all heard that we shouldn't use unstored calculation fields as they "slow things down". But HOW does it slow things down?

 

It slows down the display of data on a layout, but it actually speeds up the creation and deletion of records.

 

I set up two test tables in a hosted FM12 file. Each table had 100 fields.

 

The first table, called "stored" had 100 calculation fields, pulling data from another field in the SAME table. Therefore, each field could be a STORED calculation field.

 

The second table, called "unstored" had 100 calculation fields, pulling data from ANOTHER table using a basic relationship. Therefore, each field had to be an UNSTORED calculation field.

 

I ran a script on each table to create 100 records, view 100 records (scroll through each record), and delete the 100 records.

 

I performed each test after quitting and starting FileMaker again to ensure caching wasn't speeding things up.

 

THE RESULTS ARE:

 

 

Create 100 records:

stored: 1 min, 40 seconds

unstored: 1 min, 5 seconds (35% faster)

 

View 100 records by looping through each record and refreshing the window:

view stored: 14 seconds

view unstored: 27 seconds (50% slower)

 

Delete 100 records by executing the "delete found records" script step:

deleting stored: 26 seconds

deleting unstored: 22 seconds (15% faster)

 

MY OPINION - Any thoughts on this?...

This means all those auto-enter fields I have to store identical data from unstored calculation fields are actually slowing down the creation of new records.

 

 

So I should remove them as my unstored calculation fields are mostly back-end fields, used by scripts as required, or displayed on the layout but under various tabs and therefore only displayed if the user clicks on the relevant tab.

 

Ah but.... I need them for relationships, and definitely for faster sorting / searching.

 

I don't know if anyone agrees but I cringe when people use my solution, click "new record" and have to wait 3 seconds.

 

Some options:

Find all stored calc / auto-enter calc fields that are only used by scripts. Remove them and adjust your scripts to reference the relevant unstored calc field.

 

Find all stored calc / auto-enter calc fields that are only used by relationships. Remove them and adjust your scripts to use ExceuteSQL commands.

 

 

Find all stored calc / auto-enter calc fields that will never be used for searching or sorting. Change them to unstored calcs, particularly if they're fields that aren't displayed often on screen.

 

The idea being that record creation is fast, and scripts are slightly slower. But most of the time, people are entering data, navigating records, and using only a few key scripts. Many of your scripts that will reference unstored calc fields will only be used ocassionally by users.

 

Am I missing something or the above a good idea?