Design/Performance Q: Separate Files and Calcs

[spankalee]

My Inventory/Ordering/Shipping system has been running pretty well for a year now on FM 7. Recently it's been slowing down a bit, and I've got to hunt down the problem. I've made a few network improvements, and changed some calculations, but I think the big problem is with my design.

First, the number of records we're talking about isn't outrageous: there's about 9000 invoices, with 15000 line items; 700 products; 500 customers. But there is a lot of data duplication, between Invoices and Line Items especially.

So I didn't follow a separation model, but I didn't put everything in one file either. There's about 50 tables spread over 10 files. I grouped the files by function: Invoices, Customers, Products, Inventory, etc... Since there's so many links between the tables there are a lot of external table instances. Invoices links to customers, etc...

There's two main areas I suspect of slowing things down, but I'm not so sure how these things really work in FM so i want to run them by you:

1) data duplication: I do a lot of calculation based on line items (from invoices or purchases) so I copy data from the parent record to the line item via a lookup. There's a lot of redundant data just to support relationships and calculations.

2) lot of relationships: Something I do for inventory and some reports is I set up many relationships that function as active finds. Inventory for instance, relates to the InvoiceLineItem table 6 different times. These relationships are a little complex too with a match depending on four fields (ProductCode, InventoryCode, Location, and Date). Then I use aggregate functions to add up the quantities. I can't store the results of these calculations, so when I look at the whole inventory list (675 products x 2 locations x 4 unstored calculations based on relationships) it takes a while.

3) multi-level relationships. Finds seem to get really hit by this. On an Invoice there's a portal to IineItems, In LineItems there a drop down for Location. The lineItem actually only stores the location ID, but the drop down displays the name which is stored in the Locations table. Doing a find for Invoices with a certain product from a particular location takes forrrrrever. 10 minutes maybe, we usually cancel.

A lot of my techniques have been really helpful, but I think they have a lot of overhead. I'm not sure how to re-implement things to make it faster.

One idea I've had is to make the inventory into a transaction system. Everything that effects inventory would create a transaction with a product code, location and either negative or positive quantity. Then there would be a simple calc for determining the inventory level. The only problem here is that if the original record changes, it would have to update it's related transaction record. Since there's no triggers, this means more buttons to press, and errors if the user doesn't.

But I'm not so sure that that would help that much. The calculation would still be from one table to a related table, even though it's now in the same file. Would FM be able to store the result, and only update when there's a change to the transaction table?

The finds are the biggest problem right now. They seem to be mostly affected by the depth of the relationships graph, and I'm not sure at all how to deal with that.

Anyways, I'm rambling, but any suggestions would be greatly appreciated.

[Søren Dyhr]

Well, what a huge post ...lets take them as they occures. The first thing that surprises me slightly is this:

But there is a lot of data duplication, between Invoices and Line Items especially.

Ideally shouldn't there be any here at all, could you please explain why you copy stuff from invoice to itemlines, they're related aren't they???

I use aggregate functions to add up the quantities. I can't store the results of these calculations, so when I look at the whole inventory list (675 products x 2 locations x 4 unstored calculations based on relationships) it takes a while.

I wonder if your stuff, is ready for a strong algorithm like this, weeded out for the most obvious pre-7 issues:

http://www.onegasoft.com/tools/fastsummaries/index.shtml

--sd

[Ender]

Spanky,

None of the things you mentioned should cause significant decreased performance. On the contrary, lookups and relationships are a good way to improve the speed of certain types of searches.

If you don't already have it, you may benefit greatly from hosting with FM Server. Finds on unstored calcs are processed on the server side, which is much faster than having the client calculate over the network and compile the results.

There may be other hints we could offer about improving a specific slow find process, but I think we'd need to see the script and the part of the TOG involved.

[Søren Dyhr]

to improve the speed of certain types of searches

But the question is if searches is the proper way to address a what I feel must be a sort of KanBan system???

--sd

[Ender]

Well, KanBan or Can-Can, we all gotta dance to the tune of performing finds for certain types of reports--it's just not practical to build all reports with fast summaries or related record jumps. A normalized structure would be the ideal design, but if performance of Finds and Sorts can be improved by using Lookups, then I think it makes sense to do so. You just have to make sure you design it so those lookups can be updated if the parent data changes.

[Søren Dyhr]

fast summaries or related record jumps

What I had in mind was something like these two flavours:

http://www.fmforums.com/forum/showtopic.php?tid/159548/

or

http://previews.filemakermagazine.com/videos/585/InterfaceSummaries_full.mov

Which aren't particular "Jumpy" ...in my humble opinion! All though quick-quick-slow reminds me of the fraction of a second I attended dance tuition in my green youth. Alright Kanban isn't much about dancing:

http://www.graphicproducts.com/tutorials/kanban/index.php

--sd

[spankalee]

Thanks for all the responses. I'm off to read the links here, but I'll respond to the data duplication question real quick.

The reason that I need to duplicate Invoice fields into InvoiceLineItems is that I'm using them in a relationship.

Inventory has a record for each ProductCode/Location combination. Then it's related to InvoiceLineItems based on four fields: Date, InventoryCode, ProductCode and Location. Date, InventoryCode and Location are all Looked up from Invoice, but I need them for the relationship.

Now that I think about it, it's not that much duplication, but there are other looked up fields in lineitems, like price, description, so that they can be changed from the default values stored in products.

What's interesting is that the relationship dialog has a dropdown box for selecting which table to make the relationships by, but it's disabled. It'd be nice to relate InvoiceLineItems to Inventory by way of Invoice::Date, say.

Ok, time to read.

[spankalee]

So I checked out the links and the first thing to note is that I'm not doing a report, I have live updating inventory numbers. This is important because we need to level to be accurate to the minute so that if an order is placed that takes up the remaining inventory of a product and another order come in 10 minutes later we know that we're out.

I actually have this set up in the order processing form so that as soon as you enter a product code on a line item it shows the inventory levels (in stock, on hold, etc) for that product. Then when you enter a quantity it's immediately taken off of inventory. So a system based on scripts wouldn't work. Summary fields might not work either, since the InvoiceLineItems would have to be sorted, right?

I am running on FM Server, and I haven't noticed much of a performance difference. How can I tell which calculations are performed on the server and which aren't? The server is pretty slow processor-wise and we have an upgrade coming. Maybe this will help.

To give an idea of how bad the performance is for inventory, is I'm showing all 1300 inventory records it takes about 5-6 seconds just to do a page down (25 records visible). A find based on a inventory level, like all records where available stock is less then 100, takes a few minutes and can't easily be canceled. A find based on the product code is nearly instantaneous.

[Ender]

...a system based on scripts wouldn't work.

Don't be so sure.

It sounds like the reason your finds are so slow is because the calc you're searching on has to add up all the related quantities purchased to get the new stock level. This is a common problem with inventory systems that calculate on the fly like this.

One good solution to this is to use scripts to update a regular number field in the Product record. By storing the current inventory level and search on that, you should get much faster search results. This requires careful scripting to update it everytime the product is used in an order or added to inventory. You might also build a recount script that updates the stored values with the current difference between the related counts.

[spankalee]

That's definitely a thought I had on how to improve performance, but my concern is that it seems fragile.

Most of our orders have something changed after they're initially created, often times it's the quantity (just due to the nature of our product). When the quantity is changed I'd need to make sure that the number field is updated appropriately, by the difference between the last value and the new value. I can trigger a script with the MacExample plugin, but how do I capture the previous value?

That's why I was thinking of a transaction system, each lineitem of every table that affects inventory could be linked to an inventory transaction. Then if the value is changed it can change its related transaction. The Inventory calc would then be based only on the one transaction table, in the same file, instead of 6 or so tables in a few files. Would this even help? Could the calc result be stored?

[Ender]

I think your proposed transaction system would end up having the same performance problems (unless Server 7 can do this calc quickly??) But it would be doing a similar thing: calculating the difference between sums of related values.

As I said, updating a stored field will require careful scripting. This mean either using an event plug-in (the Example plug-in has some flaws that could be problematic in a live solution,) or using scripts to process all quantity changes. The scripts could be executed when navigating out of the invoice edit screen, updating the count for each product, or you could make it so a special Item Edit screen is used for editing line items after the invoice is initially processed (this would allow you to update the inventory level for just the item that changes.)

[Søren Dyhr]

but there are other looked up fields in lineitems, like price, description, so that they can be changed from the default values stored in products.

No and yes, meaning that price is a sure candiadate for a LU in the sence that an invoice is a historic document, and future changes to the price shouldn't be refelcted in old invioces already issued ...but you're right about the text.

that I'm using them in a relationship

If you can pull them in by a relationship, so could matters more than a relation away ...but only on demand and as a reference.

--sd

Edited August 4, 2005 by Guest

[Søren Dyhr]

This is a common problem with inventory systems that calculate on the fly like this.

Indeed, thats why the Venetian approach adapted by Luca de Pacioli still have it's use, the merchants of the 1490'ies knew exactly what they were worth, way before aggregate or summary functions arrived or desktop computers for that matter.

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

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

--sd

[spankalee]

I think your proposed transaction system would end up having the same performance problems (unless Server 7 can do this calc quickly??) But it would be doing a similar thing: calculating the difference between sums of related values.

Yeah, it would be doing basically the same thing, but I was hoping to find a way to get FM to store the calculation result and only update it when a related field has been added or changed.

As I said, updating a stored field will require careful scripting. This mean either using an event plug-in (the Example plug-in has some flaws that could be problematic in a live solution,) or using scripts to process all quantity changes. The scripts could be executed when navigating out of the invoice edit screen, updating the count for each product, or you could make it so a special Item Edit screen is used for editing line items after the invoice is initially processed (this would allow you to update the inventory level for just the item that changes.)

The part of the scripting I don't understand is how to figure out what the previous value of a field was so I can adjust the inventory correctly. Do I have to do something where I dump the previous value into a temp field when the quantity field activates and do the calc when the user exits that field? Is there an event plug-in that can trigger scripts upon entering and exiting a field?

[Søren Dyhr]

Do I have to do something where I dump the previous value into a temp field when the quantity field activates and do the calc when the user exits that field? Is there an event plug-in that can trigger scripts upon entering and exiting a field?

Before convincing yourself entirely that plugins are the answer to your prayers take a look at this template:

http://www.nightwing.com.au/FileMaker/demos/RunningBalance.zip

...where you make the portal sort on a time-stamp field instead of Rays Item column. Getting the last line's figures are pretty easy:

Last ( Data::sBalance )

But what could be a wish would be to know when the level gets critical, I allowed myself to change/hack a little in Rays template to illustrate the idea ...hope it's alright??

Meanwhile will I go in the thinking box to make a Luca di Pacioli thingy, to make a system ignorant to whether its 3000 records or 1000000 records!

--sd

RunningBalHack.zip

Edited August 4, 2005 by Guest

[comment]

I don't quite understand the purpose of your modification. IMHO, running balance IS stock level. Purchasing items and stock-counts should be records - not fields.

[Ender]

Soren, it's not clear to me at all how this running balance discussion helps. Spankalee is trying to get the Product inventory counts to update, not see a running total of the items on a particular invoice.

The part of the scripting I don't understand is how to figure out what the previous value of a field was so I can adjust the inventory correctly. Do I have to do something where I dump the previous value into a temp field when the quantity field activates and do the calc when the user exits that field? Is there an event plug-in that can trigger scripts upon entering and exiting a field?

Spankalee, yes you will need to store the previous quantity when the user goes in to edit. If you're going to only allow editing of one item at a time via an Item Edit layout, then one global field will work. If you're going to allow the user to edit the whole invoice and then process it, use a regular number field in the Line Item table. Have your process script check the difference between the previous value and the current value and if there's a change, adjust the count in the Product table.

There is a chance for a record locking error, if two users try to edit the same Product at the same time. Be sure to commit changes to Products right away, and check for record locking in the process scripts.

[Søren Dyhr]

Yes it should - I just tried to brainstorm.....I'm working with the idea that purchase and sales all drills down to a record per single item, this makes it have a life in the warehouse starting to be an item in purchase document, and then eventually ending it's life being assigned to an item line in a sales document.

If you're selling 6 items of type AA will all the unassigned uniqueitems be a portalfull of AA's ...knowing that the need is 6 is making the script go to portalline 6 or last if fewer availiable and issue a GTRR(SO), omit the remaining of the set, gathering the ID's of the reduced set, and assign them to the invoices itemlines as a returndelimited field.

The purpose is that ValuelistItems( on the itemlines field that carries the returndelimited, are giving us a key combined with the typekey to show availiable AA's. The other purpose is that it makes a FIFO system where the actual level is irrelevant, and the fields shown in the portal of the invoice is all stored, you're not counting on something a relation away but only ValueCounting( on a field in the record ...

The good question is where ValueListItems gets unreliable sizewise???

Shortage in supply is easy to establish, as diff' of the desired number of items and to the itemline assigned. Changing of the count of items in a line, well fewer are straight forward giving the atomic ID free for others to use, but adding requires a n eventtrigger, or dare I say a button in the line???

--sd

[mminer]

I seem to have a very similar situation to spankalee.

Although it is still a work in progress, I'm trying to make my file "purgeable"--in other words, I can delete data as needed to keep speed up. To this end, I have scripts to create PDFs of the invoices and, as an end of day batch script, I'm duplicating order line items into another table (inventorylog) that logs inventory changes. I can then delete the orders and line items periodically. I can delete the log as well if I replace it with current inventory levels.

I'm sure this is not the best solution but I welcome feedback on it. I'm also interested in hearing what script solutions spankalee may be offered.

My concern with Ender's proposal is that if a script doesn't perform correctly (just once!), the entire inventory could be incorrect and irrepairable.

[Søren Dyhr]

My concern with Ender's proposal is that if a script doesn't perform correctly (just once!), the entire inventory could be incorrect and irrepairable.

but wrapping your script into a:

Allow User Abort [ Off ]

...certainly prevents the lions share of scripts not performing as desired. Putting an UPS on the mains solves some other fair share of hickups.

If you take other database tools, do they rely entirely on triggers and scripts ...filemaker is actually pretty unique by having summary- and agreggate- functions. So tools like FmMigrator will have to deal with this uniqueness somehow. So it's not a fact causing partiular much sweat for other developers not choosing Filemaker Pro - why should we be concerned???

--sd

[Ender]

My concern with Ender's proposal is that if a script doesn't perform correctly (just once!), the entire inventory could be incorrect and irrepairable.

I said you could build a recount script. It's not too hard--just loop through all the products, setting the current count to be the difference between all the items purchased and all the items sold. If line items get purged each year or something, then you use a separate table to remember the counts for each year, and factor that into the current count.

...I'm duplicating order line items into another table (inventorylog) that logs inventory changes...

I'm not a big fan of systems that duplicate records. Duplication is usually unnecessary, and creates another point of failure. Besides, I think you'd end up with the same problem--over time you're having to count up all the items (and all the changes.) It's not clear that your proposed system avoids this.

Edited August 10, 2005 by Guest

[Søren Dyhr]

Duplication is usually unnecessary, and creates another point of failure.

Hear, hear!! Unfortunately a FAQ ...that requires more effort to make sense in the newbes ear, than I'm able to put in it with my limited english.

Could one of the native english speakers, make an explanation we all could link to when the topic occurs - next???

--sd

[T-Square]

Spankalee--

It is not overly difficult to maintain a stored inventory value for your products--at least, it hasn't proved to be onerous in my experience. Basically, you need a script that handles the product inventory numbers, attached to the LineItem Add/Delete operations. Every time you save a record to the LineItems table, you lower the ProductInventory number by the number in the LineItem record with a Set Field; when you delete a LineItem, raise the number. This could be managed with a single script that takes the amount to adjust as a parameter (negative or positive values being passed). If you delete an entire Invoice, just loop through the LineItems and perform the adjustment for each product as you delete it.

As others have noted, if you need to recreate your inventory numbers, you may have some trouble. I have a system that saves a client's account balance, and as they order products, their balance is adjusted. Generally, the system works fine, except that when part of the system crashed, it was nearly impossible to determine accurate balances, in part because the users were in the habit of manually adjusting the client balance. I'm not sure how you might solve that problem longterm.

HTH,

David

[mminer]

The recount script sounds like the best solution. Currently I have an unstored calc field in my product table that takes the sum of the line items from that table.

The recount field is supposed to store the value, correct? Is it a summary field or calc? Definition? Or is it just supposed to store the unstored calc field that I already have.

Thanks,

[Ender]

Yeah, it would just be a regular number field that is used to hold the value of the unstored calc. Seems redundant, but what we're doing here is providing a way to improve access time to view this count (or perform finds on it.)

[gdurniak]

I have a similar inventory solution in FM6, and the problems are the same. A few comments:

1. you really can't display "live" inventory data. FileMaker (6 or 7) is just too slow. FileMaker was not designed to "crunch" numbers. Period.

2. put your "live" data only on forms, not lists

3. in FM7, stick to standard "=" relationships. Avoid > or <, they are WAY too slow.

4. if possible, off load "old" inventory / invoice items. Store these records in an archive, and get them out of the system. Store the TOTALS of those items in a "stock adjustment" field, so that your "live" totals stay correct