Portal Row Creation Speed

[RainDog]

Hey folks,

I have created an application that allows stores to login and select quantities of products to order each day. It works great, but it is very slow to create a new order, as it has to create hundreds of portal rows listing products for order each time.

I have a table for Orders, a table for Inventory and a table for Orders_Inventory which handles the details of each order.

To create the list of products for order, I enumerate the items in the inventory and create a new row in the Orders_Inventory table with the PK of the new Order. Inventory details are added using lookup fields.

When I run the database locally, it is very fast (< 10 seconds), but when I run remotely from the server, it is very slow (10 minutes) and the stores are starting to get upset at how long it takes to create a new order.

Any ideas what is going on?

[Vaughan]

Files hosted on FM Server can actually be faster than files accessed locally.

I'd look for bottlenecks with network and server; with the server check that the box is up to spec with cpu and ram, and has a good fast hard drive and good network card. It should also be dedicated to the task of running FM Server. Definitely not running other file sharing processes like a shared network volume.

Other than that there can be some optimisations like making sure the new records are created on a form layout not a list layout, minimising graphics, etc.

[RainDog]

Thank you for getting back to me.

I don't think the server is the bottleneck...I am not the administrator, but I have been in contact with him many times and he is on it. It's a nice, fast box dedicated to File Maker.

I am interested in your other point, though...and I am creating records in a list view. I thought that was the only way to go since the line items records are all displayed in a portal on the main form. How can I create new rows in the main Form portal?

[Fitch]

I don't think Vaughan was suggesting creating records via portal, but rather on the layout where you are creating new records, set the view to form rather than list so that FileMaker only has to deal with one at a time as your script runs.

Another problem may be indexes. If every order has hundreds of line items, I imagine your line items table must have hundreds of thousands if not millions of records. If you have many indexed fields in that table it is really going to slow down creation of new records.

This may actually be a reason to look at creating the records via a portal - you can create all the related records, and not commit them until you commit the parent records. This can be considerably quicker than creating records in the child table.

[RainDog]

OK, tried a couple of things you suggested, and didn't see any real results. I removed all but 4 indexes on the child table and created a form to handle the record adds, but things still run slow.

How can I create a new row in the portal? I think that is worth looking into.

Thanks for all the help so far!!!

[Fitch]

The portal's relationship must be set to allow creation of related records.

Your script can then go to the last row and set a field to create a new related record. Loop/repeat until all the desired records are created.

At that point, you can do a Commit.

A nice side effect of this is that if there is any error generated at commit, you can revert the record. Not so useful perhaps in your application, but fantastic for financial transactions.

PS: what's on the form you created? Is it minimal, or blank? Are you hopping back and forth from one layout to another as you create the records? If so you should try putting the data you need into a variable array.

[RainDog]

Thanks. Going to try the portal thing and will let you know how it works for me. It sounds like exactly what I need.

The form I created is minimal, it only contains the 3 fields I am populating. And, yes, I am flipping between layouts (from Inventory to Orders_Inventory) to create each row with Inventory data. Seems like I'm missing another good trick...care to educate me further?

[Fitch]

With this approach you'd loop through the Inventory records, setting the data into repetitions of variables. Notice how we increment $i to specify the repetition. This makes it easy to do the reverse process when you create your Orders_Inventory records.

You might optimize this further by using GetNthRecord instead of Go to Record.

/* gather the Inventory records */

Go to layout(Inventory)

Loop

  Set Variable( $i ; $i+1 )

  Set Variable( $product[$i] ; product )

  Set Variable( $price[$i] ; price )

  Go to record(next; exit after last)

End Loop



/* create Orders_Inventory records */

Go to layout(Orders_Inventory)

Loop

  New record

  Set Variable( $n ; $n+1 )

  Set Field( product ; $product[$n] )

  Set Field( price ; $price[$n] )

Exit Loop If($n=$i)

End Loop

[RainDog]

Thanks a lot - that works great. My code is cleaner, order creation is much faster and I can keep users updated with a "Creating Record x Out Of y" message while it loads. You rock.