Summary speed

[detwolfe]

Yup, another newbie!

I'm running a solution for my police department containing over 30,000 records. The solution has roughly 20 tables and is used for everything from keeping arrest records and traffic ticket information to reports and general complaint information.

My problem is this; I have a layout which has around 50 summary fiels which keep a running summary of statistical information. When going to this layout, it takes a rather long time to summarize and dispaly the results. It wasn't bad when there weren't so many records, but it's taking more and more time as the number of records increase.

Is there a more streamline way of using summary fields or are calculation fields quicker or can anyone suggest anything that might speed this process up?

The method I am using is to get a statistic for accidents for example; there is a field which specifies what type of case is being recorded (case type). In this example it's an accident. This field is a standard text field with a drop down list to enable the user to pick from a list what type of case. I then have a calculation field that is not on the layout and simply does a pattern count for "accident" (count accidents). Next I have a summary field which keeps a running summary of the calculation field counting the "accidents" (accident stats). This summary field is one of the 50 on the statistics layout.

We are using FM8 clients on a FM8Server and all machines running XP.

Any suggestions would be appreciated!

[stanley]

Detwolfe:

Because summary fields are recalculated so often, you're going to see performance deteriorate as the record set grows. One way to get around this is to use calculation fields when viewing in Browse mode, or to script the recalculation process so that you have faster access to your data.

I ran into this kind of problem a while back with a client who had a system which showed all of their financial data summarized on one layout, with income, payments, etc. in portals and totals for each shown as summary fields. You could sit there for twenty minutes waiting for the summary fields to calculate, as there was four years worth of financial data in the system. I removed the summary fields from the layout and replaced each one with a global number field. Next to each of these, I put a button to 'calculate total'. These buttons were scripted to calculate the totals for each field individually, and the calculations were much faster than the summary fields.

This way, the client could see whichever bit of data they wanted, without having to wait for everything to calculate. There was also a 'calculate all' button, if they wanted to see all of the info.

Hope that helps.

-Stanley