Increasing Performance - auto update calc / unstored calc / other!

[Visionjcv]

Hi Everyone,

I have been working on a database solution for a large business for several months.

Everything is operating great but now that a new addition is required I have been faced with major performance issues and would greatly appreciate some advice.

This is part of a much larger solution - but I will merely mention what I feel is necessary for this issue.

I have the following tables:

Customers

Assets (machines)

Services

Cash Line Items

Asset Specific Data

My problem is as follows:

The Cash table has a cash record for every Asset in every Service for Every Customer. As a result, this table currently has over 150,000 records.

On a day to day basis, for processing and administration operations, there are no visible performance issues.

However, for each Cash record there is a field called 'Previous Level'. This is acquired via a self join by Date which pulls up the 'Final Level' from the previous record for that Asset, for that Customer.

Therefore the total Cash Collected is currently an unstored Calculation that essentially goes as follows:

'(Previous Level - Current Level) * Stock Value'

The problem is, if you consider a customer with 1 Asset, but 10 Service Visits for that asset, in order to calculate the 'Total Cash Collected' it needs to sum the individual Cash collected which in turn references the 'Previous Level' recursively.

Performing a total sales report by Date on this data set takes approximately 10 minutes - which is fine, but I now need it to take seconds.

I have been trying to store the cash collected in a number of ways and have spent several days reading up on the pros and cons of different methods.

However, In order to store the field I always end up looking at an Auto-Enter calculation with Do Not Replace Field Contents unchecked.

However, like alot of other people, and due to the way Filemaker operates (I recognise this isn't a limitation!) this value will not update in the 'Final Level' of a Previous Service is changed, thus the data goes out of sync.

I simply cannot find a way around this which won't sacrifice data integrity but will allow me to store a calculation field and increase performance.

I have tried methods like using a trigger field every time the record is modified, but that would involve looping through child records and performing this trigger setting every time - which comes across as not being a great idea??

Even though the trigger field is a Global Field, and so won't be subject to Record Locking issues, I still feel that this is not the best way to go about it.

I honestly don't know how to get around this issue, but performance is essential at this stage.

All advice would be greatly appreciated!

[comment]

Why don't you use a summary field for your report? The unstored calculation will not evaluate if it's not on the layout.

[Visionjcv]

Thanks for your reply.

I'm not entirely sure if I understand what you're suggesting.

On my reports as it stands, it is sorted by Serial Number and Standard Industry Code.

As a result, I am only viewing the subsummary parts which includes the SIC, Serial and a Summary Field which is 'Total Of' the Unstored Calc field.

The Unstored calc field itself doesn't actually show on the layout as it stands... Is this what you are suggesting?

[comment]

Perhaps I misunderstood the issue: are you saying that you have only the current cash level, and in order to calculate the received amount, you must subtract from the previous level - sort of like calculating trip mileage from odometer readings?

Perhaps you should attach a simple file to demonstrate the problem.

[Visionjcv]

Thanks again.

I will attach a file asap but I would need to recreate the issue altogether, which could take some time.

It is precisely like an odometer reading though.

The only values that are recorded (by a service engineer in the field on a PDA) is the Current Level, and the Final Level. The final level becomes the 'Previous Level' the next time an engineer attends the site.

The Cash Collected is the Previous Level - Current Level multiplied by the item value (eg: $2.00)

The fields are specified as follows:

[color:red]Current Level Input Field

[color:red]Previous Level GetNthRecord (Cash for Previous Cash Levels::Level; 1)

[color:red]Sold Products Let ( $soldItems = (Previous Level - Products Found);

If (

$soldItems ≥ 0;

$soldItems;

0)

)

[color:red]Total Sold Summary with Total of Sold Products

[Søren Dyhr]

Try to investigate this:

http://fmhelp.filemaker.com/fmphelp_10/en/html/func_ref3.33.17.html

...and stuff it in a autoenter calc instead of an unstored calc'field - since referencing referenced fields is going to make a solution slow as molases.

http://www.filemakerpros.com/LULAST.zip

--sd

[comment]

The only values that are recorded (by a service engineer in the field on a PDA) is the Current Level, and the Final Level.

What is the difference between these two? Is it the stock refill by the engineer?

[Visionjcv]

Essentially yes

Current Level is the level found on site when an engineer arrives for instance.

Final Level is the stock left on site when he leaves.

[comment]

Well, one way to handle this would be to lookup the previous level, and restrict editing of existing records to a scripted process. At the end, the script would find the affected records (same group, later date) and force a relookup.

Note that you need to provide for possibly locked records, if you go this route.

There may be another option, but I need to think about it for a while. BTW, I believe that even your current method could be a tad faster by changing the redundant:

GetNthRecord (Cash for Previous Cash Levels::Level; 1)

to simply:

Cash for Previous Cash Levels::Level

but I doubt it would make the difference you're looking for.

[Visionjcv]

Thanks for that improvement... I think originally it wasn't retrieving the first related record - hence the GetNthRecord calculation step, but yes, now it's most definitely pointless.

I had a feeling the solution would probably involve a method like this... To be fair, all input is done via one layout, so perhaps it wouldn't be all that bad to just perform a relookup on the later services...

Thanks a lot for having a think about this. I'll let you know how it goes. Cheers!

[Visionjcv]

Thanks for your reply.

This is something I had explored but ran into a wall that I simply couldn't get around without scripting methods.

Essentially this works. However, if the previous level changes (due to an admin error etc.) the next value will not update since it is referencing a related field via an auto-enter.

How could I get around this??

[Søren Dyhr]

if the previous level changes (due to an admin error etc.)

But if they have recieved any kind of tuition in book-keeping, is it obvious that err's like that needs a posting counter the wrong one and not changing any previous record, it has been so ever since Luca di Pacioli adabted what he learned the arab merchants did in the late 1290'ies ... nothing particular new about that except they back then made the book in ink on paper....

--sd

[comment]

Here's another thought, which may fit your business rules, or not:

You said the service person records the inventory level at the beginning and at the end of each service. The difference between these two is the quantity put in during the service. IIUC, this is roughly the quantity sold since the previous service - and over time, the discrepancy becomes negligible.

[Visionjcv]

That wouldn't really work...

The whole purpose of recording the Current Level and Final Level is to increase accountability and reduce theft.

Since the engineer visiting the site doesn't know how much was left by the previous engineer, it becomes very difficult to be able to keep some of the sales revenue without this being noticed and flagged.

But I do of course see your point!

[Visionjcv]

True - but try explaining that to a FTSE 100 company

They think they always know best! haha

[Søren Dyhr]

True - but try explaining that to a FTSE 100 company

It's what is called induction - to say if one of the companies in it have arrived in that class by cooking the books, would it be the rule for the rest! Neither can't tell if you've gotten anywhere by cooking the books or otherwise being or pretending ignorant?

The pacioli system needs a safeguard just as the demand to write in ink was originally ... here should a validation ensure that other records only are previous the attempted modified - if later records exists should the user be prevented to do so. It's not anything which needs to get explained, more than once at least - never to use a pencil in book keeping because it can be erased an fiddled with.

The use of spreadsheets might have polluted this notion?

--sd