Comparison between layout load times and performance

[Stu412]

Hi there

I am experimenting on a sample of data in a very simple FM database.  I need some help on 'how' FM does things, rather than how to do something myself (although you may scream at my technique below!)

I have two customers on Customer_Table with CustomerID and Name as the fields.  The only other table is called Data and is joined to Customer via CustomerID_FK.

It's a one to many relationship as each customer can have many data rows, around 400 each.  Therefore I have around 800 records in the Data table.  I have added three text fields to the data table  which enable true/false conditions to be set.

I use many CASE statements on the Data table to select various values from records depending on the combination of true/false conditions.  I then summarise these on a second field to get the grand total per true/false combination.

An example of this would be:

     CustID    Text1  Text2          Text3  Amount   CaseStatement1   CaseStatement2

     10           North  Confirmed  Bob      11000     11000

     10           North  Possible     Bob      5000                                     5000   

     If I want a grand total of Bob's confirmed and possible sales in the north, I'd do:

     Case ( Text1 = "North" and Text2 = "Confirmed" and Text3 = "Bob" ; Amount)

     Case ( Text1 = "North" and Text2 = "Possible" and Text3 = "Bob" ; Amount)

I then create summary fields for CaseStatement1 and CaseStatement2 to get the specific values I want.    As I say, given the details in the data I have and the requirements of the task, there are many similar calculations through my test DB.

The next part of my test was to see how I can easily move between customer records to view which sales have happened, ie, view the summary fields.  I set up two layouts, one based on Data and one based on Customer.  Given the data I have and the volume of summaries, I noticed it was significantly quicker moving on the Customer layout between records 1 and 2 than using the Data layout (which of course has 800 records).  

What I don't understand is that surely FM still needs to calculate the Case and Summary statements while moving between just the two customer records regardless, so why is it so much quicker one way versus the other?

Thanks in advance