Poor performance on sum calculations and summaries

[BigMike]

I just set up FileMaker Server and am accessing a hosted database over the internet. I am getting very bad performance when accessed a layout that contains summary fields or sum calculations. Is there any way to improve the speed when accessing these types of layouts?

[JesseSFR]

Unfortunately, this is one of FileMaker's Achilles heels. It's very difficult to transmit all the data needed for those calculations over to your client so that you can calculate it. To minimize the times the calcs and summaries take make sure that everything that they depend on is indexed (if possible). You might also need to spend some time optimizing those calcs to make sure you are doing them as efficiently as possible. There is no bullet proof answer for that problem that I know of.

[BigMike]

Wow. That's a bummer. I just spent a LONG time creating a custom database that's got a lot of top level summaries to show costs and revenue across a lot of jobs.

How can you optimize the calculations?

[mr_vodka]

If a lot of that data does not change that often, you can use a server side process that can set a static field with the summaries. It should run pretty well.

[Søren Dyhr]

when accessed a layout that contains summary fields or sum calculations

No layout except subsummary layouts should carry any summary fields at all. It's rubbed off metaphor from spreadsheets I'm afraid, which there isn't catered for in this tool. Just throw in a few dependent calc's in a linked manner and your solution gets slow as molases.

It's not the tools achilles heel, but instead the user attempting to squeeze down a foreign metaphor from outside of the database realm. Similar to making french fries with at tennis racket ... which might succeed for a while, it's only a mater of scale - but no-one here are in doubt what the tennis rackets realm is, or?

Try to think what we did before spreadsheets arrived to our notion. Book keeping was done by hand in ledgers, where the "worth" of each account was the new transaction + the previous sum. This was done in ink and what today would resemble a stored value. This mean that each transaction record not only the new value bu also the sum from the transaction above. Now the ink is very important here, any attempt to cook the books is avoided since the ink is pretty tough to get rid of, in a non revealing way.

The entire metaphor can be copied to databases as well, but must then include prevention of deletion of previous records.

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000333

--sd

[Vaughan]

Part of the problem is that the database is hosted on a remote server out there on the internet somewhere. The bandwidth will be limited compared to a LAN at Ethernet speeds.

[Steven H. Blackwell]

Be sure that the cache on the Server is set to the maximum amount. On the FMP client set the amount of RAM reserved for cache to 12 MB from the default 8 MB. See if that helps. The othere answers in the this thread are good ideas as well.

Steven