Speeding up a networked solution

[Angus McKinnon]

I'm looking for ideas on how to speed up a networked Filemaker solution that I'm responsible for. At the moment parts of it are running quite slowly and the users are complaining. 

 

There is a mixture of Filemaker 12 and 13 clients, on both Windows and Mac. There is a Mac Mini running Snow Leopard Server which is hosting the file. This computer doesn't have any other duties. It's a normal FIlemaker Pro client rather than Server. 

 

In the usual working day, there is one client machine with the solution permanently open but rarely used, and another two that can have it open and be using it a little more intensively. 90% of the time there are just those two clients, and it's only on extremely rare occasions that there is more than three clients. Even when I'm the only person running it, functions are still very slow and I'm seeing far more of the "spinning pizza of death" than I feel I should be. 

 

The database itself is a bit convoluted but doesn't seem that large compared some discussed here. There are 12 tables, 42 relationships, 44 layouts. The largest table has 30,000 records, there's a total of 3500 customer records split between two tables. My hunch is that it shouldn't be choking on that but I might be wrong. 

 

There isn't masses of other network traffic going on - just usual web/email access from other users. In theory everything should be 100Mbps speed if not Gigabit. The two constant clients are on wired ethernet connections, but I don't notice any speed difference between them and a laptop on reasonably fast wireless. Client machines don't seem to affect performance much, if at all. The oldest is an iMac G5 and it doesn't seem to run any slower than my own i7-based MacBook Pro. 

 

I'm struggling to discern any pattern with the slowdowns. The one consistent thing is that it takes a while - around 20-30 seconds - for the database to open. This is a bit of a pest because sometimes staff are opening it up whilst a customer waits on the other end of the phone. Other than that, it can freeze with any and all kinds of user interaction. 

 

I've tried to eliminate any of the obvious resource hogs like summary fields and complicated sorts. I use quite a lot of conditional formatting on some layouts but it's all based on relatively simple conditions.

 

 

Things I'm considering, having had a read around:

 

I've heard that a non-Server installation of Snow Leopard might make more sense - especially since the machine is doing nothing else except Filemaker. (It's not acting as a file or print server or anything like that). Would it be worth reinstalling a non-Server OS, possibly upgrading to Lion?

 

It's not out of the question to replace the Mac Mini - it's a relatively humble spec, 1.66GHz Core Duo with 1Gb of RAM. Even if I don't replace the entire machine, I think there's scope to upgrade the RAM, and with SSDs relatively cheap especially for the small capacity I'd need. However I'm not sure if it's the machine that's the problem or if the bottlenecks lie elsewhere?

 

Filemaker Server seems like overkill for the small number of users we have, and I think I'd have a fight on my hands to get the expenditure approved. However I'd certainly consider it if anyone reckons it would make a serious difference?

 

Are there any other steps that I should be considering?

 

 

[Rick Whitelaw]

I run FMS on a mini with a fast drive and 8gb RAM. I Highly recommend it. Bumping the ram and the drive did wonders for stability and performance.

[Angus McKinnon]

Sounds good, but what processor does your Mini have? I'm wondering how much benefit a Core Duo is going to get from a fast SSD, for instance. 

[dwdata]

Hello,

If the sole purpose is to host the DB and you are considering a purchase of a server, I have heard from some of my IT connections that FMS runs faster on Windows. Maybe some can chime in to conform or deny. With the cost of windoze machines probably being less expensive for more power, you might consider this route.

Of course you would have to compromise and give into the dark side (Microsoft) for the greater good.

My 2 cents...

[Angus McKinnon]

I'm definitely not a Windows guy but could probably cope with that. I certainly agree that the cost vs the raw horsepower is much more attractive. 

I had a bit of a dig around online last night and have found someone selling refurbished Mac Pros (quad core, 16Gb etc.) for relatively little money. I could move the existing Mac Mini to be a client machine, and maybe stick an SSD in the Mac Pro. 

I could probably get a Windows machine with more grunt for around the same price, but it's hard to tell if the additional speed would actually be beneficial.

[Wim Decorte]

Before solving a bottleneck you need to find out which one is causing you grief.  Unfortunately because you are not running FM Server you don't get the benefit of its statistics which would point your right to where the problem is.

20-30s for the solution to open is a lot, especially since it sounds like it is a small solution.  Find out what the opening scripts do by using the script debugger and stepping through it.

Make sure the solution opens on a blank layout so that no unstored calculations and summary fields need to be refreshed.

Also have a look at the sheer number of unstored calculations especially in relation to what you see is slow.  If you need to so searches for instance on those unstored calcs then that could be one reason for the performance hit.

 

[Angus McKinnon]

Thanks, Wim - that's all very useful. 

Unfortunately the main page of the database is a "dashboard" style layout - it's being used for a hire company so it's crucial that staff can see what's due to move in or out. This is the "busiest" layout - there are two portals (one for movements out, the other for equipment returning) and each portal row has four or five pieces of conditional formatting. (Different colours depending on whether a job is overdue, out on hire, been prepped, invoice been issued, been paid for etc.) There aren't any calculated or summary fields displayed in the portal rows. Most of the conditional formatting is based on IF or CASE statements and doesn't seem particularly convoluted, but with 20 rows in each portal, that's getting on for 200 calculations, so this might be the main resource hog. 

The opening script sets the reference field for the portal, clears a couple of search fields, and sets the window dimensions. I don't think there's anything in there that's going to have hideous overheads but can't be 100% sure. 

Once the database is open on a client machine, then running the "Go back to Dashboard" script works almost instantly. Also if I change the date the portals are based on it doesn't seem to take long to re-do the portals. The delays once things are open is the spinning pizza of death appearing seemingly at random. 

The problem I have is that the users need to have the dashboard in front of them immediately - I agree that opening on a blank layout would be better but the first thing they would do is go straight onto the dashboard. I'm not sure if opening the database onto something blank, then scripting a move onto correct layout, would be any quicker?

 

[Angus McKinnon]

Also I've just run the script debugger on the main opening script. No errors reported and only one "Call stack" is accessed. 

When I open the database for the first time, I get asked for my login almost immediately, but then there's a long pause before the layout appears. However nothing appears in the script debugger until the layout appears - after that it rattles through the script steps more or less instantly. I'm guessing that this indicates that the problem isn't with the script itself?

[dwdata]

Thanks, Wim - that's all very useful. 

Unfortunately the main page of the database is a "dashboard" style layout - it's being used for a hire company so it's crucial that staff can see what's due to move in or out. This is the "busiest" layout - there are two portals (one for movements out, the other for equipment returning) and each portal row has four or five pieces of conditional formatting. (Different colours depending on whether a job is overdue, out on hire, been prepped, invoice been issued, been paid for etc.) There aren't any calculated or summary fields displayed in the portal rows. Most of the conditional formatting is based on IF or CASE statements and doesn't seem particularly convoluted, but with 20 rows in each portal, that's getting on for 200 calculations, so this might be the main resource hog. 

The opening script sets the reference field for the portal, clears a couple of search fields, and sets the window dimensions. I don't think there's anything in there that's going to have hideous overheads but can't be 100% sure. 

Once the database is open on a client machine, then running the "Go back to Dashboard" script works almost instantly. Also if I change the date the portals are based on it doesn't seem to take long to re-do the portals. The delays once things are open is the spinning pizza of death appearing seemingly at random. 

The problem I have is that the users need to have the dashboard in front of them immediately - I agree that opening on a blank layout would be better but the first thing they would do is go straight onto the dashboard. I'm not sure if opening the database onto something blank, then scripting a move onto correct layout, would be any quicker?

 

Yup. Sounds like it is a calculation thang. Probably multiple calculations even prior to the conditional calculation to color the rows. There is most likely a way to optimize the elements on this layout, but we would have to know each field's definition and the conditional calcs. on the row, Without that data, we would be assuming things,

This issue is probably going to be difficult to solve in this type of forum because there needs to be a bit of digging and testing. You might need to hire a sharp-shooter, in this case, to asset you.

Good Luck!

Edited June 17, 2015 by dwdata

[Angus McKinnon]

Thanks, Don - that certainly seems to make sense to me.

I might try duplicating the layout and removing conditional formatting bit by bit to see if there is any one piece that is choking the system. 

 

I'm thinking that I'll try a hardware upgrade first and see if the additional horsepower helps, before hiring someone in. Hopefully if I throw 8x the RAM and 4x the processing power at it things will speed up at least a little. At least if a new machine does nothing, it could be sold on again or has at least freed up a slightly faster client machine for use elsewhere. 

I've no real experience of Filemaker Server (short of mucking around with a demo of an older version many moons ago) but I'm guessing that it wouldn't magically handle these sorts of calculations any quicker than the peer-to-peer sharing that we're using just now? (At least, for the limited number of clients that I'm dealing with)

[Wim Decorte]

FMS is going to be a bit faster because of the way that interacts, but it is not going to solve the issue.  You WILL get a lot better data on which of the 4 traditional bottlenecks you are running into (processing, memory, disk i/o, network i/o).

 

Your plan to remove bits and pieces from the dashboard is solid.  Remember that conditional formats may fire multiple times in the process of drawing the layout.

[dwdata]

Hey Agnus,

(I tried to private message you, but got an error)

If it helps, before you shell out $ for a new server, I'd be glad to take a free peek at what you got going on.

Contact me via SKYPE (dwdata) and I can setup a TeamViewer or GoToMeeting session.

Lemme know if I can help ;-)