Optimizing Slow Unstored Calculated Field Search and Export

[brokenmouse]

I have a database that we use to update our website inventory. A few years ago we began offering customized merchandise that gets dropshipped direct from suppliers.  Suppliers give us data feed files with their inventory levels, pricing, etc. and this file manipulates the data. It takes our current web database, compares values and exports those products with their updated values.

Importing the data and exporting used to take less than hour but now it takes several since the size of the web database has grown and the number of suppliers has grown.  Everything is automated through scripts. In the main table (web database), the proposed quantities, pricing, leadtime, variations, etc. all use unstored calculated fields to determine the new value. I then have a separate field which is used to flag items that need updating. The major bottleneck of the entire process is the searching of this field. It can take sometimes over an hour to search this field. Other steps like exporting the changes can take a while, too.

I have done some things to optimize the database but it still seems that these unstored calc fields are what is dragging everything down. I have tried replacing some of those calc fields with text/num fields with "replace field contents" script steps (or auto entry) but it does not seem to make a difference because of the indexing.  The database is not hosted or shared and my computer has decent specs with an SSD HD. I've got a simplified design chart attached for reference.

I am not sure that this is what comes with a large complex database file or if my design is flawed. The only two things I can think to try to reduce the processing time is:

1) Rewrite the scripts to update the supplier/inventory table records instead of replacing the records fresh each time.

2) Use a looped set field script to set the "change flag" field and/or the other updated price/qty/etc fields

 

Any thoughts or advice is much appreciated.

[Fitch]

Really hard to say more without seeing the actual schema and scripts. 

Your file has "grown" but how many records are we talking about? 

Both the options you mentioned are worth exploring. Regarding #2, if you're doing multiple Replace, that can be less efficient than a loop, because the record has to be opened/closed for each replace, whereas it only has to do that once in a loop.

PS: it helps us help you if you update your profile here with your FileMaker version.

[brokenmouse]

Thank you for your response.

Regarding the size, the central table is about 120k records. Each supplier table can have between 40k and 200k records...and there are about 10 of those tables. There are a two or three different smaller tables which we use to enter customized information like pricing parameters. There are about 8 unstored calculated fields in the central table that calculate the new price (n_price), qty (n_qty), etc. and it is nice to instantly be able to see what the new numbers will be when we are toying with the parameters.

 

The way the scripts work is very simple. There is a master script that runs the entire process:

It imports each supplier file one at a time, in their own separate scripts, manipulating the data into the same format.

Then it imports our web data into the central table.

Then it fills the "needs_update" field via replace contents. The calculation compares the new values to the old for several different fields (n_f1 =/= f1 or n_f2 =/=f2...) and flags it if there are any differences. Originally that field was a calculated field.

Then it finds the records where the "needs_update" field is true. This step and the previous one take a long time. It is hard to tell if was faster or slower leaving that field as a calculated field and searching the unstored calculated field.

Lastly it exports those records. Usually it's between 5k and 10k records unless we do major changes...then it can be much more. This step also takes a bit of time.

 

I don't know if my idea #1 would help because nearly every record in each of the related supplier tables would change every time we did this update process.

Regarding #2, If I did a looped set field as,  read somewhere that FM would update the index as it did each loop, thus eliminating the lengthy indexing step. Is this true?

 

 

I am using FM11 advanced.

 

Again thank you for your help.

[Fitch]

If the "needs update" field is a number field that is either 1 or blank, it should take no time at all to index or search. The replace calc seems as good as any a place to start. Please post the actual calculation.