Speedy Calculations?

[silhougal]

Hi all,

I've been in the final stage of developing a database for a collector friend of mine, the files feature a summary page where all the statistic information can be found, ie totals for each category, historical cost and current value etc...

everything seemed to work very smoothly until now, i'm faced with a reality that with a set of 8000 records (potentially more), this summary page takes more than 10 minutes to generate the numbers. the majority of the fields (about 90) on these page are just calc fields with sum(relationship::field)

the computers which i used to test this were powerbook g4 400 with 384mb Ram, PC Pentium 3, 700Mhz with 256mb Ram.

Someone must have come accross this situation, at the moment i cant think of a better way to speed up this page which is the last thing that's holding me up... have i done something wrong or it's just one of Filemaker's bad features?

desperately look forward to any comments here...

cheers,

FileMaker Version: 6

Platform: Windows XP

[Ugo DI LUCA]

Typical problems.

All these unstored calcs are definitely slowing down your whole db, and when it comes to summarizing, it's just unworkable.

That's why scripting plain number fields instead of relying on the sole relationship model is what is usually advised.

SetField[total1,::Sum(:relationship1)] vs c_Total1 = Sum(:relationship1)

The big challenge is to planify scripting, but when the relational structure is done, I'd say you're 75% done.

FileMaker Version: 6

Platform: Mac OS 9

[Ender]

Hey silhougal,

It's good you are testing for speed.

Unstored calcs, like Sum(relationship::field) are slow because they can't be stored. If this is a print only report, try summarizing the data on a list view with summary parts and summary fields. Summary fields have to be calculated on the fly too, so I don't know if they would be faster.

If you don't need up-to-the-minute totals, you could run a script over night (or whenever) and store the results in regular number fields. I use this method for determining vacation days earned based on total hours worked in a time card database.

Other suggestions: Use a fast computer and a fast network. Reduce the number of fields you are summarizing on this page--break it down into several pages. Try to limit the number of records that are being summarized. If the databases are hosted, make sure they are on FMP Server, not client.

[silhougal]

hi again,

thank you Ugo Di Luca & Ender for the terrific fast responses, i think i am very curious with Ugo's approach but i'm just not sure how this works, i wonder if there's a sample file somewhere in here that uses this SetField[total1,::Sum(:relationship1)] vs c_Total1 = Sum(:relationship1)

further on this summary page of mine, it's kind of a flexible page where it displays the first 10 items from a value list at a time and by clicking a button, a script will update the page so that it shows the next 10 and so on, until there's no more item to show

each of these 10 items will have fields to show totals of records, etc as described earlier. and for this reason, i think it's quite important to have up-to-the-minute totals

could you explain further Ugo?

many thanks,

[Ugo DI LUCA]

Well,

Let me illustate somehow with a standard Business system.

[color:"red"] Settings relying on FM Relationships only

In the Product File

There would usually be 2 direct links.

ItemsSoldRel -->Product:ProductID::InvoicesLineItems:ProductID

ItemsPurchasedRel -->Product:ProductID::PurchaseLineItems:ProductID

And this is what you may currently have for a start :

cTotalSalesPerItem =Sum(:ItemsSoldRel:QtySold)

cTotalPurchasesPerItem = Sum(:ItemsPurchasedRel:QtyPurchase)

These are some standard unstored calculations.

In the Invoice File

Your standard relationship would be

ItemsInvoicedRel -->Invoice:Invoice#:InvoicesLineItems:Invoice#

and then 2 classic calcs

cTotalInvoice = Sum(:ItemsInvoicedRel:TotalLine)

cTotalDue = Payment - Sum(:ItemsInvoicedRel:TotalLine)

In the Contacts File

One standard relationship would be :

ContactsInvoicesRel -->Contact:ContactID::Invoice:ContactID

and the worse unstored calc ever :

cAmountDue = Sum(::ContactsInvoicesRel:cTotalDue)

The later is a Sum of several Unstored Sum !

What happens then :

Enter find mode, search for cAmountDue >0, and you'd find that you'd have finished your coffee before the search ends.

The same, even worse happens for Summary Reports involving these Unstored Calcs.

Basically, you should avoid any calculation accross records.

[color:"red"] Settings relying on FM Relationships and scripts

This obviously suppose you are comfortable with scripting.

What does this script do :

What this set of scripts would do is just set numeric values (in this case) to the related values, so that your unstored calcs are now totally indexed calcs.

In your Invoice File for example,

cTotalInvoice = Sum(::ItemsInvoicedRel:TotalLine) would be substituted by a numeric index field nTotalInvoice set by a script step which would look something like :

SetField[nTotalInvoice, Sum(::ItemsInvoicedRel:TotalLine)]

and

nTotalDue would now become a standard indexed calculation of Payment - nTotalInvoice

Of course, you can get rid of the unstored calcs.

Is it complex ? :

The big pain is that this script will necessarily involve external scripts.

So that when you'd run the Paid Invoice script for example, you'd also in fact :

- loop through the affiliated records in the line items and set a field nPaid to 1 (or Paid)

- go to the Customer File and reduce the nAmountDue from this invoice payment

- and any other step you'd need.

So yes it is rather complex, and efficiency would depend about how you did "programmed" these updates.

When to run these scripts :

When these scripts would be triggered depend on your solution, the frequency of entries, and the kind of data you're updating.

Staying in the Invoice file, if a lot of entries, from multiple users are made at same time, you'd consider an update any time a single entry is made into the Line Items.

This would make sure someone processing an order with these same products would be alerted if the quantity in stock was insufficient.

If not, then you can update at the end of the Invoice creation, and even rely on the "Allow Creation of Related Records". You may have an opening script that reset the accounts from the payments done yesterday, etc.

[color:"red"] About Statistics

This is just a side note.

If you need to generate Weekly/Monthly sales by products, then you may

add global fields for Week, Month and Years and use a concanation WMYProductID matching a WMYProductID in the Line Items.

If you need to keep track of these Monthly sales, then my suggestion (which is mine I insist) is to store these numbers in repeating fields. You would therefore be able to easily export the set for Historic statistics, or use them for your reports.

HTH

FileMaker Version: 6

Platform: Mac OS 9