Super Slow Summary and Calculation Reports

[AudreyRae]

Hello,

I'm looking for ways to speed up our generation and printing of financial reports/layouts with many summary and calculation fields (accounts receivable, sales reports, etc.) in a FileMaker solution that is no longer supported by the developer. These reports often take 30 minutes or more to generate and print. All suggestions, tips and tricks welcome!

Thank You!

Audrey

[BobWeaver]

Depends on how much access you have to the files. You could:

- get a faster computer

- delete some obsolete records

- save compressed copies of the files

- redesign some of the scripts and/or layouts that use unstored calculations or summary fields.

And if you are using a version of Filemaker older than 5.0 you should upgrade.

[LiveOak]

The largest single factor is how you are accessing the files. If you are in a client/server configuration over a network, some operations will be 30-60 times slower than single user operation with the files on the hard drive of the host machine. The speed difference in this case is determined by the relative speeds of the network vs. the disk drive interface.

It is very common to design a FM solution in a local, single user configuration with complex scripts taking less than a second. When the solution is moved to FM Server and the script run from one of the clients, the time balloons to over a minute or more. FileMaker is also sensitive to the speed of the hard drive. Significant speed improvements can be made by using a RAM Disk as the location of FM files, either in a local or server configuration. A UPS should be in place if this is attempted to protect from power outages.

So:

1) Run the reports in local/single user mode (this may be inconvenient)

2) Setup a RAM Disk for the FM files (works locally or using FM Server).

3) Perform appropriate redesigns to improve speed. (specifics not possible with seeing your files)

-bd

[Anatoli]

The largest single factor is how you are accessing the files. If you are in a client/server configuration over a network, some operations will be 30-60 times slower than single user operation with the files on the hard drive of the host machine. The speed difference in this case is determined by the relative speeds of the network vs. the disk drive interface.

Couple of days/weeks ago someone was surprised in some thread, that exact opposite is happening. This above applies to Macs, but IMHO not to FM Windows or FM Linux server.

Explanation is very simple and was also posted there.

[LaRetta]

Another major slowdown with reports with summary fields is the script-step sequencing and could apply in your case, particularly since you mentioned printing (and switching) to several reports. It's surprising how many solutions contain inefficient scripts, even by more advanced Developers. I heard of one db running the type of process you describe. When the script steps were resequenced, instead of taking 3 hours only took 7 minutes!

The following is an excerpt from CobaltSky that I had gleaned somewhere a while back. It might be helpful.

If it happens that there are any summary fields showing on the layout that the script may be starting from, then calling the 'Show All Records' step before leaving the layout will force a delay while all the summary fields are recalculated, before the layout can be exited.

On the other hand, if the layout you are going *to* has summary data on it, then the summary fields will have to calculate as soon as the layout is displayed. Therefore if you go to the layout *before* calling 'Show all', the summary data will have to be calculated twice for no reason - once for the found set as you enter the layout and again for all records after the 'show all' step.

Thus the decision as to which way around to put the show all and go to layout steps could as much as double the time the script takes to run.

In fact it is possible that both the layout you are coming from *and* the layout you are going to will have summary data to be calculated, in which case the solution would be to precede both steps with a 'Freeze Window' step - which will obviate screen redraws and thus remove the necessity for the summary data to recalc while the script is in progress. This step would be redundant if there were no summary data. And it would also be redundant if there were summary data on only one of the layouts, since you could get the desired effect by simply putting the other steps into the optimal order.

I've found it invaluable; and a reminder to think about the steps, sequence and logic when scripting.

LaRetta