"Overview" layout of portals - too slow... how to speed up?

[truelifeajf]

Hi all,

 

I have a solution that I've recently split out into UI and data so users have better speeds when using over a WAN.

 

And it works nicely... still kinda slow but acceptable.

 

However, I have an "overview" or "dashboard" layout of portals which is going horribly slow even with a simple (one field, stored) relationship.

 

The portals show order statuses so in one portal it shows all the orders at "waiting on payment" and in another portal, all the orders with a status of "ship", etc.

 

The status is a stored text field on each order. So the relationship is really easy.

 

But even across 600 records, it takes about 1 minute per portal to display the results!

 

Options?

- ExecuteSQL... about the same speed

 

- server-side script that puts all data into repeating fields so they look like portals but aren't actually... would work but I'm trying to avoid server side scripts if possible

 

- Have a layout in "table" mode, based on the "orders" table and a script performs a search for status=ship... but then I'd need 4 windows to show the 4 found sets. That's really a "dashboard".

 

- Do something similar to above but somehow use a webviewer to display the results... then I could have 4 webviewers on the "dashboard" layout... but then users couldn't click on the orders to go to that related order

 

- As users create orders and change statuses, it could push the key_order_id and the status to a table in the UI file so then portals would display much faster... but then each user would only get a "dashboard" of the orders they've interracted with, not ALL orders. So each user would have a different "dashboard" which is not consistent

 

- Similar to above but the data is pushed to a MySQL database, completely separate from filemaker 12 and then I can use PHP and their web browser to show a "dashboard". Again though, they could then not click on an order to view that order in FileMaker

[Josh Ormond]

However, I have an "overview" or "dashboard" layout of portals which is going horribly slow even with a simple (one field, stored) relationship.

 

The portals show order statuses so in one portal it shows all the orders at "waiting on payment" and in another portal, all the orders with a status of "ship", etc.

 

One thing to keep in mind, even though you only are showing 1 field in the portal or with the relationship, FM still has to download the entire record AND any record that the values of that field are dependent on.  That may be a big part of the slow down.

 

How is the performance if you do this?

• Create a layout or table that only has straight data fields ( no calcs, no graphics, no relationships )
• From a different layout: Enter Find Mode, Go to that new layout, perform a find that displays (25, 50, 100, 200, 400, 600) records. Benchmark each of those times.
• This will let you know if there is a network problem slowing you down.  ( If it's still pretty slow for all of those counts, it's likely your network that's the problem ).
• If everything runs fine there, the problem is something with the structure of the db. Not that you've really done anything wrong...but FM may be forced to download a lot of data to let the client display the Overview layout.
• Begin looking at your structure to reduce dependencies on other data ( hint: many times you can replace a calc or relationship with a script that does the calc and then sets the field value for you ).

[Brooks]

Are there any unstored calcs on server side in the tables? Particularly using SQL? This will cause the Delays you are experiencing.

[Mr. Scott]

Hi, truelifeajf:

 

First of all, I'd like to ask why you are avoiding server-side scripts? I use them to populate straight number fields with the calculated results of a calculation field. The speed improvements are enormous — not only for searches ("Find routines"), but also for sorting, filtering, and scrolling. I use these server-side scripts to allow me to use the Join table for aggregate results, reducing results from a 5-minute summary report down to 5 seconds via  the WAN.

 

That being said, Josh has proposed some great isolation steps (maybe a worthy topic for DevCon, Josh…), and "Brooks" brings up another — where most critical calculations are NOT indexed (stored), and thereby increase evaluation times over a network (especially a WAN!).

 

Please let us know how you fare.

 

- - Scott

[Josh Ormond]

What can I say?  I am very experienced in doing things the "wrong" way. So now I'm becoming better at diagnosing the chaos.  Since the first solution I built 5 or 6 years ago is still in production use, I get to practice diagnosing it's quirks.  It's definitely made me a better developer.

 

That being said, Josh has proposed some great isolation steps (maybe a worthy topic for DevCon, Josh…), and "Brooks" brings up another — where most critical calculations are NOT indexed (stored), and thereby increase evaluation times over a network (especially a WAN!).

[truelifeajf]

I've just learned that I don't get any email updates of posts that I follow so I'll have to work out why.

 

So... in reply to this thread from 5 months ago!....

 

It was simply the speed of the WAN and having to download all the data so it can be sorted and displayed in a portal.

 

And to confirm... there were no unstored calc fields or anything complex... so there wasn't a lot to isolate really... Finding those 600 records took seconds, but displaying a portal which had to sort those 600 records took "forever".

 

So my solution was:

 

- have an "update dashboard" button

- clicking that, finds all records with a status of "waiting on payment", collecting the unique IDs for each record and storing them in a global field called "Dashboard::g_waiting_on_pay".

- it does the same for all other 4 statuses... so I have 4 global fields on the Dashboard table, each storing the unique IDs required to display the 4 portals.

 

The portals are then based on those multi-line global fields.

 

It's much faster because if there's 10 unique ID's in the g_waiting_on_pay field, then FileMaker just has to look up 10 records - easy.

 

Much fast, nothing overly tricky required, and it just means when the user opens their dashboard, it triggers the "refresh dashboard" script, and the user has to wait maybe 20 seconds.

 

Scrolling the portals is also much faster.

 

So overall, mission accomplished.

 

Thanks for your help