fmp speed performance with too many calculation

[kyle]

hi, i have a inventory file with order lineitem, order and shipping lineitem , shipping files. now , i have about 70 unstored calculations in the inventory file which sums up the lineitem qty fields ( 12 each record ). So it is something like 12 recd sum qty fields + 12 shipped qty fields + 12 on hand qty fields, etc.... . and it ends up with around 70 of them.

The problem is in the inventory list view, it takes about 3 seconds to list 13 records. So i worry, what if the records grows up to thousands. will it show the sand time capsule and take about 10 or 20 minutes or more :

i read live-oaks post regarding " too many calculation --- bug down fmp".

and i wonder that re-look up scheme can work on this problem , too or not. and if i have to make a self relationship, should i need to make a loop script which setting the trigger key field on every records whenever i go to list view to update the each look up fields :

or any other ideas another thing is i must see the list view with all those sum up fields which shows the kind of work flow ( how many recd + how many shipped & what is the balance, etc.... ).

any input will be appriciated ...

kyle

[kennedy]

First recommendation: test it before you worry about it. Write a quick script to generate 1000 records. Then play with your layouts. Is it a problem?

Then, if it is, first consider your layout design. Is there some reason users really need to look at all those fields for all 1000 records? Most users can't really absorb that much... figure out what the user really needs to do, and then construct a variety of layouts that filter the number of rows and/or present the columns in a series (first the ones to help them find the right things, then ones that give all the details). Your users will thank you, independent of the speed benefits.

Then, if you still have speed issues, look at the calculations and make sure they aren't more complicated than necessary.

Then, look at how to prevent the remaining calculations that are slowing things down... which gets you to doing lookups and displaying those instead of the calculated fields.

But don't spend hours complicating stuff that would have worked acceptably.

Brian

[kyle]

hi, brian ! actually it is beyond just worrying.

i'm facing it as real problem which will definitely happen in the near future.

(1) yes. all those calc fields show the history of the certain's record's work flow. so it will be helpful for the user.

(2) the calculation is simple ones. calc1=sum(A file's related qty), calc2= sum(B File's related qty) and calc1-calc2.

(3) Yes, i thought about doing what you said about solving this problem with filtering by do the find first so i can minimize the display of the rows. since it requires more work and if user's find criteria results finding all records, i will still have the same problems. so i'm leaving it as my last option.

but as a seperate issue , i like to learn all new method for approaching to a certain problems.

so my question is should i use loop script to trigger the look up key field which used in self relationship every time i go to list view :

TIA ...

Kyle

[kennedy]

If you recalc everytime you go to the view, you'll have a long delay everytime you go to the view. Is that okay?

How often does the underlying data change? Does it matter that the view is perfectly up-to-date everytime?

BTW, I wouldn't call a sum over related records a simple calc. And 1000 rows of sums doesn't sound like the best UI, even ignoring the speed issue. If you're even just scanning, and you can digest the meaning of a sum once per second, it'll stick take the user over 30 minutes to digest all those sums. Call me Mr. Skeptical.

[kyle]

>> How often does the underlying data change?

( not that often.)

>> Does it matter that the view is perfectly up-to-date everytime?

( what will be the meaning of showing records not

updated ? )

>> If you recalc everytime you go to the view, you'll have a long delay everytime you go to the view.

( that is why i'm trying to use look up method.

live oak said if i don't display those calc_sum

fields on the list view and display self related

look up fields for those calc fields instead, it

will not cause slow showing records on the list

view. so calculation will occur whenever it's

related records changes or created or etc... .

but since i don't display that on the list view

, it will not recalculate. but it will calculate it

as individually internally.

so my question is (1) how exactly should i set

this look up method ? and

will it definitely solve my

problems : is triggering

look up key field whenever

i go to list view by looping

script way to go :

and what will be the speed

difference between calc

fields on the list v look up

fields on the list

TIA...

Kyle

[kennedy]

If the data doesn't change too often, less often than you look at the data, then you're better off NOT triggering the lookups every time you go to the view. Rather, you can try to trigger off changes to the data. Or you can use a manual button press. Or you can run a batch process overnight that does the lookups.

Or a combination of all three... each is just invoking the same recalc, so it doesn't hurt to have all three methods possible.

You might want to add an additional global that tracks the date the last recalc was done and display that so that your users know they are looking at the data as of last night. If they have an issue with that, they can click on that field and you can have that be the recalc button. That way, they choose if they want to wait, or use the latest recalc.

As for "how", for a basic example you can look at the starter template I posted in the Sample Files forum. Look at the Template.fp5 file... look at the "LOG" relationship and the fields that start with "Log" or "LOG". The purpose is different... in this case to maintain a full audit trail... but the technique is the same.

As for the speed difference, it will be HUGE given your calcs are actually sums over other files. If you are only displaying looked up fields, then its the same as only displaying data stored in the database... you can't get any faster. Now the recalc itself, whenever that gets invoked, won't be any faster. But if that's only done overnight, it shouldn't matter too much.

HTH.

[Vaughan]

"hi, i have a inventory file with order lineitem, order and shipping lineitem , shipping files. now , i have about 70 unstored calculations in the inventory file which sums up the lineitem qty fields ( 12 each record ). So it is something like 12 recd sum qty fields + 12 shipped qty fields + 12 on hand qty fields, etc.... . and it ends up with around 70 of them."

--

Are your line items in a related file, or have you duplicated all the fields 12 times and kept it flat? If you've kept it flat... then this is your problem. It should be related!

If it was related you won't be limited to 12 line items each invoice, printing will be easier, getting reports out will be easier...

[kyle]

hi, kennedy ! thanks, i will try it.

and for vaughan, i didn't make it flat. that wasn't the problem. anyway , thanks for the reply.

regards,

kyle