Recommendations on pre-caching inventory numbers?

[Peter Payne]

We have an online shop selling bento boxes and other random things from Japan. Our current Filemaker server system (running on a custom built PC from an SSD, with backups made to another SSD for speed, then those backups are moved to a hard drive regularly) works exceedingly well and has given us a big speed increase over the old server we were running from.

The only speed bottleneck is this: when we bring up a product that has sold a lot, usually 1000-10,000 copies, the calculation that must report the number in stock slows Filemaker down for the user, causing delays of a few seconds while the information is tallied. Some of these are download products so we don't need to know that we have sold 9,923 copies of them, but that's the way we have the products database set up.

 

My question is, is there no way to bring this kind of calculation to the next level? Couldn't we pre-cache the number sold and number in stock on the server so it's instantly displayed, or something like that? I'm a fan of the way OS X Mountain Lion shows the last known number for a folder's size in light grey, e.g. 650MB, a number that is updated and changed to black when the new value is calculated, resulting in no delay for the user who is usually fine with seeing the slightly outdated value. Of course, there have been many improvements in the way we use software and computers, between sold Mac and Windows UI development and all the stuff being done with iOS, but we never seem to see these in Filemaker, do we? ^_-

 

Anyway, any recommendations for pre-storing number sold (from the lineitems database) so the producs database wasn't always slowing down to think would be appreciated.

[David Jondreau]

It's not entirely clear what is taking a long time. Is this an import? Is this an unstored calculation or a summary field that calculating? If it's the latter, you can make a stored number field that gets set to the correct value on a daily basis by script or triggered when a new sale is made.

 

If it's an import, you can probably do some calculations on the delimited text faster than record creation.

[Peter Payne]

Sorry for not being clear. The number in stock is an unstored summary field, calculated by adding all the items sold in a related lineitems.fp7 database. (Actually this database was modified from the old Invoices sample database they gave you with Filemaker 3, it's been quite a long time ). So for items where there are a lot of records that need to be tallied, it can take 3-4 seconds to come up, or twice that if I'm connecting from home and accidentally view a product with a huge number sold. 

 

So your suggestion is to make a script that records these numbers, perhaps on one computer that can run every hour as part of other background tasks? That might work. I was hoping there as some newfangled type of function Filemaker might have thought to add in for us. It'd also be nice if the calculating could be interrupted, just as finding the sizes of files and folders doesn't hold up the Finder when you want to do stuff. I'll think about how we might implement something like this, thanks. 

[Steven H. Blackwell]

Perhaps look into server side scripting to accomplish this. Steven